QGIS regular expression while importing delimited text

Question

I have a CSV file with following format :

UTC,Callsign,Position,Altitude
2021-05-11T11:44:38Z,FABCD,"28.084,7.452",500
...

So I can't import with personalized delimiters. I tried to use regular expression but doesn't find any documentation. What I need is to split the Position field into X and Y, so create an additional column and rename as X and Y. My field separator is , but my position in inside "". How can I write my regex to do this operation ?

Answer 1

1. First import your data into QGIS.
2. Open the processing tool : "Geometry by expression"
3. Choose the point geometry
4. Here the expression :

make_point(
    -- if the second number is longitude (~ X)
    regexp_substr("Position", '[0-9]*[.][0-9]*,([0-9]*[.][0-9]*)'),
    -- if the first number is latitude (~ Y)
    regexp_substr("Position", '([0-9]*[.][0-9]*),[0-9]*[.][0-9]*')
)

If the first number is latitude, so the point falls in Algeria.

If the first number is longitude, so invert the two expression lines and the point will fall in South Sudan.

Answer 2

To get the coordinates from the field "Position", use this expressions:

1. First coordinate: regexp_substr(Position,'(^\\d+\\.\\d+)') -> 28.084
2. Second coordinate: regexp_substr(Position,'(\\d+\\.\\d+$)') -> 7.452

See below for explanation or here an easy to understand introduction to Regular Expressions.

• ^: string starts with the following element (any digit)
• \\d: any digit
• + the element before (here: any digit) should appear at least once or be repeated one or more times (alternatively: * 0 or more repetitions)
• \\. period (dot, point) - \\ used to mask
• • \\d: any digit
• +: repeat once or more
• $: string ends with the previous element (any digit)

Answer 3

In the Import Delimited Text dialog, choose the regular expression option and write:

,"|",|,

In Regex, the | acts as a boolean OR and will match the characters before or the characters after.

After this, you can choose which columns are latitude and longitude, but note that their names (the ones from the first row) will get shifted.

Answer 4

You can get the two coordinates values in an array with this expression:

regexp_matches ("Position",'(.*)\\,(.*)')

results in: [ '28.084', '7.452' ]

Advantage: you need just one expression to get both values at once. Simply add the array's index at the end of the expression in square brackets [] (0 for first one) to tell which of the two values you want to get: [0] to get the first, [1] to get the second element, e.g.:

regexp_matches( Position,'(.*)\\,(.*)')[0] -> `'28.084'`