Data Import Basics

For data imports, the required file type for data is CSV - that is, comma separated value files that can be generated from Microsoft Excel or Google Sheets. See Creating CSV Files for details on this. There are also a number of other restrictions and requirements for data imported via these CSV channels. These include;

Date Format

Within your CSV file, any date values must be formatted as DD/MM/YYYY. For example; 24/10/2023 represents the 24th of October, 2023. It should be noted that the time of day will always be set to 00:00AM for imported data.

Spatial Format

The supported spatial format is ‘WKT’ (Well Known Text). This is a text string that represents a valid geometry definition. See Create WKT Geometries using QGIS for more details on this.

File Size

The maximum support CSV file size is 20MB. If your CSV file exceeds this limit, consider splitting it into two or more smaller CSV files.

Header Row

The header row of the csv file must be restricted to row 1 of the file. Also, the header row must not contain any spaces or special characters (such as carriage returns, or line feeds). The header row is mandatory.

Blank Cells

Throughout the CSV file, if any data row cells are blank, they will be ignored by the importer. This means that, when updating existing data, setting a CSV cell to blank will NOT delete the corresponding data from the system.

Effective Date

Where supported, users can include the effective_date column to stipulate when the new record, or updated data, should be considered effective from. This can be useful in setting a residual life value as at a particular point in time.

Avoid Special Characters

When constructing your CSV file, avoid special characters in both your header row and data rows. These include, but are not limited to:

• Commas within a cell’s content
• Carriage returns or Line feeds
• Other non-ascii characters that are not part of a standard keyboard layout.

CSV Files do not support Formulas

When using a product such as Microsoft Excel or Google Sheets, the program will allow you to utilise cell formulas whilst working on your data. As soon as you save and close the file however, the formulas will disappear, and only the results will be saved.

Creating CSV Files

This page provides guidance on how to create a CSV file from within Microsoft Excel and Google Sheets on a Windows based machine. As these are 3rd party programs, screenshots and processes are subject to change without notice. However, is is anticipated that the basic flow should remain the same.

Create CSV file in Microsoft Excel

To create a CSV file from your data in Microsoft Excel, you will need to ‘Save-As’ the data with the ‘CSV’ file extension enabled. To do this:

1. Click File > Save As.
2. Below the ‘File Name’ input box, where it says ‘Excel Workbook (.xlsx)’, use the drop down control to select ‘CSV UTF-8 (Comma Delimited) (.csv)’.
3. Give the file a name in the ‘File Name’ input and choose a location to save the new file.
4. Click ‘Save’ to finish.

Create CSV file in Google Sheets

To create a CSV file from your data in Google Sheets, you will need to ‘Download’ the data with the ‘CSV’ file type set. To do this:

1. Click File > Download > Comma Separated Values (.csv).
2. A file dialog will appear asking you where you wish to save the CSV file. Save the file in a known location and click ‘Save’ to finish.

Multi-Component Assets

When you are importing assets with more than one (1) component - such as a road asset with earthworks, base, and surface - you will need to have a csv row for each of the components being imported. To tie the rows together, so the system knows they are of the same asset, rather than distinct asset entries, set an identical asset_import_ref for each row.

Below is a video that demonstrates this practice.

WKT Geometries in QGIS

QGIS, an open source and free Spatial Data creation & editing tool, has the ability to export data in CSV format. Within these files, the geometry data can be represented as WKT (Well Known Text). This is the supported spatial type for importing data into the Metrix Asset Management system using CSV files. To do this, follow the steps below:

1. In the ‘Layer Browser’, right click on the spatial layer that you want to export.
2. From the menu, click on Export > Save Feature As…
3. A dialog box will appear. Firstly, set the ‘Format’ drop down box at the top to ‘Comma Separated Value (CSV)’.
4. Next, click on the 3 dots (…) on the right-hand side of the ‘File Name’ input control.
5. A file browser will appear. Give your export a file name in a known location, and click ‘Save’.
6. Then, back in the QGIS dialog, move down to the ‘Layer Options’ section of the export dialog and set the ‘GEOMETRY’ drop down to ‘AS_WKT’.
7. Lastly, uncheck ‘Add saved file to map’ and click ‘OK’.

You now have a valid CSV file with a WKT Geometry Column, as well as any other data attributes that were in the spatial file from QGIS. Below is a video guide of the above steps.