bcp, empty strings and null

Not sure if this is the right section...
I've got a problem with empty strings in a table turning into null (ascii
code 0x00) values when bcp'ed out to file:
When I bcp out of a table columns that have an empty string in them get
written to file as a null value (ascii code 0x00).
For example:
--SQL
use Sandpit_SL
create table bcpTest (Col1 varchar(10), Col2 varchar(10), Col3 varchar(10))
insert bcpTest select 'COLUMN1', '', 'COLUMN2'
And then bcp:
bcp Sandpit_SL.dbo.bcpTest out C:\bcpTest.csv -S DevDB1 -U sa -t "," -c
Produces an output file where the 2nd field contains ASCII (0x00) rather
than an empty string.
How can I make it so that the second field is an empty string? Am I missing
the point here?
Thanks
StephenSte (Ste@.discussions.microsoft.com) writes:
> I've got a problem with empty strings in a table turning into null (ascii
> code 0x00) values when bcp'ed out to file:
> When I bcp out of a table columns that have an empty string in them get
> written to file as a null value (ascii code 0x00).
> For example:
> --SQL
> use Sandpit_SL
> create table bcpTest (Col1 varchar(10), Col2 varchar(10), Col3
> varchar(10))
> insert bcpTest select 'COLUMN1', '', 'COLUMN2'
> And then bcp:
> bcp Sandpit_SL.dbo.bcpTest out C:\bcpTest.csv -S DevDB1 -U sa -t "," -c
> Produces an output file where the 2nd field contains ASCII (0x00) rather
> than an empty string.
I agree that behaviour is not what you would expect, so I submitted a
bug for it on
http://lab.msdn.microsoft.com/Produ...px?feedbackId=F
DBK50454
However, as I note in the bug report, I don't really expect a fix. I think
I can see the logic here.
If you import the file, the NUL character will become the empty string.
Now, consider the file:
COLUMN1,,COLUMN2
What do you get if you import this file? Answer: you get a NULL value in
Col2. Which neither that may be what you want.
The basic problem is that character format does not have a way to expess
NULL values. Sybase in their original design circumvented this, by
leaving out empty strings from the mix (That is, '' was the same as ' '.)
What is the consumer in the other end for this file?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> I agree that behaviour is not what you would expect, so I submitted a
> bug for it on
> [url]http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=F[/ur
l]
> DBK50454
> However, as I note in the bug report, I don't really expect a fix. I think
> I can see the logic here.
Indeed, the bug was quickly closed as "By design".
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Bcp

I need to import a CSV file (strings are in quotations) into SQL server using BCP. Some of the fields contain quotations whereas some of them do not. When i try to import of file such as this an error regarding truncation appears and I am not able to import the data.
When I try to import the same file without quotations around the fields it imports correctly. If you have help regarding this it would be greatly appreciated.

"XXX","20040326080100",1040,"121732","100203223","0040227324003"

or

XXX,20040326080100,1040,121732,100203223,004022732 4003

Thanks
DrahosCan they be tab delimited?

The quotes are a pain|||No, actually I receive those delimited files with quoted strings from somewhere.|||Let's see your bcp command?

Did you try and DTS it in?|||I am using this command:

bcp "Received_Data.dbo.XXX" in "Received_CSV.csv" -ffmt_file.fmt -T

Data I am loading is huge and I was recomended to use bcp instead of DTS.
I have tried DTS and it could load the data . There is a choise for 'Text qualifier' - you can choose 'double quoted', so it is OK.

format file is attached

Thanks again
Drahos|||format file is attached|||Is there anyone who can help me with that?|||There's a workaround; tell bcp not to import specific columns. In your case, turn the quotes into columns. The example works for me and on the first column only:

8.0
8
1 SQLCHAR 0 0 "\"" 0 dummy SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "\"," 1 Col001 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\"" 0 dummy SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 25 "," 2 Col002 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 25 "," 3 Col003 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 25 "," 4 Col004 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 25 "," 5 Col005 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 25 "\r\n" 6 Col006 SQL_Latin1_General_CP1_CI_AS|||Is there anyone who can help me with that?

either use PERL/PYTHON to srip the quotes or use DTS. DTS will not be much slower than BCP but DTS will br 100 times easier to use and maintain.