Export data to a text file

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

This topic explains how to export text files by using the Export Wizard in Microsoft Access.

What do you want to do?

Understand exporting text files

Export data to a text file

What else should I know about exporting?

Troubleshoot missing and incorrect values in a text file

Understand exporting text files

You can export data from Access in many different formats, including Excel, Word, and SharePoint lists. However, you may have to export data to a programme that employs a file format that Access does not facilitate. In that case, if the destination programme can use text (.txt) files, you can export your data in that format and launch the resulting file with the second programme.

You can export tables, queries, forms, and reports as text files. You can even export a specified portion of a datasheet view. After you export tables and queries, you have the option of exporting the whole object, or only the data without any deeper formatting.

At the end of the operation, Access produces a text file (*.txt). If you decide to ignore formatting, you have the option of designing a delimited text file or fixed-width text file. If you select to export formatted data, Access pursues with approximating the layout of the source object.

The Export Wizard forms the following types of text files:

  • Delimited files  –   In a delimited file, every record is listed on its independent line, and the fields are split by a single character called the delimiter. The delimiter can manifest as any character that does not appear in the field values, such as a comma or semi-colon.

1,Company A,Anna,Bedecs,Owner
2,Company C,Thomas,Axen,Purchasing Rep
3,Company D,Christina,Lee,Purchasing Mgr.
4,Company E,Martin,O’Donnell,Owner
5,Company F,Francisco,Pérez-Olaeta,Purchasing Mgr.
6,Company G,Ming-Yang,Xie,Owner
7,Company H,Elizabeth,Andersen,Purchasing Rep
8,Company I,Sven,Mortensen,Purchasing Mgr.
9,Company J,Roland,Wacker,Purchasing Mgr.
10,Company K,Peter,Krschne,Purchasing Mgr.
11,Company L,John,Edwards,Purchasing Mgr.
12,Company M,Andre,Ludo,Purchasing Rep
13,Company N,Carlos,Grilo,Purchasing Rep


You only have the option of making a delimited file once you export the contents of a table or query without formatting. A delimited file includes each of the records and fields in the deeply ingrained object. Hidden columns and filtered rows are exported in addition with all of the other data.

  • Fixed-width files   –  In a fixed-width file, all records display on an individual line, and the width of each field stays the same across records. In other words, the length of the first field of every record might always be seven characters, the length of the second field of every record might always be 12 characters, and so on. If the actual values of a field vary from record to record, the values that fall short of the required width will be padded with trailing spaces.

1 Company A Anna Bedecs Owner
2 Company C Thomas Axen Purchasing Rep
3 Company D Christina Lee Purchasing Mgr.
4 Company E Martin O’Donnell Owner
5 Company F Francisco Pérez-Olaeta Purchasing Mgr.
6 Company G Ming-Yang Xie Owner
7 Company H Elizabeth Andersen Purchasing Rep
8 Company I Sven Mortensen Purchasing Mgr.
9 Company J Roland Wacker Purchasing Mgr.
10 Company K Peter Krschne Purchasing Mgr.
11 Company L John Edwards Purchasing Mgr.
12 Company M Andre Ludo Purchasing Rep
13 Company N Carlos Grilo Purchasing Rep

You only have the option of forming a fixed-width file whilst you export the contents of a table or query without formatting. A fixed-width file embodies each of the records and fields in the underlying object. Hidden columns and filtered rows are exported along with all of the other data.

  • Formatted files  –   In a formatted file, hyphens (-) and pipe characters (|) are used to organise the content in a grid. The records display as rows, and fields appear as columns. The field names appear in the first row. --------------------------------------------
    | ID | E-mail Address |
    --------------------------------------------
    | 1 | nancy@northwindtraders.com |
    --------------------------------------------
    | 2 | andrew@northwindtraders.com |
    --------------------------------------------
    | 3 | jan@northwindtraders.com |
    --------------------------------------------
    | 4 | mariya@northwindtraders.com |
    --------------------------------------------
    | 5 | steven@northwindtraders.com |
    --------------------------------------------
    | 6 | michael@northwindtraders.com |
    --------------------------------------------
    | 7 | robert@northwindtraders.com |
    --------------------------------------------
    | 8 | laura@northwindtraders.com |
    --------------------------------------------
    | 9 | anne@northwindtraders.com |
    --------------------------------------------
    You only have the option of placing a formatted file whilst you select to export the data in a table, query, form, or report along with the formatting. A formatted file contains only those records and fields that are within in the source object or presented in the existing view. Hidden columns and filtered rows are not exported.

Top of Page

Export data to a text file

The process of exporting data as a text file follows these broad steps:

  • Open and review the source database;

  • Run the export wizard;

  • Save your export settings and review the text file.

The following sets of steps explain how to perform each task.

Open and review the source database

  1. Enter the source database in Access.

  1. In the Navigation Pane, pick the object that has your desired data to export. You can export a table, query, form, or report.

Note: You can export only one database object in a single export operation. When you export a form or datasheet that contains subforms or subdatasheets, only the main form or datasheet is exported. You must repeat the export operation for each subform and subdatasheet that you want to view in the text file. On the other hand, when you you export a report, subforms and subreports that are included in the report are exported along with the main report.

  1. Review the source data to make sure it does not contain error indicators or values.If the data contains errors, make sure you resolve them before you export the data. Otherwise, null values will appear in the text file.

  1. If the source object is a table or query, decide whether you want to export the data with or without the object’s formatting.This decision affects three aspects of the resulting file — the type of text file that the process creates, the amount of data that is exported, and the display format of the data. The following table describes the outcome of exporting formatted and unformatted data.

  1. To export only a portion of a table, query, or form, open the object in datasheet view, and then select only the records that you want.

Top of Page

Run the Export Wizard

  1. In the Access Navigation Pane, right-click the source object, point to Export, and then pick Text File. You can even open the Export – Text File wizard by highlighting the source object in the Navigation Pane and then on the External Data tab, in the Export group, press Text File. Access launches the Export – Text File dialogue box.Select your export options on the Export - Text File dialog box.
  2. In the Export – Text File dialogue box, accept or edit the name that Access advises for the text file.

  1. If you are exporting a table or a query, and you want to export data with formatting and layout, choose the Export data with formatting and layout check ox. If you are exporting a form or a report, the option is always chosen, but looks dimmed.

Note: If you don’t select the first checkbox, you can’t select the second or third checkboxes.

  1. To see the destination text file after the export operation is complete, click the Open the destination file after the export operation is complete checkbox.

  1. If the source is a datasheet, and you picked some records in the open datasheet before commencing the export operation, you can pick the Export only the selected records checkbox. However, if you want to export every record that are in the datasheet view, leave the checkbox unselected.

Note: This checkbox is unavailable (appears dimmed) if none of the records in the source datasheet are selected, or if the source is not a datasheet.

  1. Select OK.

  1. If a text file that you specify in step 2 is pre-existent, Access urges you to update the file. Press Yes to overwrite, or select No to return to the Export – Text File dialogue box to confirm a new name. Important: You cannot import data to an existing text file.

  1. If you are exporting data with formatting and layout, you are requested to select the encoding to be used for saving the file. Either adopt the default, or specify your preferred option, and then press OK. Access exports the data, and presents the status on the final page of the wizard.

  1. If you decide to export the data without any formatting or layout, the Export Text Wizard starts and asks you to pick the type of text file that you want to create. Press Delimited or Fixed-Width, and then choose Next. The choice you make normally is determined by the system that works with the exported files. Some programmes operate with delimited files, and some will work with either delimited or fixed-width. If users must review the data, a fixed-width file can be considerably easier to read than a delimited file.

  1. Do one of the following sections, according to your choice you made in the previous step:

  • Delimited    

  • Below Choose the delimiter that separates your fields, pick or elaborate the character that delimits the fields – Tab, Semicolon, Comma, Space, or Other.

  • To add the field names in the text file, click the Include Field Names on First Row checkbox.

  • In the Text Qualifier list, choose the text qualifier (the character that is used to enclose text values). If your source data contains multivalued fields, and if you picked the semicolon as the delimiting character, choosing a text qualifier is crucial, because encompassing the value list in a pair of single or double quotation marks assists with retaining the values in the value list together.

Fixed-width    

  • Check and place the vertical lines that split the fields. If necessary, scroll to the right to see all of the fields.

  1. On the last page of the wizard, you can alter the file name and path for the text file, or simply leave it as its default state and pick Next.

  1. Select Finish. Access exports the data, and shows the status of the export operation on the final page of the wizard.

Top of Page

What else should I know about exporting?

  • For information on how to change a specification name, delete specifications, or update the names of source files in specifications, see the article Manage Data Tasks.

Top of Page

Troubleshoot missing and incorrect values in a text file

The following table describes various ways of troubleshooting common errors.

Tip: If you find that just a few values are missing, fix them in the Excel file. Otherwise, fix the source object in the Access database, and then repeat the export operation.

IssueDescription
Missing field namesIn a fixed-width file, the field names are missing. Manually add them to the text file. In a delimited file, the field names are included if you select the Include Field Names on First Row checkbox in the wizard.
Display of multivalued fieldsBy default, fields that support multiple values are exported as a list of values separated by semicolons (;) and enclosed in double quotation marks (“”). If you set the field delimiter to be the semicolon, and if you changed the text qualifier to None, each value in the multivalued list might appear to belong to an individual field. Either change the delimiter settings and run the export operation again, or manually enclose the values of the multivalued field in the text file in double quotes.
Missing pictures, objects, and attachmentsGraphical elements (such as a logo, the contents of OLE object fields, and attachments that are part of the source data) are not exported.
Missing GraphWhen you export a form or a report that contains a Microsoft Graph object, the Graph object does not get exported.
Missing expressionsThe expression that is used to calculate the values is not exported to the text file. Only the results of the expressions are exported. Manually add the formula to the text file after completing the export operation.
Missing subforms and subdatasheetsWhen you export a form or a datasheet, only the main form or datasheet is exported. Repeat the export operation for each subform and subdatasheet that you want.
1 and 0 values appear in Yes/No fieldsWhen you export data to a delimited or fixed-width file, the values in Yes/No fields appear as 1 (True or Yes) and 0 (False or No). In the text file, perform a search-and-replace operation to correct the values.
Null valuesCheck the source file to see whether the corresponding value appears correctly in the source field. If you see an error value or an unsupported value, correct the source, and then try exporting again.

Top of Page

Leave a Reply

%d bloggers like this: