This article discusses the behavior of the BCP utility when extracting data from SQL Server tables to files and more specifically to CSV files.
Imagine that you have the below table:
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
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:
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
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.
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!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners: Windows Forms (C#)
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
- Updating SQL Server Tables Without Causing Blocking
- The set identity_insert Command in SQL Server
- The IDENTITY_CACHE Option in SQL Server
- The Import Flat File Wizard in SSMS v17.3 and later
- How to Suppress the “N Row(s) Affected” Output Message in SQL Server
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:
Reference: SQLNetHub (https://www.sqlnethub.com)
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).