The MS Access table or query can be exported to different file formats, one of which is a text file. The delimited text file which uses comma to separate the values is the comma-separated values (.csv) text file. This section will cover how to export MS access table or query to .csv file format.

The exported data must be reviewed carefully since it might not export accurately and precisely as the data from MS Access. Truncation of digits might happen depending on the computer system setup and the decimal precision of the data.

Export Access table or query as a .csv file

1. Highlight all records by clicking the top-leftmost square of the MS Access table (or query).

2. On the File Menu, select Export.

3. Choose the destination folder. The File name will have the .csv attachment and the Save as Type will be Text Files. Click Export All.

4. On the Export Text Wizard, choose Delimited and click Next.

Choose Comma as the Delimiter and {none} for Text Qualifier. Tick the box beside Include Field Names on First Row, then click Next.

Click Finish.

A successful export file will output the message below. Just click OK.

Review of the exported .csv file

The image below is the exported .csv file opened in MS Excel. It can be observed that all the data in this file has 2 decimal places, which is different from the original data in the MS Access table.

TRUNCATED DIGITS

Formatting the cells to have the same decimal place as the original data in MS Access will not provide the same value as the original data since the exported values are already truncated (not rounded off) to 2 decimal place. When truncated, the values are shortened by dropping the remaining digits after 2 places of the decimal point.

This truncation, or dropping-of-digit, behavior is controlled by the default system setting for the number of digits after decimal.

To illustrate, got to Start -> Control Panel -> Regional and Language Options -> Customize -> Numbers -> No. of digits after decimal -> 2

SOLUTION

The truncation problem can be eliminated by increasing the number of digits after the decimal. It is recommended that the value will be one digit larger than the decimal scale (number of digits after the decimal place) of the data.

• For this example, the number of digits after the decimal is set to 6. This is because the decimal scale of the data is 5. With the current setting, the MS Access table was exported again, thus the exported .csv data is not truncated.

Below is the newly exported .csv data opened in MS Excel. This now shows the same values as the MS Access data.