Skip to content

How to export comma delimited data to csv including commas in the data

January 21, 2014

I have been meaning to write this post for a long while so here is a very good short tip on how to do this.

If you wanted an automated script to export data from a database onto a CSV file, you can easily do this with the built in SQL Server Command – sqlcmd in the command prompt. I usually write the export as follows : –

SQLCMD -S DB-SERVER -E -Q "set nocount on; set ansi_warnings off; SELECT [Column1] As TextHere, [Column2] As NormalColumn FROM [Database].[dbo].[Table]" /o output.tmp /s "," -W

The second command converts the tmp file to csv

type output.tmp | findstr /V \-\,\- > output.csv

However, the problem lies in that if you have a comma (,) in any of the columns in the data, the sqlcmd will still take into account that comma as a delimiter and would split that data into two, for example if the address column had a column, it would split the address into two columns! Not exactly what we want. After reading StackOverflow on a few forum discussiosn, the solution was to convert the column containing the comma to varchar(MAX) – as a string with inverted comma quotes surrounding the outside. My thinking is to easily append those either side of the string and cast it as a nvarchar(MAX). The results were that it worked perfectly! Here is the solution: –

SQLCMD -S DB-SERVER -E -Q "set nocount on; set ansi_warnings off; SELECT '""' + cast ([Column1] as nvarchar(max)) + '""' As TextHere, [Column2] As NormalColumn FROM [Database].[dbo].[Table]" /o output.tmp /s "," -W

The key thing is that /s "," sets the column seperator to ,

 



Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: