ACC2000: How to Export Null Fields to Delimited-Text Format Files

This article describes two methods that you can use to export Null fields with delimiters.

Null values do not have text qualifiers around the fields in a standard, delimited-text export of a table or query in Microsoft Access 2000. An example of a line from an export of this type would be: The two commas together show how a Null value is exported if a comma delimiter is chosen.

You can use one of two methods to place text qualifiers around Null values in a text export from a Microsoft Access table or query. Use Method 1 if you do not require field names to be exported as the first record of the text file. Use Method 2 if you do require field names as the first record.

Method 1: Field Names Not Required in First Row

1. Start Microsoft Access and open any database.
2. In the Database window, click Tables, and then click New to create the following table:
   Table: tblTest
   
   FieldName: A
   DataType: Text
   
   FieldName: B
   DataType: Text
   
   FieldName: C
   DataType: Text
   Save the table as tblTest.
3. Open the tblTest table and add the following records to the table:

      Record   Field A    Field B    Field C
      --------------------------------------
        1      data       data       data
        2      data                  data
        3      data                  data
        4      data       data       data
   					

4. Close the tblTest table.
5. In the Database window, click Queries, and then click New. In the New Query dialog box, click Design View, and then click OK.
6. In the Show Table dialog box, click tblTest, click Add, and then click Close.
7. Create the following fields in the query grid:
   Field: Field A: Chr(34) & [A] & Chr(34)
   
   Field: Field B: Chr(34) & [B] & Chr(34)
   
   Field: Field C: Chr(34) & [C] & Chr(34)
8. Save the query as qryExportTest.
9. On the File menu, click Export.
10. In the Export Query qryExportTest To dialog box, in the Save as type box, select Text Files. Click the Save button to start the Export Text Wizard.
11. In the Export Text Wizard dialog box, click Next.
12. On the next screen, set Text Qualifier to {none} and click Finish.
    
    The text export is completed, and a message box is displayed stating that the file was created successfully. Click the OK button.
13. Open the text file in NotePad. Note that all fields have quotation mark text qualifiers, including the Null fields.

Method 2: Field Names Required in First Row

1. Start Microsoft Access and open any database.
2. In the Database window, click Tables, and then click New to create the following table:
   Table: tblTest
   
   FieldName: A
   DataType: Text
   
   FieldName: B
   DataType: Text
   Default Value: =Chr$(32)
   
   FieldName: C
   DataType: Text
   NOTE: The default value is set to a single blank space (Chr$(32)) in any field in which there might be a Null value. The field appears blank, but it is, in fact, not null.
   Save the table as tblTest1.
3. Open the tblTest1 table and add the following records to the table:

      Record   Field A    Field B    Field C
      --------------------------------------
        1      data       data       data
        2      data                  data
        3      data                  data
        4      data       data       data
   					

4. On the File menu, click Export.
5. In the Export Query qryExportTest As dialog box, in the Save as type box, select Text Files. Click the Save button to start the Export Text Wizard.
6. In the Export Text Wizard dialog box, click Next.
7. On the next screen, click to select Include Field Names on First Row, and then click Finish. The text export is completed, and a message box is displayed stating that the file was created successfully. Click OK.
8. Open the text file in NotePad. Note that all the fields have quotation marks at the beginning and the end.
9. On the Edit menu, click Replace. In the Replace dialog box, in the Find what box, type " "; in the Replace with box, type "", and then click Replace All. Note that NotePad replaces any instance of " " with "". Close the Replace dialog box. This step removes any default blank spaces which were used to force quotation marks to be inserted for Null text fields.
10. Save the text file and quit NotePad. All Null fields now contain "" text qualifiers.

For more information about exporting text files, click Microsoft Access Help on the Help menu, type export data or database objects to another database or file format in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.