How to Open NEM12 Data in a Spreadsheet

Import NEM12 CSV into Excel, Google Sheets or LibreOffice Calc as a clean table.

Last Updated: 2026-08-05

Key Takeaways

• A NEM12 CSV file is a semi-structured, non-tabular data exchange format from AEMO. It does not open as a clean tabular layout in Excel, Google Sheets, or LibreOffice Calc.
• The format is mixed-record. Header (100), metadata (200), data (300), and event (400) rows share one file, so a spreadsheet cannot align them into one set of columns.
• The format has a hierarchy. A 200 metadata row sets the NMI and the interval length for the 300 data rows below it.
• To open NEM12 CSV data in a spreadsheet cleanly, you first flatten the interval data into a proper table.
• For charts and summaries only, a browser tool reads the smart meter data file directly. You do not need a spreadsheet at all.
• For your own formulas, convert the NEM12 CSV data into a useable format for a spreadsheet — a flat wide format or long format CSV.

Why the NEM12 CSV Format Does Not Display Cleanly in a Spreadsheet

You downloaded your smart meter data from your DNSP portal. You then opened the NEM12 CSV in Excel or Google Sheets. The columns did not line up. The cause is not your spreadsheet. The cause is the NEM12 CSV format itself, an AEMO data exchange standard that machines read rather than people.

A representative extract from the raw file — one 200-record (metadata) and one 300-record (interval data) for each register, plus the end-of-file 900 marker. The same column position carries different information depending on the row type.

Column 1 holds the NMI in a 200-row but the date in a 300-row. Column 2 holds the NMI configuration code (B1E1) in a 200-row but the first 30-minute energy reading in a 300-row. A spreadsheet has no way to resolve this ambiguity without a custom parser.

NEM12 Is a Non-Tabular, Hierarchical Format

A standard CSV carries one header row and consistent columns. NEM12 carries several distinct row types in one file, and a record number opens each row. The chart above shows how far the field count varies between those row types.

How Metadata Rows Give Each NEM12 Reading Its Context

A 200 record declares the details that apply to every 300 record below it. These details are the NMI, the interval length, the unit of measure, and the register code. The 300 records carry none of those labels themselves. That is why the file cannot stand alone as a table.

The metadata rows in this NEM12 file identify the meter, the registers present, and the period covered.

Source: 200-records in the NEM12 file. NMI 6200000000.

The Technical Shape of NEM12

A small set of precise terms describes why the file resists a spreadsheet. Read together, these traits explain the layout on your screen:

• Semi-structured: the format applies strict comma separation and defined record codes, but no consistent column attributes across rows. Most workflows need a custom parser to flatten it.
• Hierarchical: the format is nested, or parent-child. The NMI and interval length on a 200 record set the context for the 300 records beneath it.
• Mixed-record: the file intermingles heterogeneous row schemas (100, 200, 300, 400) instead of one uniform record type.
• Unpivoted: a single 300 record lists the 48 half-hour blocks in chronological sequence — long format — rather than under named columns.
• Decoupled: a separate 400 record can override quality, so the status flag sits apart from the numeric value it describes.
• Context-dependent: the meaning of a value — its NMI, its unit of measure — lives only on the metadata rows.
• Conditional: a 400 event can qualify specific intervals within a day. The true value for a block of time may then need several rows read together.
• Flagged: each 300 record embeds quality codes such as A (Actual), S (Substituted), and F (Final Substitute).

Together, these traits make NEM12 a non-tabular file. A spreadsheet cannot show it as a clean table until you restructure it.

Before You Start — Check Your NEM12 File Is Valid

Check the file before you convert it. Look for missing intervals, substituted readings, and a short date range. Each of these reduces the value of any analysis you build from the file.

1. Confirm the date range: open the NEM12 CSV in a text editor. Read the first and last 300 records — a full year covers every season.
2. Scan the quality flags: search the file for the letters S and F. Those flags mark substituted or estimated values rather than measured values.
3. Verify the NMI check digit: your bill may show ten NMI digits where your portal wants eleven. If so, calculate the missing digit. Our NMI Checksum Digit Calculator calculates it in the browser.

Try our NMI Checksum Digit Calculator Tool

You will find the digit checker with our other free online tools. A NEM12 file can also hide multiple NMIs, mixed interval lengths, and timezone traps. Our guide on how to avoid NEM12 CSV data gotchas covers those traps. With a valid file, you have two practical ways to open your NEM12 data.

Solution A — View Charts and Tables Without a Spreadsheet

If you want charts and summary tables only, you may not need a spreadsheet. Our free NEM12 Smart Meter Data Analyzer reads a raw NEM12 CSV directly in the browser. The tool reports on each meter register:

• Load profiles: the average shape of a day, drawn from your own interval data.
• Seasonal breakdowns: how daily use and generation change through the year.
• Grid import and solar export: separate totals for the E1 and B1 registers.

The tool converts nothing, asks for no account, and retains none of your register data. If you want your own formulas, Solution B covers the conversion instead.

Try our NEM12 Smart Meter Data Analyzer Tool

Solution B — Convert Your NEM12 File to a Spreadsheet-Ready Format

Our free NEM12 CSV Spreadsheet Converter converts a NEM12 file into a flat CSV with consistent columns. The converted file should open correctly in Excel, Google Sheets, or LibreOffice Calc. The converter drops the 100, 200, 400, and 900 records. It then reshapes the 300 records into a table. It also copies the parent context — the NMI and the register code — onto every reading. The converter writes two output shapes.

Format 1 — Wide Format (One Row Per Day)

Each row holds one full day for one register. The 48 half-hourly readings appear as separate columns under time-of-day headers:

Four days from the same file in wide format — one row per day per register, with time-of-day column headers. Columns shown: overnight (00:30–02:00), midday (11:30–13:00) and end-of-day (23:30–24:00). 40 of the 48 time columns are hidden.

Summer day 15 Jan 2025, winter day 15 Jul 2024. B1 solar export is zero at night regardless of season and non-zero at midday in summer. At midday on the summer day, E1 grid import falls to zero — the solar system covers all consumption and exports the surplus.

Wide format suits a side-by-side comparison of daily load profiles. Some solar design tools also expect one row per day. Our guide on how to import NEM12 CSV into solar design tools explains that requirement.

Format 2 — Long Format (One Row Per Interval)

Each row holds a single 30-minute interval. The NMI, the register code, and the date repeat on every row. This makes the file easy to filter and sort:

The same day (15 Jan 2025) in long format — one row per 30-minute interval, NMI and register repeated on every row. Overnight grid import (E1, 00:30–03:00) and midday solar export (B1, 10:30–13:00) are shown.

The full 366-day file contains 35,136 rows (366 days × 2 registers × 48 intervals). Filter Register = E1 for grid consumption, Register = B1 for solar export, then group by Date, Time, or month to build any summary you need.

Long format is the more flexible shape for custom work in Excel or Google Sheets. Every interval is its own row, so you can group, filter, and add calculated columns with standard formulas. You can take a long format table further. See our guide on how to analyse NEM12 smart meter data in a spreadsheet.

Try our NEM12 CSV Spreadsheet Converter Tool

Frequently Asked Questions

• What is a NEM12 CSV file? NEM12 is the AEMO format for exchanging interval data from smart meters. It carries half-hourly readings, metadata, and quality flags as semi-structured, non-tabular data.
• Why does my NEM12 file look broken in Excel? The file is not broken. NEM12 is a mixed-record format that packs several row schemas into one file. Excel cannot align those schemas into clean columns.
• Can I open a NEM12 file directly in Google Sheets or LibreOffice Calc? You can open it, and you will see the same mixed-record layout as in Excel. The file still needs flattening into a tabular shape first.
• How do I import NEM12 CSV into Excel as a clean table? Convert the file first. A converter reshapes the 300 records into consistent columns and copies the metadata context down. The result opens as a proper table.
• How do I import NEM12 CSV into Google Sheets? Convert the raw file to a flat wide format or long format CSV first. Then use File and Import in Google Sheets to load the converted file.
• How do I convert NEM12 CSV data into a useable format for a spreadsheet? Run the file through a NEM12 converter that drops the 100, 200, 400, and 900 records. The converter then rebuilds the 300 records as a table.
• What does semi-structured mean for the NEM12 format? The file obeys strict comma separation and defined record codes. It does not, however, apply one consistent column schema to every row.
• Why does NEM12 use a hierarchy instead of flat rows? AEMO designed the format for machine-to-machine exchange. A parent 200 record states the context once, and the child 300 records inherit it.
• What does unpivoted mean in a NEM12 300 record? The interval readings run along the row in chronological order with no individual column headers. They do not sit under named time columns.
• Why is the NMI missing from the NEM12 data rows? The NMI lives on the 200 metadata row, not on the 300 data rows. Each reading therefore depends on the row above it for its identity.
• What does a 400 record do in a NEM12 file? A 400 record qualifies or overrides data quality for a range of intervals within a day. It sits separately from the numeric values themselves.
• What does the quality flag on a 300 record mean? The flag records how the reading was obtained — A for Actual, S for Substituted, and F for Final Substitute.
• What is wide format versus long format? Wide format places each half-hourly interval in its own column with one row per day. Long format gives each interval its own row with the date and time repeated.
• Which format should I choose for a spreadsheet? Long format is generally easier to filter and sort. Wide format suits reading a whole day of load profile across the page.
• How many intervals are in a day of NEM12 data? A standard half-hourly file holds 48 intervals per register per day. Some newer meters record 5-minute intervals, which gives 288 per day.
• Where do I get my NEM12 file from? Your DNSP portal usually offers a download against your NMI. Your electricity retailer may also supply the file on request.

Try our NEM12 CSV Spreadsheet Converter Tool

Glossary

AEMO

Australian Energy Market Operator — the body that administers the National Electricity Market and defines and maintains the NEM12 specification.

Check Digit

The 11th digit of a full NMI — calculated from the first ten digits to detect transcription errors; some DNSP portals require it. Also called a 'Checksum'.

CSV

Comma-Separated Values — a plain text file in which each row is a record and fields are separated by commas.

DNSP

Distribution Network Service Provider - the company that owns and operates the physical poles and wires of the local electricity network, and usually provides the smart meter data portal. Not the same as your Electricity Retailer. See also: Electricity Retailer.

Electricity Retailer

The company that sells electricity to the consumer, issues the bill, and manages the account. Not the same as your DNSP (Distribution Network Service Provider) which owns and operates the network. See also: DNSP.

Energy Usage Time Series Data

Electricity readings (consumption, generation, import, export, storage) recorded against a sequence of timestamps. In NEM12, a chronological run of interval values typically measured in kWh, typically Half-Hourly but can be 5, 15, or 30 Minutes.

Half-Hourly Data

Interval data recorded every 30 minutes — 48 readings per register per day.

Hierarchy

In the NEM12 Format, a parent-child structure where a metadata row (200) defines the context for the data rows (300) that follow it.

Interval Data

Energy readings recorded at fixed time steps — typically every 30 minutes (but sometimes 5 or 15 minutes on newer meters) rather than as a single billing total. This allows charging different Tariffs at different times of day or days of the week. See also: Time-of-Use (TOU) Tariff.

kW

KiloWatt — the standard unit of (Electrical) Power: 1,000 Watts of Power used continuously for one hour is equivalent to 1000 Watt-Hours or 1 KiloWatt-Hour of Energy. Power is not an Amount of Energy but a Rate of Energy Transfer. See also: kWh.

kWh

KiloWatt-Hour — the standard unit of (Electrical) Energy: 1,000 Watts of Power used continuously for one hour is equivalent to 1000 Watt-Hours or 1 KiloWatt-Hour. NEM12 CSV records Electricity Use in kWh. See also: kW.

Load Profile

The pattern of a site's electricity use over time, derived from its interval data. The granularity or resolution of the Profile is the Interval Length (eg. 30 Minutes, Hours), and is typically based on averages for a longer period (eg. Months, Seasons).

Long Format

A table shape with one row per interval — keys such as date, time, and register repeated on every row, making the data easy to filter, sort, and pivot. See also: Pivot Table.

Mixed-Record

In the NEM12 Format, a file that intermingles several different row types (100, 200, 300, 400) with different schemas, rather than one uniform record type per file.

NEM

The Australian National Electricity Market — the interconnected electricity grid covering Queensland, New South Wales, Victoria, South Australia, Tasmania and the ACT. Western Australia and the Northern Territory operate separate grids.

NEM12

The AEMO file format (version 12) for exchanging Interval Data from Smart Meters across the Australian National Electricity Market (NEM). NEM12 Format records the Amount of Energy per Time Interval in kWh. And also other types of data, using specific types of labelled rows or records (100, 200, 300, 400, 900) to record different types of data. This makes it a 'Semi-Structured' 'Non-Tabular' Format. See also: AEMO, NEM, kWh, Semi-Structured, Non-Tabular.

NEM12 CSV

A NEM12 File stored as comma-separated values — the form most DNSP and retailer portals distribute. See also: NEM12, CSV.

NMI

National Metering Identifier — the unique 10-digit identifier for an electricity connection point (11 with the Check Digit), permanent to the property. Note that the electricity connection point identified by the NMI, typically has multiple NMI Suffixes, and can have multiple Physical Meters. See also: Check Digit, NMI Suffix.

NMI Suffix

In the NEM12 Format, the short two-character code (E1, E2, E3, B1, B2, Q1, K1) identifying a specific Meter Register for recording electricity flows on the mains electricity connection identified by that NMI — also called the Meter Register or Register Code. See also: NMI, NEM12.

Non-Tabular

Data that does not fit a single consistent grid — in NEM12's case because of its semi-structure, hierarchy and mixed-record layout.

Pivot Table

A spreadsheet feature that groups a long-format table by chosen keys and aggregates a numeric column (sum, average, minimum).

Quality Flag

In the NEM12 Format, a single-letter code on each interval reading showing how the value was obtained — e.g. A (Actual), E (Estimated), S (Substituted), F (Final Substitute). See also: NEM12.

Semi-Structured

A data format that follows strict syntactic rules (e.g. comma separation, defined record codes) but does not enforce a consistent column schema across all rows. NEM12 is a Semi-Structured Format. See also: NEM12.

Tabular

Data arranged as a single grid — one header row and consistent columns, where every row follows the same schema. NEM12 is not stored this way; rather it is 'Semi-Structured' and 'Non-Tabular'. See also: Semi-Structured, Non-Tabular.

Time-of-Use (TOU) Tariff

A tariff with different prices at different times of day or days of the week (Peak, Shoulder, Off-Peak).

Wide Format

A table shape with one row per day per Meter Register and one column per Time Interval — good for charting a Load Profile. See also: Load Profile.

Try our NEM12 CSV Spreadsheet Converter Tool