3

I'm trying to convert a CSV to PostgreSQL with ogr2ogr, but the CSV is coming from an API:

http://api.statbank.dk/v1/data/FT/CSV?delimiter=Semicolon&HOVEDDELE=*&Tid=*

I have tried following ogr2ogr:

ogr2ogr -f PostgreSQL PG:"dbname=postgres host=localhost port=5432 user=postgres password=xxx" CSV:"http://api.statbank.dk/v1/data/FT/CSV?delimiter=Semicolon&HOVEDDELE=*&Tid=*" -nln FT -lco SEPARATOR=SEMICOLON

But are getting

Unable to open datasource `CSV:http://api.statbank.dk/v1/data/FT/CSV?delimiter=Semicolon&HOVEDDELE=*&Tid=*' with the following drivers.....

I can get it working if I download the CSV and use ogr2ogr, but would prefer to fetch it straight from the API to reduce the steps in my script.

4

Addition due to comments

Using /vsicurl_streaming/ and CSV: before works. You can try using the following command:

ogr2ogr -f "ESRI Shapefile" fake.shp CSV:"/vsicurl_streaming/http://api.statbank.dk/v1/data/FT/CSV?delimiter=Semicolon&HOVEDDELE=*&Tid=*"

First answer

It seems it's not possible. According to some tests, GDAL can't guess correctly the file input format from the API endpoint provided. I didn't find a way to inform GDAL of the input format (possible?). You will see the tests I've done below (did not use PostgreSQL for output, instead use ESRI Shapefile)

  • Work because OGR/GDAL guess the input format with the extension

    ogr2ogr -f "ESRI Shapefile" fake.shp "/vsicurl/http://samplecsvs.s3.amazonaws.com/Sacramentorealestatetransactions.csv"
    
  • Do not work because OGR/GDAL unable to guess the input format (no extension)

    ogr2ogr -f "ESRI Shapefile" fake.shp "/vsicurl/http://api.statbank.dk/v1/data/FT/CSV?delimiter=Semicolon&HOVEDDELE=*&Tid=*"
    
  • According to gdal.org/1.11/ogr/drv_csv.html: Starting with GDAL 1.8.0, for files structured as CSV, but not ending with .CSV extension, the 'CSV:' prefix can be added before the filename to force loading by the CSV driver. Tried ogr2ogr -f "ESRI Shapefile" fake.shp CSV:"/vsicurl/http://api.statbank.dk/v1/data/FT/CSV?delimiter=Semicolon&HOVEDDELE=*&Tid=*" but are getting same error. – Baffioso Nov 15 '16 at 11:17
  • I've tried this one but like you, it was not working the expected way for me. It does not works with remote URL maybe. – ThomasG77 Nov 15 '16 at 15:04
  • Even Rouault from got it working by using /vsicurl_streaming/. Se answer below – Baffioso Nov 16 '16 at 12:24
  • You should accept your own solution. – ThomasG77 Nov 16 '16 at 12:33
  • Stackexchange says that I have to wait for 6 hours before it's possible. I will accept later. – Baffioso Nov 16 '16 at 12:45
1

Answer from GDAL osgeo mailing list: The server doesn't seem to support range GET requests, but I see this works if you use the /vsicurl_streaming/ virtual file system (and CSV reading is in streaming, so this matches well)

ogrinfo  -ro CSV:"/vsicurl_streaming/http://api.statbank.dk/v1/data/FT/CSV?delimiter=Semicolon&HOVEDDELE=*&Tid=*" -al -q

Note that -lco SEPARATOR=SEMICOLON is useless. -lco stands for layer creation option, so this should be for the output driver, here PostgreSQL

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.