PostgreSQL team - please comment if you see a better way to do this or
if I'm getting something wrong here!
"Martin A. Marques" <martin@math.unl.edu.ar> writes:
> Is there any utilitie (for Linux if it can be) to pass from excell
> tables to postgres database tables?
We are just beginning to solve a similar problem, although the
PostgreSQL server is running on FreeBSD rather than Linux.
Goal is to migrate some technical info stores from .XLS files to
tables on PostgreSQL where everyone who needs them can get at them.
Our plan is:
1. Mirror layout of the current .XLS tables as a pgsql schema. We are
keeping it simple - all columns are varchar() and no indexing is used.
This schema is a throw-away, designed to make it as idiot proof and
effortless as possible for the Excel user to drop his tables into the
database.
2. Export .XLS files as comma-delimited text into Access.
3. Install postodbc on the MS box and link an Access database to the
provisional schema in #1. The postodbc bits seem a bit crufty (pgsql
6.5.3) and are alleged not to work if you index on data types other
than int, so we again plan to stay away from indexing at this point.
4. Drop the Access local tables into PostgreSQL. At this point, our MS
user can use the tables almost as easily as if they were on Excel.
That is one major goal and why we didn't just export .XLS comma
delimited into pgsql - minimal squawking from the MS user.
5. Have a production schema which is better normalized, etc than the
raw input one derived from the ad hoc spreadsheets.
6. Use SQL scripts (pg-perl or such) to migrate the raw tables
imported in #4 into the production tables. Link the production tables
to Access for our MS guy.
Status: #1 is done. #3 is done in that we can read/write pgsql tables
from MS Access. We expect #2 and #4 to happen today or tomorrow.
When it's all done, we hope to have: a) our MS user happy using
Access; b) engineers able to use intranet interface to the database
for common queries and updates; c) net admin guys happy because they
can do real SQL and tie the data into the rest of the support
database. PostgreSQL is a Good Thing!