In Access 2007 CSV Export: Disable Scientific Notation.

Question

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!

Answer 1

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.

Answer 2

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.