How to Reuse Fixed-Width Text Import Settings in Excel
Question details
The user needs to save and reuse fixed-width column break settings established during text imports so they don't have to manually recreate them for future files.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing multiple text or CSV files with identical fixed-width data structures into an Excel workbook.
- Observed behavior
- Saving the workbook as an Excel template (.xltx) preserves the layout and formatting but fails to retain the fixed-width column break settings created with the Text Import Wizard.
Before you begin, verify that the text files you plan to import consistently follow the exact same fixed-width formatting and character spacing to prevent data misalignment.
Use Power Query to Save Import Settings
Power Query allows you to define your parsing rules and column transformations once, which you can easily refresh whenever new data needs to be imported.
Power Query is the most robust tool for handling repetitive data import tasks in modern versions of Excel. Instead of manually running the Text Import Wizard every time, you can create a permanent connection to your text file and define the extraction rules. When you receive an updated file, you simply refresh the query.
Go to the Data tab on the Excel ribbon, click on 'Get Data', select 'From File', and then choose 'From Text/CSV'.
Locate and select your fixed-width text file, click 'Import', and then click 'Transform Data' (or 'Edit') in the preview window to open the Power Query Editor.
In the editor, right-click the single column containing your text, select 'Split Column', and choose either 'By Number of Characters' or 'By Positions' to manually define your fixed-width column breaks.
Once your columns are correctly aligned, click 'Close & Load' on the Home tab. To reuse these settings for future files, replace the source file with your new file (keeping the same file name and path) and click 'Refresh All' on the Data tab.
Automate the Text Import Wizard with a Macro
Recording a VBA macro captures your manual steps in the Text Import Wizard, allowing you to execute the exact same fixed-width splits with a single click.
Automate Fixed-Width Text Imports in WPS Spreadsheet
WPS Spreadsheet provides robust data import tools and seamless VBA/Macro compatibility, making it incredibly easy to process repetitive fixed-width text files without manually reconfiguring settings.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and navigate to the 'Data' tab on the main ribbon.
- 2. Select Data Import: Click 'Import Data' and locate the fixed-width text file you wish to process.
- 3. Configure the Import Wizard: Follow the on-screen prompts to set your fixed-width breaks accurately.
- 4. Record a Macro for Reuse: Use the Developer tab to record these import steps as a macro, enabling instant reuse for all future files with the same structure.
Frequently Asked Questions
Why don't regular Excel templates save my Text Import Wizard settings?
Standard Excel templates (.xltx) are designed to preserve cell formatting, worksheet layouts, and formulas. They do not store external data connection configurations or the manual wizard steps required to parse incoming text files. To preserve these operations, you must use Power Query or save a VBA script in a macro-enabled template.
How do I refresh Power Query if my text file changes?
If your updated text file is saved in the same folder with the exact same name, simply go to the Data tab and click 'Refresh All'. If the file name or folder location is different, open the Power Query Editor, look at the 'Applied Steps' pane on the right, double-click the 'Source' step, and update the file path to point to your new file.
Can I share a macro-enabled template with my team?
Yes, you can share an .xltm (Excel Macro-Enabled Template) file with your colleagues. Just remember that for the automated import to work, the recipient must have macros enabled in their spreadsheet software's Trust Center settings.
