Getting bcp.exe to escape terminators

views:

400

answers:

+1 Q:

Getting bcp.exe to escape terminators

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?

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 2009-12-29 19:23:10

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 2009-12-29 19:26:17

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 2009-12-29 19:30:20

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 2009-12-29 19:53:10

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 2009-12-29 20:35:34

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 2009-12-29 20:40:28

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 2009-12-29 20:45:09

Anyway, I've listed the common options whilst still using SQL Server (or Sybase) so it's up to you...

gbn 2009-12-29 20:49:59

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 2010-10-15 10:22:53

ansaurus

tags:

views:

answers:

Getting bcp.exe to escape terminators

related questions