2

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

Improve this question

1 Answer 1

Reset to default
1

(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');
Improve this answer
8
  • You're right, WITH (FORMAT CSV) can totally handle double quotes. I found out that my issue was actually double quotes and commas in the text of my table itself. Hence my need to use tab as delimiter. I think at this point I am guessing windows tab is not the same character as linux tab. Still checking!! (Not sure if I should update my question since you have quoted me!)
    – Dina
    Commented Nov 28, 2017 at 18:45
  • The tab is is dependent on the encoding of the system, but it's the same being part of the ascii characters.
    – Evan Carroll
    Commented Nov 28, 2017 at 18:51
  • When you say "text of my table itself" what are you referring to? Try to create a test-case for it.
    – Evan Carroll
    Commented Nov 28, 2017 at 18:52
  • I mean it is in the data I am trying to export, so putting double quotes around it won't work. Have you heard anything about windows tab being 4 spaces and linux tab being 8 spaces? Or is this crazy talk in the depths of googling... I will try a test file next.
    – Dina
    Commented Nov 28, 2017 at 19:02
  • Tabs are just a character. That character is usually displayed as (\t, or two, four or eight spaces). With vim, the editor I use, you can set it yourself set ts=4, set ts=8, but you're only changing the rendering of that character.
    – Evan Carroll
    Commented Nov 28, 2017 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.