How to load the Australian car database into Excel, SQL Server, MySQL, PostgreSQL and Python

The CarsData dataset comes in four formats so you can drop it straight into whatever you already use. This guide walks through each file, then gives working examples for Excel, SQL Server, MySQL, PostgreSQL and Python, and finishes with how to apply a new release without breaking anything that depends on the data.

The files

Each release is published with a version number in the file names. Replace <version> in the examples below with the version you downloaded.

Columns

Every format has the same 27 columns, in this order:

NVIC, GuideID, Year, MakeCode, Make, ModelCode, Model, Variant, Series,
Body, Doors, Transmission, Gears, Fuel, EngineCC, Cylinders, Drive, Turbo,
VehicleType, HighPerformance, MarketValue, RetailValue, TradeValue,
TradeLowValue, KerbWeightKg, LengthMm, ReleaseMonth

The data dictionary describes each one. The codes used in Body, Transmission, Drive, Fuel and VehicleType are explained in body types, transmission and drive codes.

Excel

Just open carsdata-au-<version>.xlsx. The Vehicles sheet is ready to filter and sort, with codes already stored as text.

Avoid double-clicking the CSV to open it in Excel. Excel guesses column types, and can strip leading zeros from codes or turn values that look like dates into dates.

Power Query for easy refreshes

If you build your own workbook on top of the data - pivot tables, lookups, charts - connect to it with Power Query instead of copying and pasting:

1. In your workbook, choose Data > Get Data > From File > From Workbook and select the CarsData .xlsx file.
2. Pick the Vehicles sheet and choose Load (or Transform Data to filter it first, for example to recent years only).
3. When a new release arrives, open Data > Queries & Connections, edit the query's source to point at the new file, and choose Refresh. Your pivots and formulas update with it.

SQL Server

Unzip carsdata-au-<version>-sql.zip and run the script with sqlcmd against the database you want the table in:

sqlcmd -S . -d YourDatabase -i carsdata-au-<version>.sql

-S . means the default instance on the local machine - use your server name instead if needed, and add -U and -P for SQL authentication. If the database doesn't exist yet, create it first:

sqlcmd -S . -Q "CREATE DATABASE YourDatabase"

Large scripts are best run with sqlcmd rather than opened in SQL Server Management Studio, which can struggle with very large files. If accented characters in names look wrong after loading, run it again with -f 65001 so sqlcmd reads the file as UTF-8. Then check the result:

SELECT COUNT(*) AS VehicleRows, MIN(Year) AS FromYear, MAX(Year) AS ToYear
FROM dbo.CarsData_Vehicles;

MySQL

Create a table, then load the CSV with LOAD DATA LOCAL INFILE. The lengths below are generous; tighten them if you like after checking the data dictionary.

CREATE TABLE carsdata_vehicles (
  NVIC            VARCHAR(10)   NOT NULL,
  GuideID         VARCHAR(20),
  Year            SMALLINT      NOT NULL,
  MakeCode        VARCHAR(10),
  Make            VARCHAR(60),
  ModelCode       VARCHAR(20),
  Model           VARCHAR(100),
  Variant         VARCHAR(200),
  Series          VARCHAR(100),
  Body            VARCHAR(20),
  Doors           TINYINT,
  Transmission    VARCHAR(5),
  Gears           TINYINT,
  Fuel            VARCHAR(20),
  EngineCC        INT,
  Cylinders       TINYINT,
  Drive           VARCHAR(10),
  Turbo           VARCHAR(10),
  VehicleType     VARCHAR(10),
  HighPerformance VARCHAR(10),
  MarketValue     DECIMAL(12,2),
  RetailValue     DECIMAL(12,2),
  TradeValue      DECIMAL(12,2),
  TradeLowValue   DECIMAL(12,2),
  KerbWeightKg    INT,
  LengthMm        INT,
  ReleaseMonth    VARCHAR(20),
  PRIMARY KEY (NVIC, Year),
  INDEX ix_make_model_year (Make, Model, Year)
) CHARACTER SET utf8mb4;

LOAD DATA LOCAL INFILE 'carsdata-au-<version>.csv'
INTO TABLE carsdata_vehicles
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NVIC, GuideID, Year, MakeCode, Make, ModelCode, Model, Variant, Series, Body,
 @Doors, Transmission, @Gears, Fuel, @EngineCC, @Cylinders, Drive, Turbo,
 VehicleType, HighPerformance, @MarketValue, @RetailValue, @TradeValue,
 @TradeLowValue, @KerbWeightKg, @LengthMm, ReleaseMonth)
SET Doors         = NULLIF(@Doors, ''),
    Gears         = NULLIF(@Gears, ''),
    EngineCC      = NULLIF(@EngineCC, ''),
    Cylinders     = NULLIF(@Cylinders, ''),
    MarketValue   = NULLIF(@MarketValue, ''),
    RetailValue   = NULLIF(@RetailValue, ''),
    TradeValue    = NULLIF(@TradeValue, ''),
    TradeLowValue = NULLIF(@TradeLowValue, ''),
    KerbWeightKg  = NULLIF(@KerbWeightKg, ''),
    LengthMm      = NULLIF(@LengthMm, '');

A few notes:

• IGNORE 1 LINES skips the header row, and the byte order mark with it.
• The NULLIF calls turn empty numeric fields into NULL - without them, strict mode rejects the row.
• ESCAPED BY '' stops MySQL treating backslashes as escape characters, which RFC 4180 files don't use.
• If the last column ends up with a stray carriage return, or every row loads into one, switch the line terminator between '\r\n' and '\n'.
• LOCAL INFILE must be allowed on both client and server - connect with mysql --local-infile=1 and make sure the server's local_infile setting is on.

PostgreSQL

Create the table (same columns, PostgreSQL types), then load it with \copy from psql, which reads the file from your own machine:

CREATE TABLE carsdata_vehicles (
  nvic varchar(10) NOT NULL, guideid varchar(20), year smallint NOT NULL,
  makecode varchar(10), make varchar(60), modelcode varchar(20), model varchar(100),
  variant varchar(200), series varchar(100), body varchar(20), doors smallint,
  transmission varchar(5), gears smallint, fuel varchar(20), enginecc integer,
  cylinders smallint, drive varchar(10), turbo varchar(10), vehicletype varchar(10),
  highperformance varchar(10), marketvalue numeric(12,2), retailvalue numeric(12,2),
  tradevalue numeric(12,2), tradelowvalue numeric(12,2), kerbweightkg integer,
  lengthmm integer, releasemonth varchar(20),
  PRIMARY KEY (nvic, year)
);
CREATE INDEX ix_carsdata_make_model_year ON carsdata_vehicles (make, model, year);

\copy carsdata_vehicles FROM 'carsdata-au-<version>.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')

In CSV mode, unquoted empty fields load as NULL, so numeric columns need no extra handling. HEADER true skips the first line, including the byte order mark.

Python

CSV with pandas

import pandas as pd

df = pd.read_csv(
    "carsdata-au-<version>.csv",
    encoding="utf-8-sig",            # strips the byte order mark
    dtype={"NVIC": str, "GuideID": str, "MakeCode": str, "ModelCode": str},
    keep_default_na=False,           # keep text such as "NA" as text
    na_values=[""],                  # only empty fields become NaN
)

print(len(df), "rows")
print(df.groupby("Year").size().tail())

keep_default_na=False matters: by default pandas turns strings such as NA or NULL into missing values, and short codes like these can legitimately appear in model or series names.

JSON

The JSON file has release details at the top and the rows in vehicles:

{
  "dataset": "...",
  "version": "...",
  "released": "...",
  "rows": ...,
  "licence": "...",
  "fields": [ ... ],
  "vehicles": [ { "NVIC": "...", "Year": ..., "Make": "...", ... }, ... ]
}

import json
import pandas as pd

with open("carsdata-au-<version>.json", encoding="utf-8-sig") as f:
    data = json.load(f)

print(data["version"], data["released"], data["rows"])
df = pd.DataFrame(data["vehicles"])
assert len(df) == data["rows"]

The same file works in JavaScript, C# or any other language with a JSON parser.

Applying a new release

A new release arrives every 6 months. It adds new vehicles and years and refreshes values. There are two sensible ways to apply it.

Option 1: truncate and reload

If nothing else in your database has foreign keys to the vehicle table, the simplest approach is to replace it. In SQL Server, drop the old table and run the new script:

sqlcmd -S . -d YourDatabase -Q "DROP TABLE IF EXISTS dbo.CarsData_Vehicles"
sqlcmd -S . -d YourDatabase -i carsdata-au-<version>.sql

For MySQL and PostgreSQL, TRUNCATE the table and repeat the load. To avoid any gap while loading, load into a copy of the table first and swap the names once it is complete.

Option 2: upsert on NVIC and Year

If other tables reference vehicles, or you have added your own columns, load the new release into a staging table and merge it in on the primary key. In SQL Server, run the release script into a separate staging database, then:

MERGE dbo.CarsData_Vehicles AS t
USING CarsData_Staging.dbo.CarsData_Vehicles AS s
   ON t.NVIC = s.NVIC AND t.Year = s.Year
WHEN MATCHED THEN UPDATE SET
     t.Variant = s.Variant, t.Series = s.Series,
     t.MarketValue = s.MarketValue, t.RetailValue = s.RetailValue,
     t.TradeValue = s.TradeValue, t.TradeLowValue = s.TradeLowValue
     -- ...and the other columns
WHEN NOT MATCHED BY TARGET THEN
     INSERT (NVIC, Year, Make, Model, Variant /* ...all columns */)
     VALUES (s.NVIC, s.Year, s.Make, s.Model, s.Variant /* ... */);

In PostgreSQL the equivalent is INSERT ... ON CONFLICT (nvic, year) DO UPDATE SET marketvalue = EXCLUDED.marketvalue, ..., and in MySQL INSERT ... ON DUPLICATE KEY UPDATE.

Decide deliberately what to do with rows that are in your table but not in the new release. If you have records pointing at them, keeping them is usually safer than deleting them; add WHEN NOT MATCHED BY SOURCE THEN DELETE only if nothing depends on them.

Next steps

Download the free sample in any format to test your import before you buy, read the data dictionary, browse every make, or get the full dataset for $149 once with every future release included.

Once it's loaded, see how to build a year, make, model dropdown or read what an NVIC code is.