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

• Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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

MORE INFORMATION

Null values do not have text qualifiers around the fields in a standard, delimited-text export of a table or query in Microsoft Access 7.0 and 97. An example line from an export of this type would be:

    1,"abcde",,"fghij"

There are two methods of placing text qualifiers around Null values in a text export from a Microsoft Access 7.0 or 97 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 require field names as the first record.

Method 1

1. Create and save the following table:

   Table: tblTest ------------------ FieldName: A

              DataType: Text
          FieldName: B
              DataType: Text
          FieldName: C
              DataType: Text
   
   

2. Open the tblTest table and add several records to the table.

3. Enter text in field A. Enter text in field B for some of the records. Enter text in field C.

4. Close the tblTest table.

5. Create a new simple query based on the tblTest table.

6. Set the fields in the query grid as follows:

   Field: Field A: Chr(34) & [A] & Chr(34)

   Field: Field B: Chr(34) & [B] & Chr(34)

   Field: Field C: Chr(34) & [C] & Chr(34)

7. Save the query as qryExportTest.

8. Click Save As/Export on the File menu.

9. In the Save As dialog box, click to select "To an external File or Database," and then click OK.

10. In the Save In dialog box, in the Save As Type box, select Text Files and give the file a name. Click the Export 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 WordPad. Note that all fields have quotation mark text qualifiers, including the Null fields.

Method 2

1. Create and save the following table:

   Table: tblTest1 ---------------------------- FieldName: A

             DataType: Text
          FieldName: B
             DataType: Text
             Default Value: =Chr$(32)
          FieldName: C
             DataType: Text
   
   

2. Open the tblTest1 table and add several records to the table.

3. Enter text in field A. Enter text in field B for some of the records. Enter text in field C.

4. Close the tblTest1 table.

5. Click Save As/Export on the File menu.

6. In the Save As dialog box, select "To an external File or Database," and then click OK.

7. In the Save In dialog box, in the Save As Type box, select Text Files and give the file a name. Click Export to start the Export Text Wizard.

8. In the Export Text Wizard dialog box, click Next.

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

10. Open the text file in WordPad. Note that any Null values have " " (quotation marks) around them.

11. 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 WordPad replaces any instance of " " with "" and then displays a message stating that it is finished. Click OK, and then click Close.

12. Save the text file and quit WordPad. All Null fields now contain "" text qualifiers.

REFERENCES