Exporting CSV data using SQLCMD.EXE

Question

I'm trying to export data from SQL Server into CSV format. I have a bat task to do this that's run at regular intervals. Command is:

C:\Program Files\Microsoft SQL Server\[...]\SQLCMD.EXE" -d [db details] -i c:\export.sql -o c:\export.csv -s"," -W 

The SQL file is just a SELECT * from a view.

This works except that some of the rows have commas in the data, so the values need to be quoted. I could change the column separator to be "','", but then I'd need SQL Server to escape single quotes in the data as well.

Unfortunately changing the separator to another character is unlikely to solve the problem in 100% of cases as one of the fields contains serialized data from another application which contains all sorts of weird and wonderful characters.

Is there any way for me to get standard, quoted CSV data?

Answer 1

You should be able to modify your SELECT statement to use the QUOTENAME function. You would, of course, have to list all the columns individually rather than using SELECT *.

Note: It may be hard to read on the screen, but the second parameter for QUOTENAME is:

{single quote} {double quote} {single quote}

SELECT QUOTENAME(Column1, '"'), QUOTENAME(Column2, '"')
    FROM YourView