Postgresql COPY command

Hi,

I have a stock.csv file for my "stock" table, it comes from MS-SQL and must be transferred to Postgresql.

The command i run is:

COPY stock FROM 'mydir/Stock.csv';

BTW the table 'stock' schema (part of it) is:

ClientCode varchar(3) NOT NULL ,
Sku varchar(20) NOT NULL ,
AlternateSkuCode varchar(20) NULL ,
StyleCode varchar(4) NULL ,
BarcodeCode varchar(20) NULL ,
Description varchar(60) NOT NULL ,
DetailedDescription varchar(255) NULL ,
TechnicalDescription varchar(255) NULL ,

1)
My first Stock.csv file had headers as the column names on its first line.
e.g.



and I received this error:




So I thought - I better remove these column name headers.

2)
After removing the column names, the beginning of the first line of the Stock.csv file is now:

(part of it - the entire line is too long to paste)


and I once again received a similar error:



BTW I dont know if its a problem with "clientcode" or a problem with the detailedDescription field... because I'm looking at the CSV file that was sent to me and the detailed description had a line break (as I far as I can see using Wordpad) between 'toe' and 'and'.

This process is supposed to be automatic and, assuming the stock.csv file is uploaded to the correct location (FTP) intermittently, the Postgres database will also update its 'stock' table with a cron job.

Anyway the error mentions clientcode, but I dont understand it...there are only 3 letters..

If someone has any advice please let me know,
thanks very much!!!!

I was experimenting with different values and stuff and found that if I remove the double quotes from RJD

i.e.


etc.

the value RJD is going into the database column clientcode (which is varchar(3)).
So great!
BUT the stock.csv file is supplied to me with double quotes... so is there any way of telling the POSTGRESQL COPY command not to enter the double quotes into the database as well?

The other quoted fields were put into the database with the double quote symbols as well! Which isnt good

The command I ran to copy:



Does anyone know how to solve this double quote problem??
Much appreciated!

Unfortunately, PostgreSQL does not import CVS data with quotes. Also, I believe it will not import a CSV file if it has standard Windows line terminators of CR/LF (or "\r\n"). It will only accept data with Unix-style newline (\n), and delimited data cannot have quotes (for example, if there is a comma inside a delimited field, it must be escaped with a slash, as in \,). I'm not absolutely certain about the Windows line terminator, though.

The COPY command defaults to expecting a tab-delimited format, so you might want to just export as tab-delimited from MSSQL, specifying no quotes around fields (I can't remember if MSSQL automatically uses quotes). If PostgreSQL still won't accept the data, make sure that all line terminators or tabs inside the data is properly escaped with the \ slash, and possibly change the line endings to the Unix-style newline. (see COPY documentation).

Also see this newsgroup thread about this problem.