Re: help with COPY from .csv file into database - Mailing list pgsql-novice

From Oliver Elphick
Subject Re: help with COPY from .csv file into database
Date
Msg-id 1091542964.31602.2151.camel@linda
Whole thread Raw
In response to help with COPY from .csv file into database  (mike <michael@floog.net>)
Responses Re: help with COPY from .csv file into database
List pgsql-novice
On Tue, 2004-08-03 at 13:15, mike wrote:
> I am getting started with Postgres and cannot copy a database that was
> converted to a .csv file into my postgres database titled TEST_DB.

...

> COPY matters FROM /home/floog/TEST_DB.csv;
>
> But I don't think the data transferred properly because when I try to
> look at any column or row I get the following:

...[empty table]...

If there were rows in the input and COPY produced an empty table, there
was presumably an error message.  It would be helpful to list that in
your problem report.

> I can open up and view the .csv file using an OpenOffice spreadsheet.
> I'm thinking maybe I have to copy one column at a time from the .csv
> file to the postgres database.
> How do I copy something like column A, B, C, etc. from the .csv file to
> TEST_DB client_1_lastname | client_1_firstname | client_1_address | etc.
> etc.

If the file is comma-separated with quoted values, you need to get rid
of the quotes and change the commas to tabs (in case there are commas in
the data).  You can do this in OpenOffice by setting the parameters
correctly when you save a csv file.  If you wish, you can choose a
different delimiter, so long as it does not appear in the data.  The
fields in the csv file must appear in the same order as the columns are
listed in the database by "\d matters", without omitting any columns, or
else you must specify a field list.  The filename must be in single
quotes.

  COPY matters [(field1, field2,...)] FROM '/home/floog/TEST_DB.csv'
[DELIMITER 'x'];

(You need the DELIMITER clause if you don't use tab as a delimiter.)

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "Love is patient, love is kind. It does not envy, it
      does not boast, it is not proud. It is not rude, it is
      not self seeking, it is not easily angered, it keeps
      no record of wrongs. Love does not delight in evil but
      rejoices with the truth. It always protects, always
      trusts, always hopes, always perseveres."
                                I Corinthians 13:4-7


pgsql-novice by date:

Previous
From: Rafael Charnovscki
Date:
Subject: Re: inserting multiple rows
Next
From: michael@floog.net
Date:
Subject: Re: help with COPY from .csv file into database