sql server bulk copy out/postgres copy from infile

Question

I'm starting a conversion of a system from MS SQL Server to Postgres. I have the table structures converted, and I use "bcp" to get the data out of SQL Server.

ERROR:  invalid byte sequence for encoding "UTF8": 0x80
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT:  COPY cm_outgoing, line 200: "200  c:\temp\200.xml 2009-10-10 01:50:44.000 1900-01-01 00:00:00.000"

I've already used "sed" to get rid of the NUL (0x00) entries in the file, and I can't find any instances of 0x80 in the file that I'm trying to import.

Any thoughts? Is there an easier way?

Answer 1

Is your data really UTF8? Have you tried setting the client_encoding in your postgresql client to something like Latin1 ?

set client_encoding = 'latin1';
--do your COPY here

Answer 2

looks like the trouble was in the windows paths. I'm not sure how we got to 0x80, but doubling the backslashes worked.