Import Excel Data and Export a Fixed-Width File in Access
Question details
Create a repeatable fixed-width integration file from Excel data using Microsoft 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.
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.
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.
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.
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.
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.
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.
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.

Repeat a saved Access export
Use saved export steps to reduce manual work after the layout has been tested.
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.

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.




