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.

Answer 1

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"

:)

Answer 2

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.