ACC2000: How to Export Numeric Data with Quotation Marks and Commas (208497)

SUMMARY

In some cases, you may want to export data to a quotation-mark and comma-delimited file, and you may want each value to be surrounded by quotation marks. By design, Microsoft Access will only place quotation marks around text values and not numeric values. This article describes a method that you can use to export data that has all values surrounded by quotation marks.

NOTE: The "More Information" section has two sections. Use the first section, "Creating and Exporting a Query Using Custom Specification," if you are working with a database file. Use the second section, "Creating and Exporting a View," if you are working with a Microsoft Access project.

MORE INFORMATION

To create a comma-delimited file that has quotation marks around each value, create a new query and export the results of this query. The following steps describe the process.

Creating and Exporting a Query Using Custom Specification

NOTE: This method applies only to a Microsoft Access database (.mdb).

In the Database window, click Queries under Objects, and then click New. Click Design View, and then click OK.
Add the table or query that you want to export.
Instead of adding the field names to the query grid, use expressions. These expressions will concatenate quotation marks (ASCII character number 34) at the beginning and end of each value in a field. Type these expressions in the Field row of each column in the query grid. Use the following expressions as guidelines:
FName: Chr(34) & [First Name] & Chr(34)

-or-

New Cost: Chr(34) & [Cost] & Chr(34)
NOTE: The expression names (such as FName) must be different from the actual field names (such as First Name).
Save the query as qryExport, and then close it.
In the Database window, click Queries under Objects and select the qryExport query.
On the File menu, click Export.
In the Export Query 'qryExport' To dialog box, click Text Files in the Save as type list. In the File name box, type the name that you want to use. Click Save.
In the Export Text Wizard dialog box, click the Delimited option. Click Next.
In the next dialog box, make the following selections:
Delimiter that separates your fields: Comma
Text Qualifier: {none}
If you want to export the field names, click to select the Include Field Names on First Row check box.
Click Next. Verify the path and file name of your text file. Click Finish to complete the export.

Creating and Exporting a View

NOTE: This method applies only to a Microsoft Access project (.adp).

When you export a view, it automatically adds the characters "-" and "|" to build a table around the results.

In the Database window, click Views under Objects, and then click New.
On the View menu, click Show Table.
Drag the table or view that you want to export onto the Diagram pane.
Instead of just adding the field names to the grid, use expressions and an alias. These expressions will concatenate quotation marks (ASCII character number 34) at the beginning and end of each value in a field. Type these expressions in the Column field of the grid. Use the following expressions as guidelines:
Column: Char(34) + [First Name] + Char(34)
Alias: FName

-or-

Column: Char(34) + [Cost] + Char(34)
Alias: New Cost
NOTE: The alias names (such as FName) must be different from the actual field names (such as First Name).
Save the view as ExportQuoteView, and then close it.
With ExportQuoteView selected, on the File menu, click Export.
In the Export View 'ExportQuoteView' To dialog box, click Text Files in the Save as type list. In the File name box, type the name that you want to use. Click Save.