4

I am trying to import a file from CSV to QGIS (version 3.20.1). However, when I import the data, some values (points) are missing.

I know that this question have been already asked but I have tried the solutions in before posts and it does not work for me. For example, I have unchecking "Detect field types" or eliminating variables that I do not need.

I attach a screenshot of the CSV file that I have it. Any idea about how to solve that issue?

enter image description here

Update: I know it's so few and probably it's not useful but I have found two observations that appears in my original CSV file and when I import to QGIS dissapears. I attach the screenshot. I put four images, the first two correspond to the first observation, the lattest two, to the second one. I am not able to see what can be wrong with that two observations.

enter image description here

enter image description here

enter image description here

enter image description here

14
  • 1
    Can you share your data?
    – Taras
    Dec 14, 2021 at 17:39
  • 2
    I know you said you tried a couple of thing but ensure only digits are present in the lat/lon columns, if I run into weird issues like this, it can be attributed to extraneous whitespace characters. Also check that the values are within bounds.
    – RomaH
    Dec 14, 2021 at 18:19
  • 3
    @OgeiD No worries. If a data entry person accidentally added a space before or after the comma in the CSV in your lat/lon fields, e.g. abc,124.26787 ,-56.83743,abc. That extra space might cause QGIS to think it is text and not a number, therefore discarding the record. Make sure all values are between lat: 90 to -90 and lon is 180 to -180.
    – RomaH
    Dec 14, 2021 at 18:33
  • 3
    to know the missing rows, you start by adding an ID field (row number). Then you import the points, then you join the source csv to the imported points using the ID and a last you filter the joined table to hide records having a value in the point layer
    – JGH
    Dec 14, 2021 at 18:36
  • 2
    To find the offending lines, use a bisection analysis: Half your dataset, import it, if it is still missing points, half it again, load, check, rinse and repeat. Once you have a half that is not missing points, you know that the other half of the previous step has missing points, so half that one and repeat. In the end you will have a small dataset with lines that fail. Dec 14, 2021 at 20:17

1 Answer 1

5

Quick solution

When loading the CSV, use Custom delimiters, select Semicolon and be sure to have all others empty. When loading the CSV you provided in your comment, I get 187532 points loaded - that seem to be all.

enter image description here

Identify problematic entries

To find the problematic entries, import the CSV as NO geometry (attribute only table). Check if lat/lon values are correct: in the Layer properties / Source tab / Query builder and filter invalid values for lat/lon using this query (see screenshot below):

latitude is NULL or 
longitude is NULL or 
(not (latitude >= -90 and latitude <=90))  or 
(not (longitude>= -180 and longitude<=180))

Click Test to check if there are any features returned: if so, these are the problematic features. Apply the filter and open the attribute table: it should now contain only the filteres features with values outsite the range of -90/90 for lat and -180/180 for lon. You could also directly use the same expression with Select by expression to directly select these entries (however, in a large dataset, the attribute table will take a while to load).

By the way: you could search in your latitude and longitude fields for any characters that are not numbers or points (commas, spaces etc.). As you have many decimal digits, you might round the values to get rid of potentially problematic characters: round (latitude, 5) - 5 digits should be precise enough. Even try to convert the lat/lon values to integers, creating a new field: to_int (latitude), then try creating points with these values to see if like that all points are created.

enter image description here

The problematic entries, here selected with select by expression: enter image description here

See how to import CSV without creating geometries: enter image description here

Edit

Inspecting the data you provided (the 389 features that produced an error), it becomes clear what went wrong: the latitude field contains names. It seems that the content of the fields was shifted one column to the right:

enter image description here

4
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Ian Turton
    Dec 15, 2021 at 17:19
  • 1
    Downloaded your data and loaded it, using semicolon only as delimiter: this seems to solve the problem, see updated answer.
    – Babel
    Dec 15, 2021 at 17:30
  • Thank you so much!!!! I really appreciate it!! It was amazing!
    – OgeiD
    Dec 15, 2021 at 18:39
  • Hi @Babel, I have still some problems with the data when I import the csv. I wrote in the chat that Ian proposed but I am not sure if I should write here in case you can help me with it.
    – OgeiD
    Dec 16, 2021 at 11:31

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.