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.

Enroll to our course on Udemy “AI Essentials: A Beginner’s Guide to Artificial Intelligence” and dive deep into AI’s foundations, core concepts, and real-world applications. Discover the significance of data in AI, the role of algorithms in decision making, and the power of neural networks and deep learning.

AI Essentials: A Beginner's Guide to Artificial Intelligence
(Lifetime access, downloadable resources, quizzes, completion certificate)

Learn More

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: 10,942