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 Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:
> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>sql

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.
Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

BCP puts nulls in output text file

Hi,

I have been trying to output varchar fields from a table to a text file using BCP. When the field in the table is an empty string (where I have specifically set it to '') BCP places an ascii null in the output text file.

Is there a way I can tell it to just not place anything in the output for this field.

Other inportant information: I am trying to use comma as the field terminator (-t,). If I use the default tab terminator, then things seem to be ok.

Thanks for any help.
ScottWhat does your bcp command look like?

with -c it should be OK.

from
create table bcp (s varchar(10), t varchar(10) null, u varchar(10) not null, v varchar(10))
insert bcp select 'asd', 'asd', 'asd', 'asd'
insert bcp select 'asd', '', '', 'asd'
insert bcp select 'asd', 'asd', 'asd', 'asd'
exec master..xp_cmdshell 'bcp test..bcp out c:\bcpfile.txt -S(local) -T -t, -c'

I get
asd,asd,asd,asd
asd, , ,asd
asd,asd,asd,asd|||The command I used was very similar to yours and when I ran yours, I got the same results you did. However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

If I allow nulls in the table, then the output file contains a null (ASCII 0) in that spot between the commas.

Now, if I use DTS to output the file, then everything comes out as I want it to. I just wanted to avoid the use of DTS for something so simple.

Thanks for your help.
Scott|||However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

Actually the fields are NOT empty, they contained a zero length string. This is not the same as empty. The ASCII 0 or (, ,) is a zero length string, if you insert a NULL then your file will only contain (,,).sql

bcp out empty column

I'm bcp-ing a table to a file. I've noticed that if a varchar column is empty, that both "bcp -c" and "bcp -c -k" export the empty value as #$00. Instead, I'd like to export the value as if it were null.

use monkey
go

create table tab1 (
myColumn varchar(10) null
)
go

insert into tab1 (myColumn) values ('')
go

exec master..xp_cmdshell 'bcp monkey..tab1 out D:\data.csv -c -T'
go

drop table tab1
go

I've been fiddling about with other switches but so far haven't come up with a solution other than writing out the full SELECT-statement or updating the original. Anyone with an easier solution?try changing the extn of file to '.txt' and check it, its working fine for me using the command prompt as well as query analyser.

try as
exec master..xp_cmdshell 'bcp <db-name>.<table-owner>.tab1 out D:\data.csv -c -T'
or
exec master..xp_cmdshell 'bcp <db-name>.<table-owner>.tab1 out D:\data.txt -c -T'

cheers,
pavan.|||Changing the file's extension didn't do it for me, perhaps it's an OS or SQLServer configuration issue? It's a Windows 2000 Server running SQL2000. I'm not sure where to start.

I don't have the issue with the analyzer either, only when exporting to a file.|||Mine is windows 2000 professional runnung sqlserver 2000.
I dont think thats the problem.

Try the same in others machine,
i had a similar problem for exports and Imports of oracle,they never worked in my colleagues PC and worked fine from my pc..

bcp is inserting blank space for empty string

I'm doing a bcp out of a table to a file. Some of the fields in a
record may have an empty string.

When I bcp out to the file and examine it, the fields that have an
empty string in the database now show up in the file as having one
blank character.

Why is bcp doing this? I don't want the blank character in my output.

Thanks,
EricWhich version of MSSQL? What data type is the column? Is ANSI_PADDING
on or off for the column? If your column is char(1), then this would be
expected, as char columns are padded out with spaces; a varchar should
not be padded, though.

If this doesn't help, I suggest you post (simplified) CREATE TABLE and
INSERT statements to show exactly what your table and data look like.

Simon|||Simon Hayes wrote:
> Which version of MSSQL? What data type is the column? Is ANSI_PADDING
> on or off for the column? If your column is char(1), then this would
be
> expected, as char columns are padded out with spaces; a varchar
should
> not be padded, though.
> If this doesn't help, I suggest you post (simplified) CREATE TABLE
and
> INSERT statements to show exactly what your table and data look like.
> Simon

SQL Server 2000
Varchar 16
ANSI_PADDING is off.

Give it a shot. Create a table with three columns, all varchar. Insert
an empty string into each to get rid of any nulls. Then do a bcp out to
an output file and let me know if you get the same results.

Eric|||"epaetz" <epaetz41@.hotmail.com> wrote in message
news:1112704850.296614.123580@.o13g2000cwo.googlegr oups.com...
> Simon Hayes wrote:
>> Which version of MSSQL? What data type is the column? Is ANSI_PADDING
>> on or off for the column? If your column is char(1), then this would
> be
>> expected, as char columns are padded out with spaces; a varchar
> should
>> not be padded, though.
>>
>> If this doesn't help, I suggest you post (simplified) CREATE TABLE
> and
>> INSERT statements to show exactly what your table and data look like.
>>
>> Simon
> SQL Server 2000
> Varchar 16
> ANSI_PADDING is off.
> Give it a shot. Create a table with three columns, all varchar. Insert
> an empty string into each to get rid of any nulls. Then do a bcp out to
> an output file and let me know if you get the same results.
> Eric

I used this test script (on 8.00.760 Enterprise):

set ansi_padding off
go
create table eric (col1 varchar(16) null)
go
insert into eric select '' -- empty string
insert into eric select ' ' -- single space
go
select col1, len(col1) as 'Length', datalength(col1) as 'Datalength', col1 +
'X'
from eric
go

Then I exported the file with bcp:

bcp Development..eric out c:\temp\eric.txt -S kilkenny -c -T

When I checked eric.txt with a hex editor, it showed this:

00 0D 0A 20 0D 0A

So the empty string is an ASCII NUL character in this case, but the space is
ASCII 20. Is this the behaviour you see, or do you get something different?
Setting ANSI_PADDING ON didn't change the output (and BOL recommends it
should always be on anyway).

Simon|||I found a work around, using a Case structure in the sql query to
change empty string to a null. The BCP in turn outputs the resulting
null as an empty!

Thanks for your assistance.

Eric

BCP

Hi,
I am not able to export the result of sys.assemblies using BCP. The output
file is created but it is empty (the result of SELECT in SSMS is not empty).
BCP displays a message indicating that 0 rows were exported. It seems there
is no error!
Thanks,
LeilaLeila (Leilas@.hotpop.com) writes:
> I am not able to export the result of sys.assemblies using BCP. The
> output file is created but it is empty (the result of SELECT in SSMS is
> not empty). BCP displays a message indicating that 0 rows were exported.
> It seems there is no error!
Did you specify the database? I tried this on my machine:
bcp slasketti.sys.assemblies out e:\temp\slask.bcp -T -c -S .\NELJ
slasketti is my database, and .\NELJ is an instance that runs SQL 2005.
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|||Thanks Erland,
Yes I did :(
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97252351C54Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> Did you specify the database? I tried this on my machine:
> bcp slasketti.sys.assemblies out e:\temp\slask.bcp -T -c -S .\NELJ
> slasketti is my database, and .\NELJ is an instance that runs SQL 2005.
> --
> 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