Обсуждение: How to import a CSV file (originally from Excel) into a Postgresql 8.0.7 database

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.

Here is my database:

--
-- PostgreSQL database dump
--

SET client_encoding = 'UNICODE';
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

--
-- Data for Name: Flight Traffic; Type: TABLE DATA; Schema: public; Owner: ted
--

COPY "Flight Traffic" ("AR", "ICAOAIRP", "MOTORTYP", "VIKT", "ICAOTYP",
"FROMTO", "ANTAL", "CLASS", "SUBCLASS", "BULLERKLASS", "MILJOKLASS",
"ANTAL_PAX", "ARRDEP", "INTDOM", "KUNDNAMN") FROM stdin;
2004    ESDF    -       0       RALL    ESME    1       P       F       3
0       0       A       D       FORSBERG
\.


--
-- PostgreSQL database dump complete
--


And here is the .CSV i try to import:

2004,"ESDF","-",0,"RALL","ESME",1,"P","F",3,0,0,"A","D","FORSBERG, N MARTIN"



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

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


If you need to load things regularly, you can use the excel ODBC driver
and write a small script to transfer it over to get rid of the manual
steps.

//Magnus

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