4

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.

1
  • Do you really need the leading zeros? You can force them to be strings by enclosing them in quotes
    – Ian Turton
    Aug 20, 2023 at 7:06

1 Answer 1

6

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
)

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.