Re: How to insert .xls files into database

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(dot)saraf(at)gmail(dot)com

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