4

I have a dataset (in CSV) and am trying to add several fields with date information, so I can filter points based on this data and generate maps (in QGIS 2.8) which will form a chronological series. To accomplish this, I've done the following:

  • I've taken my original CSV and added four fields (see below for final CSV file, date data is in columns 12-15, titled award1-4).
  • Data was originally in YYYY-DD-MM format, so I've used the handy command-line csvfix tool to set all the data to be ISO format (YYYY-MM-DD).
  • To accompany the CSV file, I've generated a CSVT file, with the same name and path to accompany my CSV, following the instructions in the regularly linked blog post by Anita Graser and the related GDAL docs so my CSVT uses only the following types, "String","Integer","Real","Date" (the fourth of these is the crucial one)
  • I have also followed the helpful suggestion in this thread by Underdark / Kristen to use textwrangler to make sure characters are standard, and removing a second line and converting smart quotes resolved an issue in QGIS with importing.

After following these steps my CSV imports just fine in QGIS using 'Add Delimited Text Layer', but the layer only has Real (as "Double"), Int and String (as "Text"), with the date fields rendered as "String" as well. I'd like to have this CSV file import into QGIS with those date fields as "date" data type in QGIS so I can run queries on them. I'd also like to stick with CSV as the format as this data will be placed in an open repository eventually and I'd like other users to be able to view the data without QGIS, but also be able to import relatively painlessly into their own projects.

Has another user accomplished this in QGIS, and more to the point, what step am I missing here to make these fields import properly as "Date" field data type.

Samples of data are as follows:

ECS-GIS-Locations_2.4.csvt:

"Real","Real","Integer","String","String","String","String","String","String","String","Integer","Real","Date","Date","Date","Date","String","String","String","String","String"

ECS-GIS-Locations_2.4.csv (first few lines):

"X","Y","accuracy code","postcode","unique-id","name","network","denomination","notes","website","awards","icon-size2","award1","award2","award3","award4","wildlife-biodiversity","solar","date-built","churches trust URL","OS ref"
"349344.8977","1053019.497","2","KW17 2BU","2203","St. Ann's, Papa Westray","Orkney Islands","C of S","","","4","3","2003-10-09","2006-08-19","2009-07-07","2013-11-01","","x","1841","http://www.scotlandschurchestrust.org.uk/church/st-anns-papa-westray","HY495516"
"345691","1046915","3","KW17 2DB","2202","Westray Church","Orkney Islands","C of S","","","4","3","2003-10-09","2006-08-19","2009-07-08","2013-11-01","","","1846","http://www.scotlandschurchestrust.org.uk/church/westray-parish-kirk","HY457462"
"272400.702","655262.3425","2","ML3 7DT","2801","St John's Church Of Scotland, Hamilton","South Lanarkshire","C of S","","http://www.stjohnshamilton.org.uk","3","2.5","2003-05-01","2006-03-05","2009-07-09","","","","1835","http://www.scotlandschurchestrust.org.uk/church/st-johns-church-hamilton","NS724553"
  • Good commentary But any hints won't really work for a question. Also asking if you have stumbled on a bug would be another question (asked separately). If that is what you want to know please provide clear step by step directions to reproduce. You might restate your question with clarity on what you want an answer to. As this doesn't have to do directly with GIS it should probably be moved to SE – Brad Nesom May 6 '15 at 15:45
  • 1
    Thanks for this comment. I've edited above to focus on just one question and lay out my process a bit more straight-forwardly. Does this help? – Jeremy Kidwell May 6 '15 at 15:58
0

The reason must be somewhere in QGIS or in the GDAL version that it is using. Direct use of GDAL v.2.0-dev gives me an expected result:

ogrinfo ECS-GIS-Locations_2.4.csv -al
INFO: Open of `ECS-GIS-Locations_2.4.csv'
      using driver `CSV' successful.

Layer name: ECS-GIS-Locations_2.4
Geometry: None
Feature Count: 3
Layer SRS WKT:
(unknown)
X: Real (0.0)
Y: Real (0.0)
accuracy code: Integer (0.0)
postcode: String (0.0)
unique-id: String (0.0)
name: String (0.0)
network: String (0.0)
denomination: String (0.0)
notes: String (0.0)
website: String (0.0)
awards: Integer (0.0)
icon-size2: Real (0.0)
award1: Date (0.0)
award2: Date (0.0)
award3: Date (0.0)
award4: Date (0.0)
wildlife-biodiversity: String (0.0)
solar: String (0.0)
date-built: String (0.0)
churches trust URL: String (0.0)
OS ref: String (0.0)
OGRFeature(ECS-GIS-Locations_2.4):1
  X (Real) = 349344.8977
  Y (Real) = 1053019.497
  accuracy code (Integer) = 2
  postcode (String) = KW17 2BU
  unique-id (String) = 2203
  name (String) = St. Ann's, Papa Westray
  network (String) = Orkney Islands
  denomination (String) = C of S
  notes (String) =
  website (String) =
  awards (Integer) = 4
  icon-size2 (Real) = 3
  award1 (Date) = 2003/10/09
  award2 (Date) = 2006/08/19
  award3 (Date) = 2009/07/07
  award4 (Date) = 2013/11/01
  wildlife-biodiversity (String) =
  solar (String) = x
  date-built (String) = 1841
  churches trust URL (String) = http://www.scotlandschurchestrust.org.uk/church/
st-anns-papa-westray
  OS ref (String) = HY495516
  • Thanks for this helpful suggestion - I'm using GDAL v1.11.2-1 (compliments of Kyng Chaos' bundles, kyngchaos.com/software/frameworks#gdal_complete), and just ran the same command with the same output (fields are detected as dates and subsequent entries are ISO formatted (Date) entries. So, QGIS limitation then? – Jeremy Kidwell May 7 '15 at 14:01
1

There are two options to load .csv files into QGIS:

1_ "Add delimited text layer" 2_ "Add vector layer"

If you are loading a .csv with a .csvt you should do it by using “Add vector layer”

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.