Importing file where long and lat in one column

Question

How to import a data file in which both longitude and latitude are combined in one column? e.g. 52.2299571,21.0099775

Answer 1

When importing a delimited text file into QGIS, there's an option to choose multiple custom delimiters. Choose this option. Select both "Comma" and whatever delimiter is used by the format that your file is in.

If other columns in your data file also have commas, they will be split into separate columns. You can fix this in one of two ways:

• Use the Field Calculator to combine those columns, or
• Import the data twice, then link the two layers with a table join (this method requires a unique, shared attribute).
  1. The first time, import it as described above.
  2. The second time, choose only one delimiter (whichever one the file uses between columns). Import it without geometry by choosing the option "No geometry (attribute only table).

Answer 2

You could just use left() and right() to create new columns with the Field Calculator.

left(
    "xy",
     (strpos( 
     "xy",
     ',' ) - 1)
)

The above extracts the number before the comma (by extracting all characters up until the index of character ',' minus 1). The below extracts the number after the comma:

right(
    "xy",
     (strpos( 
     "xy",
     ',' ) - 1)
)

You could add those as virtual columns if you don't want to change your original file, and then run "Points layer from table" in processing to create a new point file from this.

Answer 3

First of all, simply try the approach that was suggested by @Erik, deploy Excel with the combination of FIND(), LEFT()/RIGHT() and LENGTH(). Afterwards, import your CSV file into QGIS, check this article "Importing Spreadsheets or CSV files (QGIS3)".

Alternatively, one can try the following workflow

Let's assume there is a CSV file 'points' with wrong coordinates knitted together as x,y, see image below.

Simply drag&drop it into QGIS, so it can appear in the 'Layers Panel', like this

Then proceed with a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

With the following query, it is possible to plot locations based on coordinates (even with knitted coordinates) on the map

SELECT p.*,
make_point(
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,0,instr(p.xy, ','))
    ELSE 0
  END),
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,instr(p.xy, ',')+1)
    ELSE 0
  END)
),
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,0,instr(p.xy, ','))
    ELSE 0
  END) AS x,
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,instr(p.xy, ',')+1)
    ELSE 0
  END) AS y
FROM "points" AS p

In case if you possess the exact CRS, you can adjust the above query with a corresponding EPSG code, by means of SetSRID().

SELECT p.*,
setsrid(make_point(
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,0,instr(p.xy,','))
    ELSE 0
  END),
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,instr(p.xy,',')+1)
    ELSE 0
  END)
),31469),
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,0,instr(p.xy,','))
    ELSE 0
  END) AS x,
  (CASE
    WHEN p.xy LIKE '%,%' THEN substr(p.xy,instr(p.xy,',')+1)
    ELSE 0
  END) AS y
FROM "points" AS p

Check the output

and the updated attribute table

P.S. QGIS likes UTF-8

---

References:

• ST_MakePoint
• How to split comma-separated value in SQLite?
• SQL As Understood By SQLite | Core Functions