Import to excel to postgres based website?

novnov wrote:

The answer may or many not be very postgres specific but...what are some
possible routes that I could take to allows users to upload data from excel
into a postgres via a website? I've never tried anything like this before.
Are there standard modules that might help with this or will I have to
create from scratch? I am obviously hoping to find something I can just plug
in. Users would be expected to have the xls ordered properly for the routine
to work.

You don't say what scripting language you're using, or what platform
you're running on.

If you have a Windows-based server, you could just install Excel and use
ODBC and some macros to open the uploaded file and process it.

If you're on a non-Windows platform then you'll want a module that can
read Excel files. There are various Perl modules on search.cpan.org that
might work for you.

Failing that, you can get people to save as a tab-separated-values file
(possibly via a macro so they just have one button to push). That is
easy to import via COPY.

--
Richard Huxton
Archonet Ltd

On Jul 22, 2007, at 11:26 PM, novnov wrote:

The answer may or many not be very postgres specific but...what are
some
possible routes that I could take to allows users to upload data
from excel
into a postgres via a website? I've never tried anything like this
before.
Are there standard modules that might help with this or will I have to
create from scratch? I am obviously hoping to find something I can
just plug
in. Users would be expected to have the xls ordered properly for
the routine
to work.

It's something that would be pretty easy, given the right web
platform, quite
hard using the wrong one. Using perl, say, it would be less than a
hundred
lines of code. (Take uploaded file, crack xls format to extract data
using one
of the several CPAN excel reader modules, use DBI to insert it into
the database).

Uploading CSV (comma separated values) is likely to be easier in
languages
that don't happen to have support for xls, but CSV can only represent
a small
subset of xls.

If the webserver itself it running on windows then there's all sorts
of games you
can play by remote controlling an instance of Excel, but doing that
tends to
be pretty fragile.

Cheers,
Steve

Your easiest route will be to have them export a csv file out of excel and
write a script to import the csv into the database.

On Jul 22, 2007, at 11:27 PM, novnov wrote:

The answer may or many not be very postgres specific but...what are
some
possible routes that I could take to allows users to upload data
from excel
into a postgres via a website? I've never tried anything like this
before.
Are there standard modules that might help with this or will I have to
create from scratch? I am obviously hoping to find something I can
just plug
in. Users would be expected to have the xls ordered properly for
the routine
to work.

How about converting the Excel file to CSV and loading that in with
COPY?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

I would recomend MS Access/ODBC.
It works very fine both with linked Excel spreadsheets and PostgreSQL
tables.
Therefore, you can easily automate data import from Excel to PostgreSQL via
Acess.

----- Original Message -----
From: "Steve Atkins" <steve@blighty.com>
To: "pgsql-general General" <pgsql-general@postgresql.org>
Sent: Monday, July 23, 2007 5:29 PM
Subject: Re: [GENERAL] Import to excel to postgres based website?