SQL Server, .NET, Python and Other Programming Troubleshooting Articles

Handling NULL Character x00 when Exporting to File Using BCP

by

This article discusses the behavior of the BCP utility when extracting data from SQL Server tables to files and more specifically to CSV files.

Example: Description

Imagine that you have the below table:

Handling NULL Character x00 when Exporting to File Using BCP - Article on SQLNetHub

As you can see, records 2 and 3 contain null values.

Then you export the table contents to CSV using BPC:

bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:\tmptestExtract.csv" -c -C 1253 -t ; -S ".SQL2K14" -T

 

Example: Output 1

bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:tmptestExtract.csv" -c -C 1253 -t ; -S ".SQL2K14" -T

As you can see on the above screenshot, the output seems correct. The records’ NULL values have been replaced with empty strings.

Now consider that the original table, instead of NULL values has empty strings:

bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:tmptestExtract.csv" -c -C 1253 -t ; -S ".SQL2K14" -T

Let’s try again to export the new table contents to CSV using BPC:

bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:tmptestExtract.csv" -c -C 1253 -t ; -S ".SQL2K14" -T
 

Example: Output 2

Handling NULL Character x00 when Exporting to File Using BCP - Article on SQLNetHub

 

As you can see, the empty strings have been replaced by NULLs which correspond to the hexadecimal character x00.

Remarks and Conclusion

From the MSDN Library article on BCP, we read the following:

  • out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.
It seems from the above example, that the queryout keyword has the same behavior regarding null and empty strings.

Now, if you are going to feed the CSV file to another application for parsing and processing it, if the application does not explicitly handle possible occurrences of the NULL character then most probably an error will be occurred.

Learn more tips like this! Get the Online Course!

Check our online course on Udemy titled “How to Import and Export Data in SQL Server“
(special limited-time discount included in link).

Learn how to import and export data in SQL Server, via all the available built-in tools such as SSIS, BCP and more!

How to Import and Export Data in SQL Server - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Enroll from $12.99

To this end, always have in mind the above behavior when extracting data to files using BCP.

Featured Online Courses:

Read Also:

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub (https://www.sqlnethub.com)

© SQLNetHub



Views: 9,340