Search code examples
sqlcsvsql-server-2005sqlcmdsql-view

SQL View export to CSV by SQLCMD - Excel format numbers from varchar fields to exponent syntax

I export data from a sql view (SQL Server 2005) with a sql job to a csv-file:

sqlcmd -W -s ";"  -E  -d master -Q "SET NOCOUNT ON; SELECT *  FROM [DB].[NameOfView];" -o "\\Location\File.csv"

In the sql view is a field (Type Varchar(20)) with numbers which are often longer than 15 characters. For this field I just usa a simple statement:

SELECT Fieldname AS Name FROM DB

...nothing special.

When I open the csv file excel formats the field with the long number to a format with exponent syntax:

enter image description here

Is there a way to edit something in the query or the sqlcmd job to say excel not to do that? I'm thinking about define the result as text and not as number in sql or something like this (but it is already a varchar field and i also tried things like ''''+ Fieldname and s.o. ... -.-)

I'm not able to change something in excel because a lot of people should have access to the csv - otherwise all of them have to make settings in excel.

They should open the csv and everything looks fine (:

Thanks for your help/ideas!

Solution

  • does

    SELECT '"' + Fieldname + '"' AS Name FROM DB

    not work? That should cause double quotes to appear in the .csv and Excel to treat it as text.

    Edit: try

    SELECT '"=""' + Fieldname + '"""' AS Name FROM DB

    (The above answer assumes that double-quotes don't need further escaping in the SQL string, given that it uses single-quotes for text literals, so if no joy just check this. You are aiming for it to look like

    "=""0.00000014567897354354"""

    in the CSV)

    See also https://superuser.com/questions/318420/formatting-a-comma-delimited-csv-to-force-excel-to-interpret-value-as-a-string