[postgis-users] ogr_fdw create data source connection error for directory containing XLSX files
Regina Obe
lr at pcorp.us
Tue Jul 14 15:45:19 PDT 2020
I dont think so. If it is possible Id be very interested.
It sees an XLSX as a database and each worksheet is a table.
The hack Ive 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
More information about the postgis-users
mailing list