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@xxxxxxxxx
Attachment:
A1_TS_Sigma-Tdensity_multi-year_anomaly_negative.xls
Description: MS-Excel spreadsheet
