[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] ogr_fdw create data source connection error for directory containing XLSX files
From: "karsten" <karsten () terragis ! net>
Date: 2020-07-15 21:51:14
Message-ID: 28D5A8C90F3B4C85BF8BE563D2B3FC2F () terragispc
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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
[Attachment #5 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 11.00.9600.19597"></HEAD>
<BODY>
<DIV dir=ltr align=left><PRE><SPAN class=812044521-15072020>Thanks for the \
hint.</SPAN></PRE><PRE><SPAN class=812044521-15072020>Yes that is a route via \
converting to csv one can go and maybe also script. <BR></SPAN><SPAN \
class=812044521-15072020>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 \
<BR></SPAN><SPAN class=812044521-15072020>ogr_fdw_info utility, writing that into a \
temp SQL file and then running that via psql. Maybe not really pretty but it works - \
s</SPAN><SPAN class=812044521-15072020>ee below. <BR>Maybe I could try to wrap that \
into a custom psql function ...</SPAN></PRE><PRE><SPAN \
class=812044521-15072020>Cheers<BR></SPAN><SPAN \
class=812044521-15072020>Karsten</SPAN></PRE><PRE><SPAN \
class=812044521-15072020></SPAN>-<SPAN \
class=812044521-15072020>-------------------<BR></SPAN><SPAN \
class=812044521-15072020>##!/bin/bash<BR>fdwservername="myserver"<BR>exceltabname="raw_data"<BR>databasename="cmein"<BR>tempfile="load_fdw_exceltabs.sql"<BR># \
remove file if exists<BR>cmd="rm $tempfile"<BR>echo "removing old file \
$tempfile"<BR>$cmd<BR>for f in *.xlsx<BR>do<BR> file=${f##*/}<BR> \
fbase=\"${file%%.*}\"<BR> twoquotes=\"\"<BR> onequote=\"<BR> \
fullexcelfilewithpath="$PWD/$f"<BR> excel_table_source="datasource '"<BR> \
echo "Processing $f file, creating SQL to create foreign table defs via ogr_fdw_info \
utility ..."<BR> # ogr_fdw_info connand for each file <BR> \
ofdwcommand=$(ogr_fdw_info -s $PWD/$f -l "RAW DATA")<BR> # replace all server \
and table names and quotes in ogr_fdw_info utility output<BR> \
ofdwcommand=${ofdwcommand//$fdwservername/$fbase}<BR> \
ofdwcommand=${ofdwcommand//$exceltabname/$fbase}<BR> \
ofdwcommand=${ofdwcommand//$twoquotes/$onequote}<BR> echo "saving SQL to \
$tempfile"<BR> printf "$ofdwcommand" >> \
"$tempfile"<BR>done<BR>psqlcommand="psql -d $databasename -U postgres -f \
$tempfile"<BR>echo "running $psqlcommand ..."<BR>$psqlcommand<BR></SPAN><SPAN \
class=812044521-15072020>--------------</SPAN></PRE><PRE><BR>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:<A \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users">postgis-users-bounces \
at lists.osgeo.org</A>] On Behalf Of karsten
Sent: Tuesday, July 14, 2020 5:01 PM
To: <A href="https://lists.osgeo.org/mailman/listinfo/postgis-users">postgis-users at \
lists.osgeo.org</A>
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 </PRE></DIV></BODY></HTML>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic