In our project we have an app that provides data of coordinates with "N" and "E" for Lat and Lon correspondingly. We get the data as Excel and want to open it as shape in QGIS. We get a lot of small datasets from the app, so deleting the Ns and Es in the Excel file or otherwise change all the Excel files would be time-consuming.

Is there a way to import the coordinates as they are? With the "N"s and "E"s in the columns.

  • Does all the excel files have the same columns?
    – BERA
    Apr 28 at 5:24

1 Answer 1


Here is another approach.

Step 1. Drag&Drop your .xlsx file with all tables into QGIS,


So, they all will be visible in the Layers Panel


Step 2. Use a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer... with the following query:

            WHEN SUBSTRING("Lon", 1, 1)=='E' THEN REPLACE("Lon", 'E', '')
            ELSE REPLACE("Lon", 'W', '-')
            WHEN SUBSTRING("Lat", 1, 1)=='N' THEN REPLACE("Lat", 'N', '')
            ELSE REPLACE("Lat", 'S', '-')
        ) AS geometry
FROM "data — Tabelle1"

and get the output like this :


P.S.: Keep in mind that the query above will create a point layer only for the File : 'data' with the Table : 'Tabelle1'.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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