Importing several CSV files without losing leading zeros from OS NGR into QGIS

Question

I have ten CSVs that I need to import, in order to turn them into geopackages/shapefiles so I can edit the attribute tables etc etc. The ten files have a combined total of about 15000 entries. Each CSV covers a different region.

In each CSV, column C is the original OS grid reference which was given in either AAXXXXXX, AA XXX XXX or AA XXXXXX format. I have then added in two new columns, which split the OS reference into the NGR Eastings and Northings so they can be plotted & maintain consistent formatting.

I am struggling as many of the Northing values have leading zeros, which are being lost when I import the CSVs into QGIS. I have tried creating a formatting mask in Excel so that the columns only accept 000000 format, which visually inserts the zeros. However, when I save and reopen the files in excel, they revert back to loosing the leading zeros; and the same is clearly happening when it is added to QGIS.

I tried converting the columns to text in excel, but that removes the leading zeros: e.g. 009356 becomes 9356 when converted to text format.

How do I fix this? Do I need to save the file differently? Do I need to reformat the columns in a normal excel document and then export it as a CSV? I can't feasibly go through and manually add the preceding zeros after converting to text.

Answer 1

Import the CSVs to QGIS without adding those two columns. Then use the following expressions in Field Calculator for two new fields named Northing and Easting, respectively. Set 'output field type' as Text(string).

Northing:

substr(
    replace(GRID, ' ', ''), -- remove spaces
    3, 3    -- get Northing 
)

Easting:

substr(
    replace(GRID, ' ', ''), -- remove spaces
    6, 3    -- get Easting
)