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

[karsten]

Thanks for the hint.
Yes that is a route via converting to csv one can go and maybe also script. 
I took a stab at creating a simple bash script to iterate over a directory
of xlsx files and creating connection SQL syntax via the 
ogr_fdw_info utility, writing that into a temp SQL file and then running
that via psql. Maybe not really pretty but it works - see below. 
Maybe I could try to wrap that into a custom psql function ...
Cheers
Karsten
--------------------
##!/bin/bash
fdwservername="myserver"
exceltabname="raw_data"
databasename="cmein"
tempfile="load_fdw_exceltabs.sql"
# remove file if exists
cmd="rm $tempfile"
echo "removing old file $tempfile"
$cmd
for f in *.xlsx
do
  file=${f##*/}
  fbase=\"${file%%.*}\"
  twoquotes=\"\"
  onequote=\"
  fullexcelfilewithpath="$PWD/$f"
  excel_table_source="datasource '"
  echo "Processing $f file, creating SQL to create foreign table defs via
ogr_fdw_info utility ..."
  #  ogr_fdw_info connand for each file 
  ofdwcommand=$(ogr_fdw_info -s $PWD/$f -l "RAW DATA")
  # replace all server and table names and quotes in ogr_fdw_info utility
output
  ofdwcommand=${ofdwcommand//$fdwservername/$fbase}
  ofdwcommand=${ofdwcommand//$exceltabname/$fbase}
  ofdwcommand=${ofdwcommand//$twoquotes/$onequote}
  echo "saving SQL to $tempfile"
  printf "$ofdwcommand" >> "$tempfile"
done
psqlcommand="psql -d $databasename -U postgres -f $tempfile"
echo "running $psqlcommand ..."
$psqlcommand
--------------

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
<https://lists.osgeo.org/mailman/listinfo/postgis-users> ] On Behalf

Of karsten

Sent: Tuesday, July 14, 2020 5:01 PM

To: postgis-users at lists.osgeo.org
<https://lists.osgeo.org/mailman/listinfo/postgis-users> 

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 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200715/2f025214/attachment.html>