Data Import & Export

Import Excel Data and Export a Fixed-Width File in Access

Emma BrownEmma Brown Nov 20, 2026 868 views

Question details

Create a repeatable fixed-width integration file from Excel data using Microsoft Access.

Import Excel Data and Export a Fixed-Width File in Access
Product
Microsoft Access and Microsoft Excel
Device & OS
not provided
Scenario
Five datasets have different lengths and must be used to produce an integration file of approximately 5,500 characters.
Observed behavior
The desired output is a fixed-width text file with the required field positions and a process that can be repeated or automated.
Before you start

Obtain the integration layout, including field order, widths, padding, encoding, and whether 5,500 characters refers to each record or the entire file. Confirm whether the five datasets require separate record layouts or must be combined into one layout.

Solution 1Recommended

Import the Excel data and save a fixed-width export specification

Use Access to prepare the Excel data and export it with repeatable field positions and widths.

If the five datasets have different record layouts, prepare a separate table or query and export specification for each layout. If the receiving system requires one file, the exported records may need an additional combination step in the required order.

Test the full layout early: Access field and text-export limits may affect a long or complex record. A custom export routine may be needed if the wizard cannot produce the required layout.

1
Import the Excel data

In Access, open the database and select External Data > New Data Source > From File > Excel. Browse to the workbook and use the import wizard to create tables for the required worksheets or ranges.

2
Prepare the export fields

Open each imported table in Design View to check data types and field sizes. Create a query in Create > Query Design to arrange fields in the required order and apply any needed formatting or padding.

3
Configure the fixed-width export

Select the table or query in the Navigation Pane, then choose External Data > Text File. Choose a destination and follow the Export Text Wizard, selecting Fixed Width when prompted. Set field boundaries and check that the resulting widths match the integration layout.

4
Save the settings

In the export wizard, select Advanced and use Save As to name the export specification. Complete the wizard and, if offered, select Save export steps to make the export available for repeat runs.

5
Validate the output

Open the exported text file in an editor that displays character positions. Check field boundaries, padding, record lengths, encoding, and line endings against the receiving system's specification.

Import the Excel data and save a fixed-width export specification
Check how length is measured: A character count may differ from a byte count, and line-ending characters may affect the total file size. Verify the receiving system's rules before treating the output as complete.
Free Microsoft Office alternative

Prepare Excel Source Data with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open and edit compatible Excel workbooks in WPS Spreadsheets before importing them into Access; WPS does not replace Access's fixed-width export wizard.

Review and organize source columns for the fixed-width integrationOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Can Access reuse a fixed-width export layout?

Yes. In the Export Text Wizard, select Advanced > Save As to save an export specification. You can also save the completed export steps and run them later from External Data > Saved Exports.

How do I handle five datasets with different layouts?

Create a table or query and an export specification for each distinct layout. If the receiving system requires a single file, combine the exported records in the specified order and validate the result.

Does a 5,500-character requirement include line endings?

It depends on the integration specification. Confirm whether the limit applies to each record or the whole file, whether it is measured in characters or bytes, and whether line endings are included.

What if the Access export wizard cannot create the required record?

Test the complete layout in External Data > Text File. If Access field or export limits prevent the required output, use a custom export routine or another tool and validate its encoding, padding, and record lengths.

Can I use WPS Spreadsheets to prepare the Excel workbook?

Yes. Open the XLS or XLSX workbook in WPS Spreadsheets to review and organize the source data, then import the saved workbook into Access. Use Access or a suitable custom routine for the fixed-width export.