Re: How to insert .xls files into database

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(at)t-zones(dot)sk wrote:
> > [mailto:pgsql-general-owner(at)postgresql(dot)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(at)comcast(dot)net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
Adrian Klaver
aklaver(at)comcast(dot)net