Export SQL query result to CSV

Question

I'm using sqlcmd to export a query result with two columns to csv. The simple query is:

SELECT DISTINCT
    CustomerGuid, CustomerPassword
FROM
    ServiceOrder
ORDER BY
    CustomerGuid

When I open the exported csv in Excel both customer and password are on the same column. Is it possible to split them into their own column using sqlcmd. My sqlcmd looks like

SQLCMD -S . -d BAS -Q "SQL STATEMENT" -s "," -o "c:\data.csv"

Thanks.

Try `SQLCMD -S . -d BAS -Q "SQL STATEMENT" -s ";" -o "c:\data.csv"` It could be a problem with the line delimiter in Excel. — andy, Nov 08 '13 at 11:38
Andy, thank you. The problem was me using "," instead of ";". — Morten Laustsen, Nov 08 '13 at 11:49
Awesome, I've added this as an answer :) If you could accept it, that'd be a great help. — andy, Nov 08 '13 at 11:51
Actually, there is nothing wrong with `,`. Excel's behaviour is locale-dependent, with `,` or `;` used depending on the Regional Settings of the user. — Panagiotis Kanavos, Nov 08 '13 at 11:56

score 2 · Accepted Answer · answered Nov 08 '13 at 11:51

2

The problem is that you are using , instead of ; as the line delimiter. Try:

SQLCMD -S . -d BAS -Q "SQL STATEMENT" -s ";" -o "c:\data.csv"

:)

answered Nov 08 '13 at 11:51

andy

2,369
2
31
50

score 1 · Answer 2 · edited Mar 20 '17 at 10:18

Actually, this is more of an Excel question and it's already answered in superuser. The default separator when you open a CSV file is locale dependent. In locales where , is a decimal separator, the default is ;.

You can either modify the List separator in Regional settings (not recommended) or open an empty worksheet and import the data, specifying the separator you want.

By the way, the same rules are used in Excel and SharePoint Formulas, where you may have to type ; instead of , to separate values depending on your locale.

score 0 · Answer 3 · answered Nov 08 '13 at 11:41

You might have one of the following issues:

Either the CSV output does not use any separator between its columns (you can check this by opening the CSV file in a regular text editor; or the column separator set in Excel for CSV files does not match the separator you actually have in your output file.

Export SQL query result to CSV

3 Answers3