12

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:

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?

SteveC
  • 15,808
  • 23
  • 102
  • 173
Tim Fountain
  • 33,093
  • 5
  • 41
  • 69

1 Answers1

13

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
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • How to make NULL output to ""? – PerlDev Nov 15 '10 at 15:20
  • 2
    In a batch file, worked like a charm like this (with delayed expansion enabled, and double quoted to escape the " char): SELECT QUOTENAME(FieldName, '""'), .... – djangofan Nov 15 '11 at 17:40
  • To convert NULL using portable SQL, use COALESCE(field1, ''). COALESCE can accept multiple possibly null parameters, it will return the first that is not null. – Jimbo Apr 03 '13 at 14:09
  • 4
    Beware that the `QUOTENAME` is limited to 128 characters. Inputs greater than 128 characters return NULL, making it useless for longer text. – JustinStolle May 24 '13 at 20:39
  • Any solution for QUOTENAME for columns having more tha 128 characters? – PC. Sep 20 '16 at 10:11
  • 1
    This helped me since my fields are all only 100 characters long. But I would also be interested in a way to do it with fields over 128 characters...maybe concatenate several small substrings, all using QuoteName, into one long string? Anyway, I'm grateful for this answer because it helped me a lot, but s @djangofan pointed out, in the CMD/SQLCMD line I had to use 2 double-quotes for an escape mechanism. – PBJ Mar 21 '19 at 14:44