[Excel VBA] How to Import Fixed-Width Text Files (Using OpenText + FieldInfo)

Fixed-width text files (also known as position-based formats) are structured so that each item has a set number of characters, unlike CSVs which use delimiters. To accurately import such data into Excel, the Workbooks.OpenText method and FieldInfo settings in VBA are essential.

In this article, I will introduce a VBA macro to automatically import fixed-width text files into Excel.

Suitable Scenarios for Fixed-Width Import

• The text file is saved as “fixeddata.txt”.
• Data is placed at fixed positions in each row.
  • Example: Name (6 chars), Date of Birth (10 chars), Zip Code (20 chars), Amount (9 chars), ID Code (10 chars).
• You want to organize this data neatly into Excel columns.

VBA Code: Importing Fixed-Width Text to Excel

Sub ImportFixedLengthTextFile()
    Dim txtFilePath As String
    txtFilePath = ThisWorkbook.Path & "\fixeddata.txt"
    
    Workbooks.OpenText _
        Filename:=txtFilePath, _
        DataType:=xlFixedWidth, _
        FieldInfo:=Array( _
            Array(0, xlTextFormat), _
            Array(6, xlYMDFormat), _
            Array(16, xlTextFormat), _
            Array(36, xlGeneralFormat), _
            Array(45, xlTextFormat))
End Sub

Explanation of Parameters

• Filename: The file path of the text file to be imported.
• DataType:=xlFixedWidth: Specifies that the file format is fixed-width.
• FieldInfo: An array that specifies the start position (character position starting from 0) and the display format for each column.

Here is an example of how to interpret FieldInfo:

Important Notes

1. Character Counts: Field width boundaries are interpreted as character counts.
2. Encoding: If the file contains double-byte characters or is UTF-8 encoded, it may not be processed correctly depending on your system settings.
3. Dates: By setting a column to xlYMDFormat, Excel will automatically recognize the string as a date value.

Summary

By utilizing Workbooks.OpenText and FieldInfo in VBA, you can automatically format and import fixed-width text files into Excel columns. This technique is highly useful for processing order data or financial records often found in business operations. Please give it a try!