Postgresql – Exporting/Importing data from SQL Server 2014 to PostgreSQL 9.5 with tab delimited files

bcpcopypostgresql

Not sure if anyone has run into this problem before and I am going a little bit crazy.

I am exporting a table from SQL Server 2014 using BCP to a tab delimited text file and then uploading the file to PostgreSQL 9.5 using COPY. 

exec master..xp_cmdshell 'bcp tempdb.dbo.##MyTable out "C:\BCPFiles\MyTableFile.txt" -c -t -T'

This works fine.

create temp table mytable (col1 int, col2 varchar(100), col3 varchar(100));
COPY mytable FROM '/tmp/MyTableFile.txt' WITH (FORMAT text);

ERROR:  invalid input syntax for integer: "1    text1      text2"

It looks to me like COPY doesn't recognize the tab, even though when I view the file it is tab delimited. There are no NULL values in this file btw.

CSV is not a good option for me because of the text has commas in it and from what I am reading PG doesn't like double quotes

Best Answer

(CSV is not a good option for me because of the text has commas in it and from what I am reading PG doesn't like double quotes)

Sure Pg accepts double quotes. See COPY

QUOTE Specifies the quoting character to be used when a data value is quoted. The default is double-quote. This must be a single one-byte character. This option is allowed only when using CSV format.

You don't even have to do anything..

COPY foo FROM 'asdf.csv' WITH (FORMAT CSV);

It looks to me like COPY doesn't recognize the tab, even though when I view the file it is tab delimited. There are no NULL values in this file btw.

If you want to use tabs, just set that up.

COPY foo FROM 'asdf.csv' WITH (FORMAT CSV, DELIMITER E'\t');
Related Question