Re: help with COPY from .csv file into database

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(at)lfix(dot)co(dot)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