Basic

Using the Wizard to Export to Text Files with Microsoft Access

In an effort to remain simple, the Access export to text file wizard manages to do the opposite, making simple updates to export specs unnecessarily cumbersome.

Mike Wolfe

Dec 5, 2023 • 8 min read

Recently, I wrote about importing text files into Microsoft Access using the built-in data import wizard:

Now, let's explore the opposite end of the process: exporting to text files.

Just like with importing, the Access export wizard provides a simple way to get your data into a delimited text format like CSV for use in other programs. In this article, I'll demonstrate the basic steps for exporting an Access table to a text file. We'll also look at saving the export steps for reuse via VBA code.

Sample Data

We will be using a Sale table (see screenshot below) with some specific field values that can pose problems when exporting to text files, including:

Optional fields (e.g., the ShipDate field)
Text fields with embedded commas (e.g., the CustomerName field)
Text fields with long strings of digits that are often interpreted as numbers (e.g., the ShippingTrackingNumber field)
Text fields with embedded double-quotes (e.g., the Notes field)

If you'd like to follow along, you can download the data for the Sale table and import it using the instructions here: Importing XML Step-by-Step.

Exporting the Data: Step by Step

Here are the detailed steps to export this data into a CSV file:

Open your Access database.
Navigate to 'Sale' table which contains the data you want to export.
Click the 'External Data' tab in the Access Ribbon.
In the 'Export' section, click 'Text File'.
In the 'Export - Text File' popup window, choose the location where you want to save the CSV file and type in a filename.
Ensure the '[_] Export data with formatting and layout' option is unchecked and click [OK].
Choose '(o) Delimited' then click [Next >].
Check box to '[√] Include Field Names on First Row' then continue with step 1 in the next section.

Saving the Export Specification

In the same window, at the bottom, click [Advanced...].
In the 'Export Specification' window that opens, click [Save As...].
Provide a name for the specification, such as "Sale Export Spec", then click [OK].
Click [OK] again to close the 'Export Specification' window.
Back in the 'Export Text Wizard' window, click [Finish].
Click [Close] to close the 'Export - Text File' wizard.

Executing the Export via VBA

You can automate the export process using VBA. The following code snippet exports data from the Sale table using the "Sale Export Spec" specification and saves it as a CSV file:

DoCmd.TransferText acExportDelim, "Sale Export Spec", "Sale", "C:\SalesData.csv"

Here's what the exported file contents look like:

"SaleID","OrderNumber","OrderDate","ShipDate","CustomerName","ShippingTrackingNumber","Notes"
1,"ORD000001",1/1/2023 0:00:00,1/3/2023 0:00:00,"John Smith","0012345678901234567890","Please expedite per customer request. They said this order is ""critical."""
2,"ORD000002",1/5/2023 0:00:00,1/9/2023 0:00:00,"Jane Doe","0002345678901234567890","Shipping to alternate address. Note customer ""prefers"" east entrance."
3,"ORD000003",1/7/2023 0:00:00,1/10/2023 0:00:00,"Bob Johnson","0000345678901234567890","Handle with care. Customer said last shipment arrived ""damaged."""
4,"ORD000004",1/9/2023 0:00:00,1/12/2023 0:00:00,"Sam Roberts","0000045678901234567890",
5,"ORD000005",1/15/2023 0:00:00,1/18/2023 0:00:00,"Sue Taylor","0000005678901234567890","Include extra pads. Customer complained last ones ""tore easily."""
6,"ORD000006",1/17/2023 0:00:00,,"Michael Smith, Sr.","0000000678901234567890",
7,"ORD000007",1/20/2023 0:00:00,1/24/2023 0:00:00,"Michael Smith, Jr.","0000000078901234567890","Must arrive by 1/28. Customer said ""no excuses"" this time."

Updating the Export Specification

In case your data requires special formatting, you can modify the export specification by following the steps below.

Begin the export process as previously outlined: Open your Access database, navigate to the 'Sale' table, click the 'External Data' tab in the Access Ribbon, and click 'Text File' in the 'Export' section.
In the 'Export - Text File' popup window, type in a location and name for your file (you have to do this even if all you want to do is modify the spec).
In the 'Export Text Wizard' dialog, click [Advanced...].
Click the [Specs...] button.
In the 'Import/Export Specifications' window, select your existing specification "Sale Export Spec" from the specifications list, then click [Open].
Make necessary changes to settings like field delimiter, date formats, etc., in the specification settings window. For this example, make the following changes:
- Set 'Field Delimiter' to: | (i.e., the "pipe" character)
- Set 'Text Qualifier' to: {none}
Click [Save As...]. The existing spec name of "Sale Export Spec" should prefill. Click [OK] then [Yes] to overwrite the existing saved specification.
Click [OK] again to close the export specification window.
Click [Cancel] in the "Export Text Wizard" dialog to exit without exporting any data.

Rerun your VBA code from above to test the updated specification:

DoCmd.TransferText acExportDelim, "Sale Export Spec", "Sale", "C:\SalesData.csv"

And here are the updated output results:

SaleID|OrderNumber|OrderDate|ShipDate|CustomerName|ShippingTrackingNumber|Notes
1|ORD000001|1/1/2023 0:00:00|1/3/2023 0:00:00|John Smith|0012345678901234567890|Please expedite per customer request. They said this order is "critical."
2|ORD000002|1/5/2023 0:00:00|1/9/2023 0:00:00|Jane Doe|0002345678901234567890|Shipping to alternate address. Note customer "prefers" east entrance.
3|ORD000003|1/7/2023 0:00:00|1/10/2023 0:00:00|Bob Johnson|0000345678901234567890|Handle with care. Customer said last shipment arrived "damaged."
4|ORD000004|1/9/2023 0:00:00|1/12/2023 0:00:00|Sam Roberts|0000045678901234567890|
5|ORD000005|1/15/2023 0:00:00|1/18/2023 0:00:00|Sue Taylor|0000005678901234567890|Include extra pads. Customer complained last ones "tore easily."
6|ORD000006|1/17/2023 0:00:00||Michael Smith, Sr.|0000000678901234567890|
7|ORD000007|1/20/2023 0:00:00|1/24/2023 0:00:00|Michael Smith, Jr.|0000000078901234567890|Must arrive by 1/28. Customer said "no excuses" this time.

Updating the Export Specification...As an Import Specification

If you look closely at the exported file contents above, you'll notice that the two date columns have 0:00:00 appended to every date value.

That's because all dates in Access include a time component.

One way to "remove" the time component during an export to text (at least when the time is always midnight) is to treat the column as a text field instead of a date field (for purposes of the export). Fascinatingly, though, it's not possible to change the Data Type of a field within the Export Specification dialog, even though changing the type can affect your export operations.

The workaround? Fool Access into thinking you are editing an import specification, but then load and save an export specification instead.

Both types of imports are stored in the same system tables, MSysIMEXSpecs and MSysIMEXColumns. In fact, when you go to load a specification, the dialog box even has the title, "Import/Export Specifications."

Changing Column Data Types for Export Specifications

Here are the steps needed to make "import spec-only" changes to export specifications:

From the 'External Data' tab in the Access Ribbon, click New Data Source > From File > Text File
In the 'Get External Data - Text File' wizard, browse to and select any text file (it doesn't matter what is in it, but pick a small one so the preview loads fast). Leave the "(o) Import the source data into a new table in the current database" option selected and click [OK].
In the next step, click [Advanced...] then [Specs...] then select "Sale Export Spec" (saved earlier) then click [Open].
Change the OrderDate field's Data Type from "Date With Time" to "Short Text"
Change the ShipDate field's Data Type from "Date With Time" to "Short Text"
To update the saved specification, click [Save As], verify the Specification Name is the same as before ("Sale Export Spec") then click [OK] and [Yes] to overwrite.
Click [OK] to close the specification settings dialog window.
Click [Cancel] to close the 'Import Text Wizard' dialog window.

Rerun the VBA code from above to test the updated specification:

DoCmd.TransferText acExportDelim, "Sale Export Spec", "Sale", "C:\SalesData.csv"

And here are the updated output results:

SaleID|OrderNumber|OrderDate|ShipDate|CustomerName|ShippingTrackingNumber|Notes
1|ORD000001|1/1/2023|1/3/2023|John Smith|0012345678901234567890|Please expedite per customer request. They said this order is "critical."
2|ORD000002|1/5/2023|1/9/2023|Jane Doe|0002345678901234567890|Shipping to alternate address. Note customer "prefers" east entrance.
3|ORD000003|1/7/2023|1/10/2023|Bob Johnson|0000345678901234567890|Handle with care. Customer said last shipment arrived "damaged."
4|ORD000004|1/9/2023|1/12/2023|Sam Roberts|0000045678901234567890|
5|ORD000005|1/15/2023|1/18/2023|Sue Taylor|0000005678901234567890|Include extra pads. Customer complained last ones "tore easily."
6|ORD000006|1/17/2023||Michael Smith, Sr.|0000000678901234567890|
7|ORD000007|1/20/2023|1/24/2023|Michael Smith, Jr.|0000000078901234567890|Must arrive by 1/28. Customer said "no excuses" this time.

Conclusion

Exporting data from an Access database table into a text-based format provides a flexible way to share and analyze data.

Saving and reusing export specifications can streamline the process, help maintain consistency across data exports, and accommodate special formatting needs. Unfortunately, there is no direct way to edit a saved export spec in Access. You have to go through the wizard again, make your changes, and re-save the spec with the same name.

If the whole process of managing export specifications seems a bit...cumbersome...that's because it is. There has to be a better way, right?

There is! And I hope you'll join me tomorrow at the December meeting of the Access Europe User Group to learn more about it.

Acknowledgements

Portions of this article's body generated with the help of ChatGPT
One or more code samples generated with the help of ChatGPT
Sample data generated with the help of ChatGPT
Cover image created with Microsoft Designer and StableDiffusionXL