Microsoft KB Archive/148394

= ACC: How to Export Numeric Data with Quotation Marks and Commas =

Article ID: 148394

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q148394



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



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.



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
 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.) 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 above (such as FName) must be different from the actual field names (such as First Name).  Save the query as ExportQuery, and then close it.

Exporting Query Using Custom Export Specification
In Microsoft Access 97 and 7.0:

 In the Database window, click the Query tab and select the ExportQuery query.</li> On the File menu, click Save As/Export.</li> In the Save As dialog box, click "To an external File or Database." Click OK.</li> 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.</li> In the Export Text Wizard dialog box, click the Delimited option. Click Next.</li>  In the next dialog box, make the following selections:

<pre class="fixed_text">     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. </li> Click Next. Verify the path and file name of your text file. Click Finish to complete the export.</li></ol>

In Microsoft Access version 2.0:

 In the Database window, click Export on the File menu.</li> In the Export dialog box, select Text(Delimited), and then click OK.</li> In the Select Microsoft Access Object dialog box, under View, click Queries, and then click the ExportQuery query in the Objects list. Click OK.</li> In the Export To File dialog box, under File Name, type the name you want to use, and then click OK.</li>  In the Export Text Options dialog box, click Options. Make sure the following settings are selected:

<pre class="fixed_text">     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. </li> Click OK to complete the export.</li></ol>

<div class="references_section">