Handling NULL Character x00 when Exporting to File Using BCP

Handling NULL Character x00 when Exporting to File Using BCP

Handling NULL Character x00 when Exporting to File Using BCP

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.

Cloud Computing is the Future – Learn Azure SQL Database Today!

Check my online course on Udemy titled “Introduction to Azure SQL Database” (discount included in link).

Learn all about Azure SQL Database and Azure SQL Server on Virtual Machines. Get started in no time via a comprehensive set of lectures and live demos.

Introduction to Azure SQL Database (Online Course - Lifetime Access)

Online Course: Introduction to Azure SQL Database

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

Check all our other related SQL Server Development articles.

Check out our latest software releases!

Subscribe to our newsletter and stay up to date!

Check out Artemakis’s eBooks!

 

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

Loading...

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

© SQLNetHub

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.