4

I've been trying to add a certain CSV file as a points layer to QGIS for a while now, without much success. It can be viewed over here. (It was adapted from this .xls data file). In the online displayer, it does not render well. That's probably because information was lost when I saved the original .xlsx file as a CSV.

However, when I add it as a delimited text file in QGIS with ; as the regular expression delimiter, the sample looks good. Still, the point coordinates are not shown on the map when I choose longNieuw and latNieuw as the x and y coordinates, respectively.

I'm not sure what goes wrong, as I have earlier successfully added other point layers in QGIS.

Question: why can the linked CSV file above not be added properly as a points layer file in QGIS?

Note: here's an example of a coordinate tuple of the csv file I'm trying to upload:

28,69733300601 ; -100,51019685

The CRS of my own project is EPSG: 3857 . I've tried adding the points layer in both this CRS and in EPSG: 4326.

7
  • 1
    Which CRS did you choose when loading the CSV into QGIS? Can you add a coordinate-tuple to this post?
    – Erik
    Nov 25 '20 at 15:53
  • @Erik I've added a coordinate-tuple to the post, and answered your question on the CRS in it as well.
    – Max Muller
    Nov 25 '20 at 15:58
  • 2
    You should have point instead of comma 28.69733300601 ; -100.51019685
    – Nil
    Nov 25 '20 at 15:59
  • 3
    Or you could check "decimal separator is comma". EPSG 4326 is the CRS you must use.
    – Erik
    Nov 25 '20 at 16:05
  • 1
    why not download the CSV files they provide directly?
    – Ian Turton
    Nov 25 '20 at 16:11
4

I can load them fine using your fixed fields longnieuw and latnieuw:

![enter image description here

However, you switched lat and lon. X actually should be lon and Y actually lat (e.g. lat of -101 does not exist). When importing you can change those easily though. Make sure to choose Semicolon as separator and check the box "Decimal separator is comma".

Btw. your original lat lon fields are obviously screwed by Excel.

The output with the above settings:

enter image description here

1
  • Thank you! Yes, it seems the x and y coordinates must be switched indeed.
    – Max Muller
    Nov 25 '20 at 16:20
1

IMHO the reason why a CSV file can not be added properly as a points layer file in QGIS are coordinates with commas instead of a decimal separator. All ',' have to be changed into '.', e.g.

18,78416650000;-97,40638880 -> 18.78416650000;-97.40638880

Moreover, as was mentioned by @MrXsquared, "longNieuw" and "latNieuw" have to be swapped.

Afterwards you will be able to add your CSV file into QGIS.

With a bit of Python and some PyQGIS I was able to achieve the desired output

# imports
import csv

# inputs
project_path = 'C:/Users/DUBRTARA/Desktop/test/project/'
csv_file_name = 'DataNewTry.csv'

# Step 1: manipulating original csv file
# reading a csv-file
with open(project_path + csv_file_name, mode='r', encoding="utf8") as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=';', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    values = list(csv_reader)
    for row in values[1:]:
        row[16] = row[16].replace(",",".")
        row[18] = row[18].replace(",",".")

# writing into the same csv-file while updated records are stored temporarily
with open(project_path + csv_file_name, mode='w', encoding="utf8", newline= "") as csv_file:
    csv_writer = csv.writer(csv_file, delimiter=';', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    for row in values:
        csv_writer.writerow(row)
        
# Step 2: reading a csv file with points and converting it into a shapefile
uri_csv_file = 'file:///' + project_path + csv_file_name + "?encoding={0}&delimiter={1}&xField={2}&yField={3}&crs={4}".format("UTF-8",";","latNieuw","longNieuw","epsg:4326")
# In .format("UTF-8",",","x","y","epsg:31469")
# "UTF-8" stands for data encoding (optional)
# "," is a delimiter used in the input file
# xField is a column name for longitude value
# yField is a column name for latitude value
# crs is a Coordinate system in EPSG number
points = QgsVectorLayer(uri_csv_file, csv_file_name, "delimitedtext")
if not points.isValid():
    print ("{} layer was not loaded".format(csv_file_name))

# Step 3: adding a shapefile with points into the QGIS's main window
QgsProject.instance().addMapLayer(points)

Press Run script run script and get the output that will look like

result


References:

1
  • 1
    Thank you. It is interesting to see how Python can be used in QGIS as well. Might come in handy in the future.
    – Max Muller
    Nov 25 '20 at 16:27

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.