[postgis-users] ogr_fdw create data source connection error for directory containing XLSX files

[Regina Obe]

I don’t think so.  If it  is possible I’d be very interested.

It sees an XLSX as a database and each worksheet is a table.

The hack I’ve used in past (kinda klunky) is to use PostgreSQL built in COPY
FROM to read the list of files and dump it in a table.

So something like this:

CREATE TABLE tmp_docs(file_name text PRIMARY KEY);

COPY tmp_docs FROM PROGRAM 'ls /data/*'  WITH (format 'csv');

And then write a plpgsql procedure that for each record in the table forms
the  create server and foreign table and then drops it when done.





From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of karsten
Sent: Tuesday, July 14, 2020 5:01 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] ogr_fdw create data source connection error for
directory containing XLSX files

Hi All,
 
I am learning how to use the get feature of foreign data wrappers and
successfully connected to various ogr data sources using ogr_fdw on postgres
12 on an ubuntu 16.04 server.
 
I wanted to use this to import a whole lot of data from XLSX files and was
able to get one table connected as below perfectly all good to go ...
 
CREATE SERVER cmein_xlsx
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
        datasource '/var/xy/mapdata/source/geodata/xy1.xlsx',
        format 'XLSX' );
 
IMPORT FOREIGN SCHEMA ogr_all
    LIMIT TO("Raw Data")
FROM SERVER cmein_xlsx INTO public
OPTIONS(launder_table_names 'false', launder_column_names 'true');
 
However I read that (at least with shape files) it is possible to
alternatively to connect to a directory (and such to all shape file inside)
by setting the data source to that path. So for my XLSX files I tried as
below:
 
CREATE SERVER all_cmein_xlsx
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
        datasource ''/var/xy/mapdata/source/geodata,
        format 'XLSX',
        config_options 'CPL_DEBUG=ON' );  
 
The directory ''/var/xy/mapdata/source/geodata is readable by the postgres
user ...
But the query above gives me the following error :
 
ERROR: unable to connect to data source "/var/xy/mapdata/source/geodata" SQL
state: HV00N
 
Is it even possible to connect via ogr_fdw to a directory of multiple XLSX
files like that 
and if so what am I missing to make it work ?
 
Karsten