11

I need to export some data using SQL Server 2000's BCP utility. Sometimes my data contains characters, such as \t and \n, that I need to use as column and row terminators. How do I get BCP to escape characters it's using as terminators as it outputs the data, so that I can actually import the data in another program?

For example, one of my columns is text data, and includes tabs and newlines. BCP just exports them as-is, and the program I'm trying to import them with gets confused because the data ends in the middle of a line and/or a line contains extra columns for no apparent reason.

This seems like a very, very, very basic function to include in a data exporter, but none of the command-line options seem to mention it. (Why it wouldn't just be the default is beyond me.) Am I missing something?

Kev
  • 15,899
  • 15
  • 79
  • 112

5 Answers5

10

Totally agree with you: escaping should be an option. "You can't have data with tabs or newlines" is the silliest thing I have ever heard.

Here is a possible solution:

  1. use the -r option to set a different line terminator. Something
    unlikely to be present in your data (#!#$#%#). I think you can use multiple
    characters, so that makes it easier.
  2. Open your data file in sed, a capable text editor, or write a script - and replace any \n and \t character with their escaped equivalents (\\n and \\t). Finally replace your line terminator with \n and you should be good.
  3. I think the same thing should apply to using -t for field terminators

Take a look at this article for more information.

MikeMurko
  • 2,214
  • 1
  • 27
  • 56
5

You can use a separator made up of multiple characters if you put them between double quotes:

bcp MY_TABLE out C:\MY_FILE.txt -S SERVER_IP -d DB_NAME -U MY_USER -P MY_PASSWORD -w -t "&#)^@" -r ">~+!"

Found the solution here.

Cosmin
  • 2,365
  • 2
  • 23
  • 29
0

You can use the native format, which will export in a binary database format that handles control characters. Just add the -n switch instead of -c or -w:

bcp MyTable out C:\temp\MyTable.tsv -S SERVER -d Database -U USER -n

Use native format to import or export data (SQL Server)

ChrisE
  • 376
  • 4
  • 14
  • Not sure that format will be able to be imported with any other SQL engine, but if you're exporting to import only with SQL Server itself, this could work. – Kev Apr 10 '23 at 07:45
-2

I have the same problem and searched a long time to find a solution. I found this one from a BCP master and it sounds reasonable. Perhaps you want to try it as well.

Possible solution: http://groups.google.co.uk/group/microsoft.public.sqlserver.tools/tree/browse_frm/thread/f1ee12cba3079189/ef9094123901fe26?rnum=1&q=lindawie+format+file&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.tools%2Fbrowse_frm%2Fthread%2Ff1ee12cba3079189%2Fef9094123901fe26%3Ftvc%3D1%26q%3Dlindawie%2Bformat%2Bfile%26#doc_fa5708ca51d967a6

Format file details & design: http://msdn.microsoft.com/en-us/library/aa173859%28SQL.80%29.aspx

Generally I can suggest these links to get you know about BCP problems and solutions: http://groups.google.co.uk/groups?q=lindawie+format+file

Best regards

Blama
  • 254
  • 3
  • 9
-9

You can't have data containing tabs and newlines with tabs and newline separators. It makes no sense. Escaping wouldn't help, because a tab is a tab. We're not talking c# string handling here.

What I'd do is use different terminators such as | and ||/n, or use a format file

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 2
    It actually does make sense--for instance, using COPY FROM in PostgreSQL, it will turn the sequence `\n` back into an actual newline in a text column. And unfortunately I can't specify a row delimiter in PostgreSQL. – Kev Dec 29 '09 at 19:26
  • Also, although different terminators is one workaround, it's taking a *really* long time to do the post-query replacements. It'd be much better if BCP could just escape text columns properly. – Kev Dec 29 '09 at 19:30
  • Neither osql nor sqlcmd support this either. What if you had a field ending in the escape character, for example? And if you read the PostgreSQL info, it mentions about dodgy escaping... SQL Server is utterly predictable, no? – gbn Dec 29 '09 at 19:53
  • 2
    Well, it would have to escape escape characters, as well. That's just how escape characters work. A text field containing a line break, backslash, and tab would come out as `\n\\\t` and turn back into a linebreak, backslash, and tab on import. Could you provide a reference for dodgy escaping? I didn't see anything at http://www.postgresql.org/docs/8.4/static/sql-copy.html . SQL Server is predictable, sure, but not the output I am (or pgsql is) looking for. – Kev Dec 29 '09 at 20:35
  • In your link: "...beware of adding backslashes unnecessarily, since that might accidentally produce a string matching the end-of-data marker ..." + 3 notes about CSV mode. Sybase, same as SQL Server: http://manuals.sybase.com/onlinebooks/group-as/asg1250e/util/@Generic__BookTextView/10086;pt=10086 – gbn Dec 29 '09 at 20:40
  • Key word being 'unnecessarily.' If you read the next paragraph: "It is strongly recommended that applications generating COPY data convert data newlines and carriage returns to the \n and \r sequences respectively." Which is exactly what I'm trying to get BCP to do. – Kev Dec 29 '09 at 20:45
  • Anyway, I've listed the common options whilst still using SQL Server (or Sybase) so it's up to you... – gbn Dec 29 '09 at 20:49