Reading TXT file as CSV with ST_Read()

[SrNetoChan]

I am trying to use st_read to read a *.txt as a CSV without needing to rename the file.

This works fine:

SELECT * FROM st_read('CarrisMetropolitana/stops.csv', allowed_drivers=['CSV']);

But the original file as a txt extension, so this fails

select * from st_read('CarrisMetropolitana/stops.txt',allowed_drivers=['CSV']);

IO Error: GDAL Error (4): `CarrisMetropolitana/stops.txt' not recognized as a supported file format.

Of course I can just rename all files, but what I really wanted to do is read it directly from a compressed GTFS zip file, which fails:

SELECT * FROM st_read('/vsizip/CarrisMetropolitana.zip/stops.txt', allowed_drivers=['CSV']);

But if inside the ziz there's a the same file as with csv extentionit works pretty well.

SELECT * FROM st_read('/vsizip/CarrisMetropolitana.zip/stops.csv', allowed_drivers=['CSV']);

[Mause]

Is there a reason you aren't using the in-built csv support in duckdb, rather than the support in the spatial extension?

[SrNetoChan]

@Mause, thanks for your interest. The reason for using st_read is to be able to read files from inside a zip file. Something that is supported by GDAL Virtual File Systems. It also allow to read tat directly from a https hosted file. Which was my final goal. I wasn't able to find a way to open files from a zip file using the in-built csv support. Am I missing something?

[SrNetoChan]

As far as I was able to find only gzipped files would work. I had no success with normal ZIP files.

[Maxxen]

I could have sworn we ship GDAL's GTFS driver, but apparently we have it disabled. Ill push a fix in my next PR that enables it.

[rouault]

I am trying to use st_read to read a *.txt as a CSV

This will be fixed with GDAL 3.10 thanks to https://github.com/OSGeo/gdal/commit/06e3fb67bb6eecff78ac2878e4ab318ecd7d849d

[SrNetoChan]

Thanks @rouault !! That will be very handy!