Обсуждение: re: How to insert .xls files into database
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver > > I guess the solution depends on what is a 'large amount of data'. The > most time consuming part is going to be converting the single data > elements at the top of each sheet into multiple elements. I would > create columns for the data in the sheet. At the same time I would > order the columns to match the database schema. Then it would a matter > of cut and paste to fill the columns with the data. The event id's > could be renumbered using Excel's series generator to create a non > repeating set of id's. If the amount of data was very large it might > pay to create some macros to do the work. Once the data was filled in > you would have a couple of choices. One, as mentioned by Ron would be > to use OpenOffice v2 to dump the data into the database. The other > would be to save the data as CSV and use the psql \copy command to > move the data into the table. > On Friday 07 July 2006 09:40 am, Parang Saraf wrote: Evrything You described is familiar to me, except the OpenOffice v2 dump - could You explain this more in details pls? Itried to do it many times, without success. Thank You Tomas [del] > -- > Adrian Klaver > aklaver@comcast.net
One option is to write a Python translator to create CSV files, or even an uploader to go directly from the Excel files to the database. There is at least one module to read Excel files, in all their complexity: http://cheeseshop.python.org/pypi/xlrd/0.5.2 and a number of Postgres modules: http://wiki.python.org/moin/PostgreSQL - John Burger MITRE
So far I have only got this to work with the Postgres SDBC driver- http://dba.openoffice.org/drivers/postgresql/index.html 1) Open the Data Source (F4) window in the spreadsheet. 2) Make a connection to the database. I usually do this by opening a table. This is fairly important, otherwise when you do the import OO will try to establish connection at the same as it is importing the data and end up hanging. 3)Select the data in the spreadsheet you want to import. The column headings will become the field names. NOTE: You want the data to be as plain as possible. OpenOffice tends to auto format the cell contents i.e changing 1/2 to a typeset version, changing " to true quotes. This introduces format codes that don't translate well. 4)Drag the selected data to the Tables heading of the appropriate data source. At this point a dialog form will pop up. 5)Name the table. You will need to schema qualify i.e. public.test_table. Make sure you select Create a Primary Key. Without the table will be created but the data will not be imported. This why I use the SDBC driver. The JDBC driver does not seem to support this step. 6) Select Next. Here you can choose the columns you wish to import. 7) Select Next. Here you can modify the data types of the columns. 8) Select Create. The table should be created and the data inserted. On Friday 07 July 2006 03:41 pm, lanczos@t-zones.sk wrote: > > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver > > > > I guess the solution depends on what is a 'large amount of data'. The > > most time consuming part is going to be converting the single data > > elements at the top of each sheet into multiple elements. I would > > create columns for the data in the sheet. At the same time I would > > order the columns to match the database schema. Then it would a matter > > of cut and paste to fill the columns with the data. The event id's > > could be renumbered using Excel's series generator to create a non > > repeating set of id's. If the amount of data was very large it might > > pay to create some macros to do the work. Once the data was filled in > > you would have a couple of choices. One, as mentioned by Ron would be > > to use OpenOffice v2 to dump the data into the database. The other > > would be to save the data as CSV and use the psql \copy command to > > move the data into the table. > > On Friday 07 July 2006 09:40 am, Parang Saraf wrote: > > Evrything You described is familiar to me, except the OpenOffice v2 dump - > could You explain this more in details pls? I tried to do it many times, > without success. > > Thank You > > Tomas > > [del] > > > -- > > Adrian Klaver > > aklaver@comcast.net > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Adrian Klaver aklaver@comcast.net
On Fri, 2006-07-07 at 22:41 +0000, lanczos@t-zones.sk wrote:
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver
> >
> > I guess the solution depends on what is a 'large amount of data'. The
> > most time consuming part is going to be converting the single data
> > elements at the top of each sheet into multiple elements. I would
> > create columns for the data in the sheet. At the same time I would
> > order the columns to match the database schema. Then it would a matter
> > of cut and paste to fill the columns with the data. The event id's
> > could be renumbered using Excel's series generator to create a non
> > repeating set of id's. If the amount of data was very large it might
> > pay to create some macros to do the work. Once the data was filled in
> > you would have a couple of choices. One, as mentioned by Ron would be
> > to use OpenOffice v2 to dump the data into the database. The other
> > would be to save the data as CSV and use the psql \copy command to
> > move the data into the table.
> > On Friday 07 July 2006 09:40 am, Parang Saraf wrote:
>
> Evrything You described is familiar to me, except the OpenOffice v2
> dump - could You explain this more in details pls? I tried to do it
> many times, without success.
>
> Thank You
>
> Tomas
Does the "OpenOffice v2 dump" convert the date correctly when
exporting into PostgreSQL?
The date in .xls when using excel is exported to CSV as a number
which is tricky to convert to a date. This is what I use :
date_pli('epoch'::date, date_num::integer - 25569) AS date_fmt
The number "25569" is a fudge factor, that can be different between
dumps, but stays consistent through the dump. I usually adjust it
and compare the result to the value shown in excel until I get a
match.
Dates don't transfer correctly. When I try it the date moves two days ahead.
On Wednesday 12 July 2006 08:43 am, Guy Fraser wrote:
> On Fri, 2006-07-07 at 22:41 +0000, lanczos@t-zones.sk wrote:
> > > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver
> > >
> > > I guess the solution depends on what is a 'large amount of data'. The
> > > most time consuming part is going to be converting the single data
> > > elements at the top of each sheet into multiple elements. I would
> > > create columns for the data in the sheet. At the same time I would
> > > order the columns to match the database schema. Then it would a matter
> > > of cut and paste to fill the columns with the data. The event id's
> > > could be renumbered using Excel's series generator to create a non
> > > repeating set of id's. If the amount of data was very large it might
> > > pay to create some macros to do the work. Once the data was filled in
> > > you would have a couple of choices. One, as mentioned by Ron would be
> > > to use OpenOffice v2 to dump the data into the database. The other
> > > would be to save the data as CSV and use the psql \copy command to
> > > move the data into the table.
> > > On Friday 07 July 2006 09:40 am, Parang Saraf wrote:
> >
> > Evrything You described is familiar to me, except the OpenOffice v2
> > dump - could You explain this more in details pls? I tried to do it
> > many times, without success.
> >
> > Thank You
> >
> > Tomas
>
> Does the "OpenOffice v2 dump" convert the date correctly when
> exporting into PostgreSQL?
>
> The date in .xls when using excel is exported to CSV as a number
> which is tricky to convert to a date. This is what I use :
>
> date_pli('epoch'::date, date_num::integer - 25569) AS date_fmt
>
> The number "25569" is a fudge factor, that can be different between
> dumps, but stays consistent through the dump. I usually adjust it
> and compare the result to the value shown in excel until I get a
> match.
>
>
>
> ---------------------------(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
--
Adrian Klaver
aklaver@comcast.net
On Thu, 2006-13-07 at 06:52 -0700, Adrian Klaver wrote: > Dates don't transfer correctly. When I try it the date moves two days ahead. Thanks for the heads up, I will continue to avoid using Open Office for Spreadsheet and DB activities. I prefer Gnumeric for Spreadsheet activities, and am very comfortable using psql for db activities.