4

I have a CSV file called office_locations.csv that contains point data. I want to load it into a SQL Server geospatial database using ogr2ogr.

The CSV file is encoded in UTF-8. It contains a header and 14902 records.

The first lines look like this:

Id,Name,Lat,Lng,CountryCode,Address,CityName,Airport,AirportCode,RailwayStation,PostalCode,StateCode,StateProv,Country_Id
"7188","Tirana Airport","41.42108838","19.71271276","AL","Tirana Airport Muhamet Gjollesha Str., Muhamet Gjollesha Str., Tirana","Tirana","1","TIA","0","","","",0
"30768","Tirana Downtown","41.332","19.832","AL","Rruga E Durresit. Nr 61, Tirana","Tirana","0","","0","","","",0
"52400","Sheraton Italia Square","0.0","0.0","AL","Square Italia Hotel Sheraton , Tirana,","Tirana","0","","0","","","",0
"4650","Escaldes","42.511","1.548","AD","Avda. Miquel Mateu, 25, Escaldes-engordany , Andorra, Ad700","Andorra","0","","0","AD700","","",1
"152576","Andorra","42.508","1.522","AD","Avda D Enclar, 142, Andorra La Vella, Ad500","Andorra La Vella","0","","0","AD500","","",1

I'm working on a Windows 7 workstation.

My GDAL version is 1.9.2.

ogr2ogr import skips records

To import CSV data using ogr2ogr you need to create a VRT file. I created one that looks like this:

<OGRVRTDataSource>
    <OGRVRTLayer name="office_locations">
        <SrcDataSource>office_locations.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="Lng" y="Lat"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

When I use the VRT file, ogr2ogr reads and writes only 4977 features:

$ ogr2ogr --debug ON -overwrite -f MSSQLSpatial  "MSSQL:server=.;database=OfficeImport;trusted_connection=yes" office_locations.vrt
OGR: OGROpen(office_locations.vrt/02F28328) succeeded as VRT.
OGR_MSSQLSpatial: EstablishSession(Connection:"server=.;database=OfficeImport;trusted_connection=yes")
ODBC: SQLDriverConnect(DRIVER=SQL Server;server=.;database=OfficeImport;trusted_connection=yes)
OGR: OGROpen(MSSQL:server=.;database=OfficeImport;trusted_connection=yes/02F3CFB8) succeeded as MSSQLSpatial.
OGR: OGROpen(office_locations.csv/02F3C808) succeeded as CSV.
OGR_MSSQLSpatial: Using column ogr_fid as FID for table office_locations.
OGR2OGR: 4977 features written in layer 'office_locations'
ODBC: SQLDisconnect()
VRT: 4977 features read on layer 'office_locations'.
CSV: 4977 features read on layer 'office_locations'.

Two thirds of the of the records have been silently skipped!

Finding the missing records

The id column in the CSV contains a unique id for each office. I can sort by that to find the missing records.

To order the rows in the database I used a command like this:

$ sqlcmd -d OfficeImport -Q "SELECT id, name FROM office_locations ORDER BY CAST(id AS INT);" | more
id   name


---- -------------------------------------------------------
11   Dublin - Airport
21   Dublin - Finglas
36   Brussels - Charleroi Airport
42   Crete - Chania Marna
79   Lesvos - Airport
83   Corfu - New Port
[...]

To order the rows in the spreadsheet I used Excel 2010's Sort feature on the Data tab.

I discovered that office 47 is the first missing record:

 "47","Fuerteventura - Airport","28.452","-13.87","ES","Aeropuerto Fue L.7, El Matorral.pto Del Rosario, Fuerteventura, 35610","Fuerteventura","1","FUE","0","35610","","",136

Isolating the skipped row

I created a new CSV file called test_import.csv that contains just office 47 and a header:

Id,Name,Lat,Lng,CountryCode,Address,CityName,Airport,AirportCode,RailwayStation,PostalCode,StateCode,StateProv,Country_Id
"47","Fuerteventura - Airport","28.452","-13.87","ES","Aeropuerto Fue L.7, El Matorral.pto Del Rosario, Fuerteventura, 35610","Fuerteventura","1","FUE","0","35610","","",136

And a new VRT called test_import.vrt that looks like this:

<OGRVRTDataSource>
    <OGRVRTLayer name="test_import">
        <SrcDataSource>test_import.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="Lng" y="Lat"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

ogr2ogr debug output shows that 1 feature was read and written:

$ ogr2ogr --debug ON -overwrite -f MSSQLSpatial  "MSSQL:server=.;database=Argus;trusted_connection=yes" test_import.vrt
OGR: OGROpen(test_import.vrt/03488318) succeeded as VRT.
OGR_MSSQLSpatial: EstablishSession(Connection:"server=.;database=Argus;trusted_connection=yes")
ODBC: SQLDriverConnect(DRIVER=SQL Server;server=.;database=Argus;trusted_connection=yes)
OGR_MSSQLSpatial: Using column ogr_fid as FID for table argus_locations.
OGR_MSSQLSpatial: Using column ogr_fid as FID for table location.
OGR_MSSQLSpatial: Using column ogr_fid as FID for table location_utf8.
OGR_MSSQLSpatial: Using column ogr_fid as FID for table office_locations.
OGR_MSSQLSpatial: Using column ogr_fid as FID for table test_import.
OGR: OGROpen(MSSQL:server=.;database=Argus;trusted_connection=yes/0349CFA8) succeeded as MSSQLSpatial.
OGR: OGROpen(test_import.csv/0349D868) succeeded as CSV.
MSSQLSpatial: DeleteLayer(test_import)
OGR_MSSQLSpatial: Using column ogr_fid as FID for table test_import.
OGR2OGR: 1 features written in layer 'test_import'
ODBC: SQLDisconnect()
VRT: 1 features read on layer 'test_import'.
CSV: 1 features read on layer 'test_import'.

This time office 47 is imported, but last time it was skipped.

Why is this row skipped in the full file but imported in the test file?

Why doesn't ogr2ogr acknowledge that any rows were skipped?

How many columns does each table have?

MappaGnosis asked: "How many columns does the single office (47) import's attribute table have and how many does the original import have?"

The input CSV file has 14 columns:

$ powershell -Command "(Import-Csv -Path 'office_locations.csv' | Get-Member -Type NoteProperty).Count"
14

The original import table is called office_locations, and the single office import table is called test_import. Each has 16 columns:

SELECT
  OBJECT_NAME(object_id) AS table_name,
  COUNT(*) AS column_count
FROM sys.columns
WHERE object_id IN (OBJECT_ID(N'test_import'), OBJECT_ID(N'office_locations'))
GROUP BY object_id;

table_name         column_count
-----------------  ------------
office_locations   16
test_import        16

ogr2ogr created one column for each of the fields in the CSV files, and generated two extra columns ogr_fid and ogr_geometry.

Importing just the ids and the coordinates

Darren Cope suggested: "Can you create a second .csv with all records, but strip out all of the columns except the ID and lat/lon columns? Try to import that, and see if all records come in. Sometimes there is an improperly delimited row somewhere that will confuse things, and is hard to pick out visually..."

I used this PowerShell command to extract just the id, lng, and lat columns from the office_locations.csv file:

$ powershell -Command "(Import-Csv -Path 'office_locations.csv') | Select Id, Lng, Lat | Export-Csv -NoTypeInformation -Encoding UTF8 -Path 'office_minimal.csv'"

It created a new CSV file called office_minimal.csv. It contains the minimal amount of data to locate and identify each office.

The first lines look like this:

"Id","Lng","Lat"
"7188","19.71271276","41.42108838"
"30768","19.832","41.332"
"52400","0.0","0.0"
"4650","1.548","42.511"
"152576","1.522","42.508"

I created a corresponding VR file called office_minimal.vrt. It looks like this:

<OGRVRTDataSource>
    <OGRVRTLayer name="office_minimal">
        <SrcDataSource>office_minimal.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="Lng" y="Lat"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

Using the new 'minimal' files, ogr2ogr imports all 14902 rows:

$ ogr2ogr --debug ON -overwrite -f MSSQLSpatial  "MSSQL:server=.;database=OfficeImport;trusted_connection=yes" office_minimal.vrt
OGR: OGROpen(office_minimal.vrt/03388328) succeeded as VRT.
OGR_MSSQLSpatial: EstablishSession(Connection:"server=.;database=OfficeImport;trusted_connection=yes")
ODBC: SQLDriverConnect(DRIVER=SQL Server;server=.;database=OfficeImport;trusted_connection=yes)
OGR_MSSQLSpatial: Using column ogr_fid as FID for table office_locations.
OGR_MSSQLSpatial: Using column ogr_fid as FID for table test_import.
OGR: OGROpen(MSSQL:server=.;database=OfficeImport;trusted_connection=yes/0339CFB8) succeeded as MSSQLSpatial.
OGR: OGROpen(office_minimal.csv/033A0F48) succeeded as CSV.
OGR_MSSQLSpatial: Using column ogr_fid as FID for table office_minimal.
OGR2OGR: 14902 features written in layer 'office_minimal'
ODBC: SQLDisconnect()
VRT: 14902 features read on layer 'office_minimal'.
CSV: 14902 features read on layer 'office_minimal'.
  • How many columns does the single office (47) import's attribute table have and how many does the original import have? – MappaGnosis Sep 3 '13 at 19:03
  • Can you create a second .csv with all records, but strip out all of the columns except the ID and lat/lon columns? Try to import that, and see if all records come in. Sometimes there is an improperly delimited row somewhere that will confuse things, and is hard to pick out visually... – Darren Cope Sep 3 '13 at 19:42
  • @MappaGnosis I added the section "How many columns does each table have?" to answer your question. Short answer: both tables have 16 columns. – Iain Samuel McLean Elder Sep 4 '13 at 11:06
  • @DarrenCope I added the section "Importing just the ids and the coordinates" to answer your question. When I do that, all the rows import successfully. Perhaps there is a missing delimiter in the original, as you suggested. Any tips on how to confirm that? – Iain Samuel McLean Elder Sep 4 '13 at 11:09
  • Are all offices from No.47 onwards missing? Maybe it is the record before that spoils the import. What happens if you delete the No.47 entry? The 5 rows you reported plus No. 47 import fine in QGIS. – AndreJ Sep 4 '13 at 11:09
4

There's a delimiter missing

While inspecting the file I noticed that Excel parsed some of the records into more than 14 columns:

enter image description here

View the full image here if it's unclear on Stack Exchange.

Office 151393 was parsed into 18 columns. I've highlighted the Address and RailwayStation columns because the values in these columns contain trailing double-quote characters.

CSV uses the double-quote as a control character, and I don't expect my data to have trailing double-quotes, so I suspect something is wrong with the formatting.

In office_locations.csv the record for office 151393 looks like this:

"151393","Yalta - Downtown","44.498","34.173","UA","Hotel "massandra", Drazhinskogo, 46, Yalta, 98600","Yalta","0","","0","98600","","",156

If we focus on how the Address value is encoded:

"Hotel "massandra", Drazhinskogo, 46, Yalta, 98600"

We see that the encoding contains unescaped double-quotes, which is an invalid format.

Presumably the intended encoding looked like this:

"Hotel ""massandra"", Drazhinskogo, 46, Yalta, 98600"

Working around the issue

PowerShell's Import-Csv cmdlet counts the correct number of rows:

$ powershell -Command "(Import-Csv -Path 'office_locations.csv').Count"
14902

For office 151393 it produces the same values in each column, trailing double-quotes included:

$ powershell -Command "Import-Csv -Path 'office_locations.csv' | Where id -eq '151393'"


Id             : 151393
Name           : Yalta - Downtown
Lat            : 44.498
Lng            : 34.173
CountryCode    : UA
Address        : Hotel massandra"
CityName       : Drazhinskogo
Airport        : 46
AirportCode    : Yalta
RailwayStation : 98600"
PostalCode     : Yalta
StateCode      : 0
StateProv      :
Country_Id     : 0

Apparently PowerShell can parse the file like Excel up to the first 14 columns. Values that don't correspond to a named field are silently dropped.

Export-Csv can write those 14 columns to a properly formatted CSV:

$ powershell -Command "Import-Csv -Path 'office_locations.csv' | Export-Csv -NoTypeInformation -Encoding UTF8 -Path 'office_ps_filter.csv'"

This is like the command I used to extract just the Id, Lng, and Lat columns. Because I removed the Select filter, all the columns are implicitly exported.

The command creates a new file called office_ps_filter.csv. In it the record for office 151393 looks like this:

 "151393","Yalta - Downtown","44.498","34.173","UA","Hotel massandra""","Drazhinskogo","46","Yalta","98600""","Yalta","0","","0"

This time, the trailing double-quotes in the Address and RailwayStation columns are properly escaped.

To import using ogr2ogr I create a corresponding VRT called office_ps_filter.vrt. It looks like this:

<OGRVRTDataSource>
    <OGRVRTLayer name="office_ps_filter">
        <SrcDataSource>office_ps_filter.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="Lng" y="Lat"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

ogr2ogr debug output shows that all 14902 records are written to SQL Server:

$ ogr2ogr --debug ON -overwrite -f MSSQLSpatial  "MSSQL:server=.;database=OfficeImport;trusted_connection=yes" office_ps_filter.vrt
OGR: OGROpen(office_ps_filter.vrt/03353F80) succeeded as VRT.
OGR_MSSQLSpatial: EstablishSession(Connection:"server=.;database=OfficeImport;trusted_connection=yes")
ODBC: SQLDriverConnect(DRIVER=SQL Server;server=.;database=OfficeImport;trusted_connection=yes)
OGR_MSSQLSpatial: Using column ogr_fid as FID for table office_locations.
OGR_MSSQLSpatial: Using column ogr_fid as FID for table office_minimal.
OGR_MSSQLSpatial: Using column ogr_fid as FID for table test_import.
OGR: OGROpen(MSSQL:server=.;database=OfficeImport;trusted_connection=yes/030DFC38) succeeded as MSSQLSpatial.
OGR: OGROpen(office_ps_filter.csv/0333FFB8) succeeded as CSV.
OGR_MSSQLSpatial: Using column ogr_fid as FID for table office_ps_filter.
OGR2OGR: 14902 features written in layer 'office_ps_filter'
ODBC: SQLDisconnect()
VRT: 14902 features read on layer 'office_ps_filter'.
CSV: 14902 features read on layer 'office_ps_filter'.

Conclusion

My CSV file was badly formatted. I should go to the source of the file to fix the formatting issue.

To answer the presenting question, ogr2ogr behaves strangely when the CSV input is badly formatted, without any indication that there is a formatting issue.

Import-Csv handles badly-formatted CSV files more robustly than ogr2ogr.

The Id, Lng, and Lat fields come before the Address field, so the bad formatting in the Address field doesn't affect them. I got lucky when I extracted just those columns and managed to import all the records.

XmlToCsv produces invalid CSV (Bonus Section)

This provides context to the question and explains how the invalid CSV was generated.

The source data was provided in XML format without a schema. Although dressed up as XML, the data was essentially tabular.

Each record is presented as an element, and each field is presented as an attribute.

I produced the CSV file we discussed here by passing the source file through XmlToCsv, a free utility that converts XML files to CSV.

In the source file the record for office 151393 looks like this:

<Location Id="151393" Name="Yalta - Downtown" Lat="44.498" Lng="34.173" CountryCode="UA" Address="Hotel &quot;massandra&quot;, Drazhinskogo, 46, Yalta, 98600" CityName="Yalta" PostalCode="98600" Airport="0" RailwayStation="0"/>

I copied the single element to a file called 151393.xml and passed it through XmlToCsv.Console`:

$ XmlToCsv.Console -xml 151393.xml -dir .

The utility produces one output file per type of element. For this input, It produced one file called Location.csv.

The output looks like this:

Id,Name,Lat,Lng,CountryCode,Address,CityName,PostalCode,Airport,RailwayStation
"151393","Yalta - Downtown","44.498","34.173","UA","Hotel "massandra", Drazhinskogo, 46, Yalta, 98600","Yalta","98600","0","0"

The output contains unescaped double-quotes that correspond to the XML-encoded double-quotes. In XML they look like this: &quot;.

Looks like XmlToCsv doesn't correctly handle double quotes embedded in field values.

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.