Re: How to import a CSV file (originally from Excel) into a Postgresql 8.0.7 database

On Tue, 2006-04-11 at 23:13 +0200, Magnus Hagander wrote:

Hello,

I am trying to import an Excel file in my database (8.0.7). I
think I am supposed to create an CSV file out of my Excel
file and somehow, feed the database with it. My pronblem is
that I don't really know how to do it... :( Can anyone give
me a hint about how to do that?
One last "detail", the Excel files contains roughly 45.000
lines and 15 columns. So, I need a fast and efficient method.

Hi!

Is your problem with how to generate the CSV file from Excel, or with
how to import it into PostgreSQL?

For generation in Excel, you can just use File->Save, and select CSV as
the format. You can use either CSV or tab delimited, really.

Then to get it into postgresql, use the \copy command in psql (I'm
assuming your client is on windows, since you're using Excel. \copy will
run the process from the client, and will load it into your sever
regardless of platform). You can specify which delimiter to use there,
etc. From the example below, I'd guess you want something along the line
of:

\copy "Flight Traffic" from yourfile.csv delimiter as ',' csv quote as
'"'

(might need some adaption, of course)

Loading 45,000 lines is trivial for copy, it shouldn't take noticable
time at all.

----
along these lines - can I do something similar (CSV file) but 'update' 2
or 3 columns?

Craig

For generation in Excel, you can just use File->Save, and select CSV as
the format. You can use either CSV or tab delimited, really.

I am not sure if it matters with postgresql, but other programs require (MS-DOS) CSV format rather
than the initial CSV choice.

Regards,

Richard

Hello,

Thank you for all your answers.
I did indeed generate a .csv from OpenOffice (Excel could do the job as well)
and I imported it into Postgresql using 'copy'.
I had to put the .csv files into /tmp to avoid permissions issues but it went
fine and fast.
To import 45000 lines took about a second. This is impressive...

Thanks for your help.

For generation in Excel, you can just use File->Save, and select CSV as
the format. You can use either CSV or tab delimited, really.

I am not sure if it matters with postgresql, but other programs require
(MS-DOS) CSV format rather than the initial CSV choice.

Regards,

Richard

--
**********************************************************************
Daniel TOURDE E-mail : daniel.tourde@foi.se
Tel : +46 (0)8-55 50 32 12
Fax : +46 (0)8-55 50 30 68
Cellular : +46 (0)70-849 93 40
FOI, Swedish Defence Research Agency; Systemteknik
Department of Aviation Environmental Research
SE-164 90 Stockholm, Sweden
**********************************************************************

\copy "Flight Traffic" from yourfile.csv delimiter as ','

csv quote as

'"'

(might need some adaption, of course)

Loading 45,000 lines is trivial for copy, it shouldn't take

noticable

time at all.

----
along these lines - can I do something similar (CSV file) but
'update' 2 or 3 columns?

I'd use COPY to a temp table, then run a normal UPDATE on that.

//Magnus

On Wed, 2006-04-12 at 17:57 +0200, Magnus Hagander wrote:

\copy "Flight Traffic" from yourfile.csv delimiter as ','

csv quote as

'"'

(might need some adaption, of course)

Loading 45,000 lines is trivial for copy, it shouldn't take

noticable

time at all.

----
along these lines - can I do something similar (CSV file) but
'update' 2 or 3 columns?

I'd use COPY to a temp table, then run a normal UPDATE on that.

----
is that a pg 8.x thing? I'm using 7.4.x (RHEL)

th-db_development=# \copy "clients_temp" from representatives.csv
delimiter as ',' csv quote as '"'
ERROR: syntax error at or near "CSV" at character 53
LINE 1: ...PY "clients_temp" FROM STDIN USING DELIMITERS ',' CSV
QUOTE ...
^
\copy: ERROR: syntax error at or near "CSV" at character 53

Craig