Can't read coordinates from CSV file

Question

I'm trying to import a CSV file with geolocated attributes, but the format is not recognized:

Is there a way to automatically recognized it?

EDIT: I think what I need is a batch converter to replace (for example) 9.8N /42.8W by 9.8 / -42.8. I'm looking for that tool.

IDT 2: here is the csv sample https://gofile.io/d/8QsYwb

Answer 1

Try the same approach with "Geometry by expression" described in @Luis Perez's answer. However, deploy a bit different expression

make_point(
    if(regexp_match("Longitude (deg.)", 'W'),
    -regexp_substr("Longitude (deg.)" ,'[^A-Z]+'),
    regexp_substr("Longitude (deg.)" ,'[^A-Z]+')
    ),
    if(regexp_match("Latitude (deg.)",'S'),
    -regexp_substr("Latitude (deg.)" ,'[^A-Z]+'),
    regexp_substr("Latitude (deg.)" ,'[^A-Z]+')
    )
)

And get the output

Using transform() is allowable.

---

References:

• Docs » QGIS User Guide » 23.1.15.35. Geometry by expression

Answer 2

In the past, I've fixed such problems in Excel. Add a column, filter for anything tagged S or W and calculate -1 in that column, and fill the rest with 1's. Find replace to get ride of all the NSEW characters. Then multiply column values by either 1 or -1.