Microsoft Access Export Truncates Numbers

Introduction

I wanted to export all tables from an Access database into a folder of CSV formatted text files. For most data types, such as Text or Integer, all data was exported without any loss. However, floating point numbers (Float or Double) were exported with only two decimal places.

Approach

You can export a Microsoft Access table to a text file using either the menu item File / Export or this Access SQL statement from KB208408 …

SELECT * INTO [Text;FMT=Delimited;HDR=Yes;Database=<Path>].[<Table>.csv] FROM <Table>

Solutions

Here's three solutions with varying advantages:

• KB153364 article suggests formatting the number column to define the required decimal places. This solution requires writing a SQL statement for each table you're exporting.
• You can add a schema.ini into the destination export folder and specify the number of decimal places for all floating point numbers in a table using the NumberDigits keyword. This solution requires creating or copying a schema.ini file into the export directory before exporting the tables.
• You can change the No. of digits after decimal in your Regional and Language Options of your computer. This solution means you or your user has to remember to the change that setting on their computer before exporting the tables.

About Schema.ini File

A slight digression: schema.ini is a file used by the Text Driver to determine the characteristics of tables and columns of a database. You can find the syntax and properties of the schema.ini file in Schema.ini File (Text File Driver).

When exporting tables, Access creates a schema.ini file in the export folder if that file does not exist. If there is an existing schema.ini file and the section for the output CSV file does not exist, Access will add a new section for the output CSV file. Otherwise, Access does not change the schema.ini file.

Conclusion

None of the solutions are very satisfactory for writing a general program to export tables from an Access database into a folder of text files. The simplest solution is to define the required number of decimal places in the database connection string (the part between the square brackets in the SQL statement). However, I couldn't find any formal specification of this string on the Web.