Importing .CSV into QGIS 3.10 missing some data points

Question

I am trying to display a delimited text layer from a .csv (utf-8) in QGIS 3.10. I have done the same thing successfully in ArcMap 10.7.1 with no problem, but when I add the delimited text layer in QGIS over 700 of the 1900+ data points do not show up. I tried to resolve this by creating a shapefile in ArcMap and bringing that into QGIS, but exact same problem occurred. When I open the attribute table in QGIS the data is all there, it simply isn't being displayed. I am posting some images. The first on is the result in Arc, then Q and then I selected the missing data in Arc so you can get a glimpse of what the data look like.

The data were collected using WGS_1984 and the Maine/NH towns is projected in NAD83_StatePlane for NH in Feet. Any help solving this problem would be greatly appreciated. Is it a CRS issue? Are the data in the .csv messed up? If so, why does it work in Arc and not Q?

Here is the text of the data the first line of data show up fine, the second does not show up.

id,username,lat,lng,hotspot,observed,country,county,state,municipality,postcode,observation_notes,Sample Number,Sample Location,Was the sample filtered?,Type of Filtration System,Water filtration description,Beryllium (Be) (ug/L),Chromium (Cr) (ug/L),Manganese (Mn) (ug/L),Iron (Fe) (ug/L),Nickel (Ni) (ug/L),Copper (Cu) (ug/L),Arsenic (As) (ug/L),Selenium (Se) (ug/L),Cadmium (Cd) (ug/L),Antimony (Sb) (ug/L),Barium (Ba) (ug/L),Thallium (Tl) (ug/L),Lead (Pb) (ug/L),Uranium (U) (ug/L),Lab data entered on,SEPA School Name,City,State,ZIP code,Well Type,Previous arsenic test,Year,Do you treat your water?,Image 1

103070,anonymous,43.181041,-71.999461,,2019-12-09 23:35:00+0000,United States of America,Merrimack County,New Hampshire,Bradford,3221,,2019-1259,Kitchen,No,No filter,,0,0,1.41,9.3,0.64,487.15,0.06,0.11,0.07,0.08,19.55,0,5.73,0.01,1/23/2020,Kearsarge High School,Bradford,New Hampshire,3221,I don't know,No,2019,,

102564,anonymous,44.821972,-67.20856,,2019-11-23 21:00:00+0000,United States of America,Washington County,Maine,Whiting,4691,,2019-815,Other,No,No filter,,0,0.3,1.64,8.13,0.03,0.18,0.67,0.05,0,0.03,3.11,0,0.02,0.04,12/20/2019,Machias Memorial High School,Whiting,Maine,4691,Dug well,No,2019,,

Not sure if this is the best way to share this. Thank you all for the help.

Answer 1

The ususal reason for this type of problem is mistyped or misinterprented dates. Have a look at the "Lab data entered on" column in qgis (Layer > Properties > Fields). Is it a date field or a text string? In the former case, what is the format of the date string? (The only way to find out the format may be to sort the attribute table on that field)

My guess is that qgis reads it as an dd/mm/yy date string (which is quite common in Europe), it will then read 03/04/19 as 3rd April 2019, but it will refuse 03/13/19, as it would believe that to be the 3rd day in the 13th month.

Try to reimport your data unchecking "Detect field types". That may cause some other problems as it may not read other data correctly. If that reads all your data, try to manipulate the first "Lab data entered" value so it is not a valid dd/mm/yy date and reread your data set.

As far as I know, qgis is reading the first few lines of your data set to determine the types of data, so if the first few lines can be read as valid dd/mm/yy, it will assume that format for the rest of the lines as well - that also means that the subset you gave us will be read just nicely, since qgis finds an invalid date and read the field as a string variable.

Edit: Rereading your data, I see that neither of the "Lab data entered" dates could be read as valid dd/mm/yyyy.