Thread: Import to excel to postgres based website?
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. -- View this message in context: http://www.nabble.com/Import-excel-to-postgres-based-website--tf4127759.html#a11738475 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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
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? > > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
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 7/23/07, novnov < novnovice@gmail.com> 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.
--
View this message in context: http://www.nabble.com/Import-excel-to-postgres-based-website--tf4127759.html#a11738475
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--------------------------- (end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly