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.

Improve this question

2 Answers 2

Reset to default
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=*"
Improve this answer
5
  • 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, 2016 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, 2016 at 15:04
  • Even Rouault from got it working by using /vsicurl_streaming/. Se answer below
    – Baffioso
    Nov 16, 2016 at 12:24
  • You should accept your own solution.
    – ThomasG77
    Nov 16, 2016 at 12:33
  • Stackexchange says that I have to wait for 6 hours before it's possible. I will accept later.
    – Baffioso
    Nov 16, 2016 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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.