Re: How to insert .xls files into database

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Parang Saraf wrote:
> Hey,
>
> I am using Postgresql 8.1.4 on windows. I have a large amount of data
> stored
> in .xls files which I want to insert into my database.
>
> The columns in .xls files are not exactly compatible with the database
> schema. For example the event_id in every .xls file starts with 1 while for
> my database event_id is the primary key. Also, there are some information
> like event_type, event_location that are particular to every .xls file and
> thus they have been mentioned only once in the .xls file but in the
> database
> there exists a separate column for them.
>
> For more clarification I am giving my database schema and attaching a
> sample
> .xls file.
>
> My database schema is as follows :
> {
> event_id int4 NOT NULL,
> buoy char(1) NOT NULL,
> deployment varchar(40),
> depth int4 NOT NULL,
> event_type varchar(64),
> model_info_id varchar(256),
> start_date float8 NOT NULL,
> start_date_sd float8,
> end_date float8 NOT NULL,
> end_date_sd float8,
> mean float8,
> variance float8,
> max float8,
> min float8,
> event varchar(20) NOT NULL,
> depth_type varchar(20) NOT NULL,
> buoy_location geometry,
> duration float8,
> Amplitude_sd float8,
> }
>
> .xls file is in the attachment. Now as you can see all the bold attributes
> are specified only once in the .xls files. And all the bold+italics one
> have
> to be manipulated a bit before storing. Even event_id in every .xls file
> starts with 1 but as this is a primary key I have to manipulate this also.
>
> I think if I can transform and manipulate each row into insert statements
> then I can insert the data into my database. Please guide me how to do
> this.
> Or if there is any another way of doing this.
>
> I am relatively new in this field so, please dont get offended if this
> problem is quite obvious.

OpenOffice.org v2 can interface .xls files with PostgreSQL.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFErqcHS9HxQb37XmcRAj6bAKCRGCZMeKXbaIUewBFAPDnko8t/kACfU+sa
7EiEI+V2LEGD1OKJh+8IDeU=
=Ahtm
-----END PGP SIGNATURE-----