Import and Format a TXT File into an Excel Table with VBA
Question details
The user needs to import a TXT file into an existing Excel table with VBA while handling blank lines, delimited records, and sections with variable lengths.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A VBA workflow must let the user choose a text file, read its contents, identify variable-length sections, split records by a delimiter, and write the fields to the RawData worksheet or an existing table.
- Observed behavior
- The text file contains blank lines and sections with different numbers of lines, so fixed row positions are unreliable when mapping records into the worksheet.
Confirm the target worksheet name, table name or starting cell, and the delimiter used by the TXT file. Also identify section labels or markers that VBA can use instead of assuming every section has a fixed number of rows.
Use VBA to import variable-length TXT sections into the RawData table
Use this approach when the file contains blank lines, delimited records, and sections whose lengths change between files.
Read the file line by line, skip empty records, and use labels or markers to determine the current section. Split each valid record with the actual delimiter, then write the resulting fields to the worksheet or resize the existing table as needed.
The delimiter in the example is a comma. Replace it with a tab, semicolon, pipe, or another separator when the source file uses a different format.
In Excel, press Alt+F11, choose Insert > Module, and create a standard module in the workbook that contains the RawData worksheet.
Use Application.GetOpenFilename with a TXT filter, open the selected file for input, and read each line with Line Input into a dynamic string array or collection.
Loop through the lines, skip entries where Trim$(line) is empty, and test each nonblank line for section labels or markers instead of relying on fixed row numbers.
Apply Split(line, delimiter), then write the resulting array to ThisWorkbook.Worksheets("RawData") or to the table's DataBodyRange starting at the next available row.
Add an error-handling section that closes the text file, restores Application.ScreenUpdating = True, and reports the error before the macro ends.
Map imported records to an existing Excel table
Use this option when the RawData table already has defined columns and imported fields must be placed in a consistent table row.
Troubleshoot delimiter and line-ending problems
Use this option when the macro imports one long column, creates too many columns, or misses records because the TXT file uses unexpected separators or line endings.
Process TXT files and organize imported data in WPS Spreadsheets
WPS Office can open and organize spreadsheet data in a familiar worksheet interface. Import the TXT content, split fields with the correct delimiter, and place the cleaned records into a structured table; macro availability and VBA compatibility can vary by WPS edition, so use the built-in import tools when a macro is not supported.
- 1. Open WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the destination workbook or create a new spreadsheet.
- 2. Import the text file: Choose Data > From Text/CSV or the available text import command, select the TXT file, and set the delimiter and text encoding in the import preview.
- 3. Review the parsed fields: Check that records appear in separate columns, remove blank rows if needed, and verify that section labels and variable-length records remain identifiable.
- 4. Format the destination range as a table: Select the imported range, choose Insert > Table, confirm that the range has headers when applicable, and apply the required column formatting.
Frequently Asked Questions
How do I import a TXT file into an existing Excel table with VBA?
Use Application.GetOpenFilename to select the file, read each line with Line Input, skip blank lines, split each record with the correct delimiter, and write the fields to the next worksheet row or a new ListObject row.
How can VBA handle TXT sections with different numbers of lines?
Use section labels, headers, or other markers to identify the current section. Do not calculate the next section from a fixed row number because the number of lines can change between files.
Why does the imported data appear in one column?
The Split delimiter probably does not match the source file. Inspect the TXT file and replace the comma in Split(line, ",") with vbTab, a semicolon, pipe, or the actual separator.
How should blank lines be handled during the import?
Trim each line and test whether it is empty before processing it. Skip empty lines so they do not create blank rows in the RawData worksheet or table.
What should the macro do if a record has fewer fields than expected?
Check the number of elements returned by Split before writing the row. Log or skip incomplete records, or fill missing table columns with empty values according to the required data structure.
