CODEWITHSUNDEEP
ms-accesscsvexportscientific-notation
Andrew Rollings
Andrew Rollings

Reputation: 14571

In Access 2007 CSV Export: Disable Scientific Notation

When exporting a CSV from Access 2007, it automatically converts decimals into scientific notation.

Unfortunately the tool that receives them treats these fields as text, and displays them as is.

The values being exported are from a query being run against some Excel linked tables, and they appear perfectly in the query view.

Is there any way to disable the automatic conversion to scientific notation.

I.e. if it appears as 0.007 in the query, it will appear as 0.007 in the output csv rather then 7E3?

Note: I'm constrained to use Excel and Access for this. As much as I'd like to switch to SQL Server, my wife would be unhappy if I put it on her work laptop!

Upvotes: 4

Views: 7814

Answers (3)

M_K
M_K

Reputation: 81

One easy way to handle this in a Query is to double-convert the value to long integer and then to string. For CSV-export it is character anyway.

myValue:ZString(ZLong(123456789))

Upvotes: 0

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

You have a couple of choices:

  • you can use the Format() function directly in your query to force the data in the offending columns to be formatted a certain way, for instance:

    SELECT ID, Format([Price],"standard") as Pricing FROM ORDERS;

  • you can write your own CSV export routine in VBA.
    I posted one recently as an answer to this question.

You can easily modify the code to format numeric types a certain way.
If you don't know how, let me know and I'll modify the code and post it here.

Upvotes: 5

HardCode
HardCode

Reputation: 6766

You could write a short amount of VBA code in access to query the data from the linked table or Access query and write it out to a text file, thus creating your own .CSV and foregoing the "Wizard". I never liked Access' export "wizard" much, and just created the files myself.

Upvotes: 1

Related Questions