ACC: How to Export Numeric Data with Quotation Marks & Commas

• Microsoft Access version 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

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.

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 a New Query

1. In the Database window, click the Query tab and click New. In Microsoft Access 97 or 7.0, click Design view, and then click OK. (In Microsoft Access version 2.0, click New Query.)

2. Add the table or query that you want to export.

3. 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 above (such as FName) must be different from the actual field names (such as First Name).

4. Save the query as ExportQuery, and then close it.

Exporting Query Using Custom Export Specification

In Microsoft Access 97 and 7.0:

1. In the Database window, click the Query tab and select the ExportQuery query.

2. On the File menu, click Save As/Export.

3. In the Save As dialog box, click "To an external File or Database." Click OK.

4. In the Save Query dialog box, click Text Files in the Save As Type list. In the File name box, type the name you want to use. Click Export.

5. In the Export Text Wizard dialog box, click the Delimited option. Click Next.

6. 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.

7. Click Next. Verify the path and file name of your text file. Click Finish to complete the export.

1. In the Database window, click Export on the File menu.

2. In the Export dialog box, select Text(Delimited), and then click OK.

3. In the Select Microsoft Access Object dialog box, under View, click Queries, and then click the ExportQuery query in the Objects list. Click OK.

4. In the Export To File dialog box, under File Name, type the name you want to use, and then click OK.

5. In the Export Text Options dialog box, click Options. Make sure the following settings are selected:

         Under File Type: Windows (ANSI)
         Under Text Delimiter: {none}
         Under Field Separator: , (comma)
   

   If you want to export the field names, click to select the "Store Field Names in First Row" check box.

6. Click OK to complete the export.

REFERENCES

For more information about ASCII characters, search the Help Index for "ASCII," or ask the Microsoft Access 97 Office Assistant.