BCP and treatment of NULLs when export to fixed format text file

[ezelasky]

We are using the bcp utility (via APIs) to export data from a SQL table
in a fixed format text file. BCP is inserting spaces for a field if
the field contains a NULL. This is fine with us except at the end of
the line, there are no spaces for that field just the end-of-row
terminator prematurely, so it looks like that field is not present and
messes up another piece of software we pump the text file into down
stream.

Example -- The last row illustrates the problem.

123-49-890 Mary Smith Raleigh NC \r\n
999-88-123 Henry Ax Boston MA \r\n
456-99-123 Sue Kite WA \r\n
789-88-126 Andy Yates Philadelphia \r\n

We have thought about using a SQL query to convert the NULL data
explicitly to spaces, but were wondering is there a switch or something
in our format file to get around this.

Thanks.

[Gregory Dean]

On 2/8/05 2:40 PM, in article
11************* *********@c13g2 00...legr oups.com,
"ez******@hotma il.com" <ez******@hotma il.com> wrote:

We are using the bcp utility (via APIs) to export data from a SQL table
in a fixed format text file. BCP is inserting spaces for a field if
the field contains a NULL. This is fine with us except at the end of
the line, there are no spaces for that field just the end-of-row
terminator prematurely, so it looks like that field is not present and
messes up another piece of software we pump the text file into down
stream.

Example -- The last row illustrates the problem.

123-49-890 Mary Smith Raleigh NC \r\n
999-88-123 Henry Ax Boston MA \r\n
456-99-123 Sue Kite WA \r\n
789-88-126 Andy Yates Philadelphia \r\n

We have thought about using a SQL query to convert the NULL data
explicitly to spaces, but were wondering is there a switch or something
in our format file to get around this.

Thanks.

You could also assume that the bcp utility is skipping the NULL field
altogether and the \r\n represents the end of the record. I say this because
what you should see if what you are describing is true would be 2 sets of
\r\n...

123-49-890 Mary Smith Raleigh NC \r\n
999-88-123 Henry Ax Boston MA \r\n
456-99-123 Sue Kite WA \r\n
789-88-126 Andy Yates Philadelphia \r\n\r\n

I recommend looking at the data in a hex editor and see if that is the case.

-Greg

[Erland Sommarskog]

(ez******@hotma il.com) writes:

We are using the bcp utility (via APIs) to export data from a SQL table
in a fixed format text file. BCP is inserting spaces for a field if
the field contains a NULL. This is fine with us except at the end of
the line, there are no spaces for that field just the end-of-row
terminator prematurely, so it looks like that field is not present and
messes up another piece of software we pump the text file into down
stream.

Example -- The last row illustrates the problem.

123-49-890 Mary Smith Raleigh NC \r\n
999-88-123 Henry Ax Boston MA \r\n
456-99-123 Sue Kite WA \r\n
789-88-126 Andy Yates Philadelphia \r\n

We have thought about using a SQL query to convert the NULL data
explicitly to spaces, but were wondering is there a switch or something
in our format file to get around this.

I did this:

CREATE TABLE nullatend (a int NOT NULL,
b varchar(23) NOT NULL,
c char(4) NULL,
d char(2) NULL)
go
INSERT nullatend(a, b, c,d) values (1, 'kjkjl', NULL, 'KA')
INSERT nullatend(a, b, c,d) values (2, 'kjkjl', 'LKL', NULL)
INSERT nullatend(a, b, c,d) values (3, 'kjkjl', 'NULL', 'KA')

And then:

bcp tempdb..nullate nd out slask.bcp -c -T

Looking at the out file in an editor that can show tabs, there appears to be
a final tab on line two, although that column has a NULL value.

Now, you only said "fixed format", but did not specify whether you are
using format files, delimiters etc. If it is not that simple that you
don't see the tab, it would help if you posted more details how you use
BCP. Best would be with a repro that populated a table with a few rows
and the issued a BCP command.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp