Wednesday, November 30, 2016

Adding double quotes to string value when importing data from SQL Server to CSV

There have been many discussions on this and but still the issue exist if you try to get it done using the wizard itself. Generally, we need string values to be encased when importing data to CSV but not all columns. SQL Server Import and Export Wizard allows us to add a Text Qualifier but it adds the qualifier to all column regardless of the data type. As per some of the threads I read, this is because CSV file has no data type set for columns, hence it cannot distinguish types.

With the wizard, this is how you set the Text Qualifier.

And once it is generated from the following query against AdventureWorks database;

 ProductID, Name, Color, ListPrice 
FROM Production.Product;

The output will be;

However, in most cased, all we need is, encase only string values, we need to handle it via the query. If you write the query as below and continue the wizard without a qualifer;

 ProductID, '"'+ Name + '"' Name, '"' + Color + '"' Color, ListPrice 
FROM Production.Product;

Then you get the following output;

Check and see, let's discuss if you experience issues.

No comments: