[postgis-users] Excel to Postgres

[Steve....@inspq.qc.ca]

Hi,
Every day I receive an Excel file containing lat long coordinates. I need to import it to postgis.
Currently, I save the Excel file in CSV format and I use ogr2ogr to import the contents into postgis. I would like to avoid human intervention.

I tried unsuccessfully  to define an ODBC link on the Excel file and use ogr2ogr with odbc to import it to postgres/postgis.
I got this error
[Microsoft][ODBC Excel Driver] External table is not in the expected format

Here is the command I sent
ogr2ogr -f "PostgreSQL" PG:"host=xxx user=xxx password=xxx dbname=geo" "ODBC:odbc_urg,test" -nln "testODBCxls" -overwrite

where test is the sheet name inside the excel file

Anyone has a better idea on how to programmatically import excel data into postgis?
thanks
Steve

[Jan Hartmann]

I never work with Excel, but using ODBC with Access is never a problem. Would that be an acceptable solution, e.g. linking the Excel data to an Access table and sending that to PostGIS?

Jan

_______________________________________________
postgis-users mailing list
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
  

[Mike Toews]

Hi Steve,

If you have done some dabbling with Python (or you are willing to
dabble), use xlrd to read Excel files
(http://pypi.python.org/pypi/xlrd) then either pyodbc (recommended for
Windows) or psycopg2 to connect to Postgres and transfer the data
over. Make it into a script, then repeat as necessary. If you want to
get user-friendly, then make a shortcut to a bat file that runs the
python script, then you can import the file by dragging-dropping an
Excel file over the shortcut.

-Mike

> _______________________________________________
> postgis-users mailing list
> postgi...@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>

--

Sent from Vancouver, British Columbia, Canada

[Stefan Keller]

Assuming you have Windows you could either use ODBC and make a system
DSN entry :
> c:\windows\system32\odbcconf /a CONFIGSYSDSN "Microsoft Excel Driver (*.xls)" "DSN=test|DBQ=c:\test.xls|DefaultDir=C:|Description=Test"

or use Visual Basic as a macro in a master Excel

or use Java and http://poi.apache.org/ API for Microsoft documents.

-S.

2010/2/15 <Steve....@inspq.qc.ca>:

[Yves Moisan]

> then either pyodbc (recommended for
> Windows) or psycopg2 to connect to Postgres

I use psycopg2 on windows. The only problem with it is finding the
right compiled version (function of PG and Python versions used).

Yves

[Kevin Neufeld]

If your data was in CSV format, you could script PostgreSQL's COPY command to import the file to a table instead of
using ogr2ogr.

http://www.postgresql.org/docs/8.4/static/sql-copy.html

But to avoid human intervention to create the CSV in the first place, a purchased solution might be to create an FME
script to read directly from Excel and write to PostGIS.
http://www.safe.com/products/desktop/formats/microsoft-excel/index.php

Kevin

[Kis János Tamás]

Why don't use a small perl script with DBD:Excel and DBD::Pg packages?

2010/2/15 <Steve....@inspq.qc.ca>: