How to Automatically Set Excel Column Widths When Importing TXT Files
Question details
The user needs to automate the process of importing fixed-width TXT files into Excel without manually setting the column widths and boundaries for every new report.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating recurring imports of fixed-width text files, such as monthly pricing reports or database exports, into a spreadsheet.
- Observed behavior
- The goal is to define the fixed-width positions once and simply refresh the query for future imports instead of redefining boundaries repeatedly.
Ensure your recurring TXT files maintain the exact same structural layout and character counts per column, and that you save the new files over the old ones in a consistent folder location.
Automate Fixed-Width TXT Imports using Power Query
Set up a Power Query connection to define your fixed-width column boundaries once, allowing you to instantly refresh the data when new files arrive.
Power Query is the most robust tool for automating repetitive data imports in Excel. By establishing a query, Excel records your exact column splitting steps and applies them automatically to any future data you refresh from that same file path.
Open Excel, go to the 'Data' tab, click on 'Get Data' (or 'Get Data' > 'From File'), and select 'From Text/CSV'. Locate your TXT file and click 'Import'.
When the preview window appears, do not click Load. Instead, click the 'Transform Data' button to open the Power Query Editor.
If Excel did not automatically parse the fixed widths correctly, right-click the main data column, select 'Split Column', and choose 'By Positions'. Enter the exact character positions (comma-separated, e.g., 0, 10, 25, 40) where you want the columns to break, then click 'OK'.
Once the columns look correct, click the 'Close & Load' button in the top-left corner. The cleanly separated data will appear in a new Excel worksheet.
When you receive a new version of the TXT file, save it in the same location with the exact same filename, overwriting the old file. Open your Excel workbook, go to the 'Data' tab, and click 'Refresh All'. Your column widths will automatically apply to the new data.
Use the Legacy Text Import Wizard for One-Off Imports
If you do not need to automate the process for future files, you can use the traditional Text Import Wizard to manually set column boundaries visually.
Easily Import Fixed-Width TXT Files with WPS Office
WPS Spreadsheet provides a highly intuitive, built-in Text Import tool. You can quickly define fixed-width column boundaries visually and seamlessly convert repetitive text reports into well-formatted spreadsheets without needing advanced query knowledge.
- 1. Open Data Import: Launch WPS Spreadsheet, navigate to the 'Data' tab, and click on 'Import Data'.
- 2. Select Fixed Width: Choose your TXT file from your local drive. When the Text Import Wizard opens, select the 'Fixed width' option and proceed to the next step.
- 3. Set Column Boundaries: Click directly on the preview window ruler to place vertical lines exactly where your text columns should break, then click 'Finish' to import the correctly structured data.
Frequently Asked Questions
Why does my fixed-width TXT file import into a single column?
This happens when the spreadsheet software fails to automatically detect the column boundaries because there are no clear delimiters like commas or tabs. You must use the Text Import Wizard or Power Query's 'Split Column by Positions' feature to manually tell the software exactly where each column ends.
Can I edit the fixed-width positions after saving my Power Query?
Yes. Open the Power Query Editor by double-clicking your query in the 'Queries & Connections' pane. Locate the 'Split Column by Position' step in the 'Applied Steps' window on the right, click the gear icon next to it, and adjust the character positions.
Does refreshing the Power Query overwrite my existing formatting in Excel?
Refreshing updates the raw data from the source file. If you applied standard cell formatting (like background colors or bold text) to the loaded Excel table, it usually remains intact. However, any manual text edits or formulas typed directly over the imported data will be overwritten upon refresh.
