BCP Error - copy direction must be in, out or format

Padmanabhan, Venkatesh 241

Hi.

I am trying to use the BCP in command line. I am trying for the below code:

bcp "select ('"' + replace(ltrim(rtrim(diary)), '""', '""""') + '"') as diry from test.txt " queryout "path" -t -s servername -d dbname -b 500 -c -c 65001 -t~

I am getting error as - copy direction must be in, out or format

How to fix this

Padmanabhan, Venkatesh 241 Reputation points

2021-03-29T11:52:55.537+00:00

This line is causing the error in BCP:

('"' + replace(ltrim(rtrim([columnName])), '""', '""""') + '"')

Accepted answer

Erland Sommarskog 100.9K Reputation points MVP

2021-03-31T21:37:39.62+00:00
This is starting to get pretty ugly. We need replace CR+LF with nothing (or would you prefer spaces?). But try this:

bcp "select char(34) + replace(replace(replace(ltrim(rtrim(diary)), replicate(char(34), 2), replicate(char(34), 4)), char(13), ''), char(10), '') + char(34) as diry from test.txt" queryout "path" -T -S servername -d dbname -b 500 -c -C 65001 -t~

And because of the command-line syntax, it has to be a single line.
Please sign in to rate this answer.
Viorel 112.1K Reputation points

2021-04-01T07:51:42.123+00:00

What about replacing single char(34) characters too?

Erland Sommarskog 100.9K Reputation points MVP

2021-04-01T21:11:54.87+00:00

When you say it, it makes sense, but Padmanabahn might know something we don't know.
Sign in to comment

2 additional answers

Viorel 112.1K Reputation points

2021-03-29T12:48:38.647+00:00

Try this command:

bcp "select ('""' + replace(ltrim(rtrim(diary)), '""', '""""') + '""') as...
Please sign in to rate this answer.
Padmanabhan, Venkatesh 241 Reputation points

2021-03-29T13:03:27.477+00:00

The result comes with 2 double quotes at the start and end
"" tHIS IS A output ""

I need the output as " tHIS IS A output "

Viorel 112.1K Reputation points

2021-03-29T13:24:56+00:00

Show your last command and make sure that the diary source values do not contain already these quotation marks.
Sign in to comment
Erland Sommarskog 100.9K Reputation points MVP

2021-03-29T22:05:25.837+00:00
Try this:

bcp "select (char(34) + replace(ltrim(rtrim(diary)), replicate(char(34), 2), replicate(char(34), 4) + char(34)) as diry from test.txt " queryout "path" -T -S servername -d dbname -b 500 -c -C 65001 -t~

I have also corrected three switches that incorrectly were lowercase. BCP comes from the Unix world, and the switches are case-sensitive.
Please sign in to rate this answer.
Padmanabhan, Venkatesh 241 Reputation points

2021-03-30T12:52:38.127+00:00

Hi .
Same error.
I did this :
BCP "SELECT (char(34) + replace(ltrim(rtrim([DIARY])), replicate(char(34), 2), replicate(char(34), 4) + char(34))) FROM TABLENAME " queryout "filename" -T -S servername -d table -b 500 -c -C 65001 -t~

The starting text is having double quotes, but the ending text is missing the double quotes. Also, if there are line feeds how to remove that.

I am seeing the repeating pattern as "one space and 2 line feeds" in the data which i receive
Thanks

Erland Sommarskog 100.9K Reputation points MVP

2021-03-30T21:15:14.807+00:00

I copied and pasted your command into a command-line window, and I got this error message (abbreviated):

SQLState = 08001, NativeError = 53 Error = [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. SQLState = 08001, NativeError = 53 Error = [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. ... SQLState = S1T00, NativeError = 0 Error = [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired

Which is perfectly to be expected since I don't have a machine with that name.

You said that you got the "same error", but you never posted which error you got - but it seemed to be an error with the command syntax.

Please post the exact message you get. Preferably with a screenshot.

Padmanabhan, Venkatesh 241 Reputation points

2021-03-31T01:49:19.67+00:00

Apologies for the delay and the wrong response. Thanks for the reply.

The data is getting passed from table to file successfully , but the ending text is missing the double quotes. Also, if there are line feeds how to remove that.

I am seeing the repeating pattern as "one space and 2 line feeds" in the data which i receive, which I want to remove as well.

Now the output I get is : "This is a text

Output required is : "This is a text"

Viorel 112.1K Reputation points

2021-03-31T14:10:18.55+00:00

Probably you will find the missing “ on next line, if your ‘This is a text’ ends with a new-line character. If this is an output CSV file, then removing or replacing new-lines is not always necessary, I think. It seems that this is a new aspect which is not covered by original question.
Sign in to comment