Обсуждение: How to insert .xls files into database

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.

Thanks
Parang Saraf
parang.saraf@gmail.com

-----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-----

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:
> 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.
>
> Thanks
> Parang Saraf
> parang.saraf@gmail.com

--
Adrian Klaver
aklaver@comcast.net