Importing Fixed Width Data Into Excel
This page describes code you can use to import fixed width fields from a text file into Excel.
Elsewhere on this site, we have code to import delimited
text from a text file into Excel. This page describes VBA code that you can use
to import a text file where the data fields are of a fixed length. Delimited
data is when the text fields are separated by some character such as a comma and
each field can be any length. Fixed field size data is when a field is defined
to always have the same number of characters, regardless of the length of the
data. The data is either truncated to make it shorted or padded to make it
larger.The code on this page allows you to import fixed width data in to
worksheet cells. Each line of the input file is transformed to a single row in
the worksheet and the columns of that row are populated with data read from the
text file.
For code to work with variable length delimited files, see Importing And Exporting Text.
To do the reverse, exporting Excel data to a fixed field length text file, see
Exporting Fixed Width Files.
The downloadable module file contain a function named
ImportFixedWidth that will import fixed width data fields into Excel. The
declaration of the function is:
Function ImportFixedWidth(FileName As String, _
StartCell As Range, _
IgnoreBlankLines As Boolean, _
SkipLinesBeginningWith As String, _
ByVal FieldSpecs As String) As Long
The parameters are:
FileName. This is the name of the file from which the data will be read. The file must exist.
StartCell. This is the first (upper left) cell where the import will begin. Each line in the
text file is written to one row in the worksheet, and each field of the imported line will be in its own column on the
current line.
IgnoreBlankLines. If True, a blank row will not be created in the worksheet if there is an empty
line in the input file. If False, a blank line will cause a blank row to be created in the worksheet.
SkipLinesBeginningWith. If this is not vbNullString, lines in the file that begin with this
string will not be imported. This is useful if you have comments in the text file that are not to be imported to the
worksheet
FieldSpecs. This string controls which columns and the length of data in those columns that
are to be imported to the workbook. The string has the form:
start,length|start,length|start,length
each start element indicates the position in the line of the text file where the field import
is to begin. Each length element indicates the length of the field to be imported. The fields
in FieldSpecs may be in any order and may overlap. For example,
1,10|21,3|15,5
This FieldSpecs string instructs the procedure import text beginning at start position 1 for a length of 10. Then, import
text starting at position 21 for a length of 3, and finally starting at position 15 for a length of 5. All
start values are 1-based from the beginning of the record.
You can specify a number format for the field which will be applied to the worksheet
cell. This format should not be in quotes and should follow the length elements in the FieldSpecs.
Commas are allowed in the number format string, since the code is smart enough not to use them as delimiters.
For example,
2,8|9,3,@|12,8,dddd dd-mmm-yyyy
This specifies that no formatting will be applied to column 2, the Text (literal)
@ format will be applied to column
9, and the format
dddd dd-mmm-yyyy will be applied to column 12. Use of formatting strings is optional. Use of the
@ code is often beneficial because it will prevent Excel from suppressing leading zeros in a string,
converting values to dates, and preventing Excel from displaying very large numbers in exponential form. Numeric strings
that have no numeric or mathematical meaning should format the cell with
@. For example, it is meaningless to do math on
phone numbers (What's the cube root of my phone number? Who cares?), so phone numbers should be formatted as text.
A note about date values... The code does not parse out date values from input strings. For example,
the value 12152011 will NOT be converted to the date 15-December-2011 if you use a date format
string in the FieldSpec for that element. Instead, it writes the value to the cell and formats
the value with the date format. In this example, the cell will display ### values in the worksheet cell
because the value 12,152,011 exceeds Excel's maximum date (31-December-9999 = 2,958,465). Remember
that formatting strings are just that, formatting. They cannot parse data or otherwise transform data.
The function returns as its result the number of records imported if successful,
or -1 if an error occurred.
The downloadable module also contains
a Private function named ImportThisLine.
This function is passed the current line from the text file and returns a Boolean
value of True or False indicating whether the line is to be imported. You may put
any logic in this function to determine whether the line should be imported. Usually,
this function will simply return True with no other logic. The declaration of this
function is:
Private Function ImportThisLine(S As String) As Boolean
Here, S is the current line of the text file but it has not yet been imported.
You can examine that text line and determine whether to import it. To import the line,
set the result of the function to True. To skip the line, set the result of the function to False.
This function is intended to be called by other VBA code, not in a worksheet cell or from the Macros dialog box. Therefore,
you can create a simple VBA procedures that passes the parametres to ImportFixedWidth and runs the code.
For example,
Sub TestImport()
Dim L As Long
L = ImportFixedWidth(FileName:="C:\A\TestImport.txt", _
StartCell:=Range("C3"), _
IgnoreBlankLines:=False, _
SkipLinesBeginningWith:=vbNullstring, _
FieldSpecs:="1,10|11,9|30,5|45,21")
End Sub
Function ImportFixedWidth(FileName As String, _
StartCell As Range, _
IgnoreBlankLines As Boolean, _
SkipLinesBeginningWith As String, _
ByVal FieldSpecs As String) As Long
Dim FINdx As Long
Dim C As Long
Dim R As Range
Dim FNum As Integer
Dim S As String
Dim RecCount As Long
Dim FieldInfos() As String
Dim FInfo() As String
Dim N As Long
Dim T As String
Dim B As Boolean
Application.EnableCancelKey=xlInterrupt
On Error Goto EndOfFunction:
If Dir(FileName, vbNormal) = vbNullString Then
' file not found
ImportFixedWidth = -1
Exit Function
End If
If Len(FieldSpecs) < 3 Then
' invalid FieldSpecs
ImportFixedWidth = -1
Exit Function
End If
If StartCell Is Nothing Then
ImportFixedWidth = -1
Exit Function
End If
Set R = StartCell(1, 1)
C = R.Column
FNum = FreeFile
Open FileName For Input Access Read As #FNum
' get rid of any spaces
FieldSpecs = Replace(FieldSpecs, Space(1), vbNullString)
' omit double pipes ||
N = InStr(1, FieldSpecs, "||", vbBinaryCompare)
Do Until N = 0
FieldSpecs = Replace(FieldSpecs, "||", "|")
N = InStr(1, FieldSpecs, "||", vbBinaryCompare)
Loop
' omit double commas
N = InStr(1, FieldSpecs, ",,", vbBinaryCompare)
Do Until N = 0
FieldSpecs = Replace(FieldSpecs, ",,", ",")
N = InStr(1, FieldSpecs, ",,", vbBinaryCompare)
Loop
' get rid of leading and trailing | characters, if necessary
If StrComp(Left(FieldSpecs, 1), "|", vbBinaryCompare) = 0 Then
FieldSpecs = Mid(FieldSpecs, 2)
End If
If StrComp(Right(FieldSpecs, 1), "|", vbBinaryCompare) = 0 Then
FieldSpecs = Left(FieldSpecs, Len(FieldSpecs) - 1)
End If
Do
' read the file
Line Input #FNum, S
If SkipLinesBeginningWith <> vbNullString And _
StrComp(Left(Trim(S), Len(SkipLinesBeginningWith)), _
SkipLinesBeginningWith, vbTextCompare) Then
If Len(S) = 0 Then
If IgnoreBlankLines = False Then
Set R = R(2, 1)
Else
' do nothing
End If
Else
' allow code to change the FieldSpecs values
If FieldSpecs = vbNullString Then
' FieldSpecs is empty. Do nothing, don't import.
Else
If ImportThisLine(S) = True Then
FieldInfos = Split(FieldSpecs, "|")
C = R.Column
For FINdx = LBound(FieldInfos) To UBound(FieldInfos)
FInfo = Split(FieldInfos(FINdx), ",")
R.EntireRow.Cells(1, C).Value = Mid(S, CLng(FInfo(0)), CLng(FInfo(1)))
C = C + 1
Next FINdx
RecCount = RecCount + 1
End If
Set R = R(2, 1)
End If
End If
Else
' no skip first char
End If
Loop Until EOF(FNum)
EndOfFunction:
If Err.Number = 0 Then
ImportFixedWidth = RecCount
Else
ImportFixedWidth = -1
End If
Close #FNum
End Function
The default version of ImportThisLine is shown below. It simply returns True
to include the line in the import. You can rewrite the body of the formula according to your business
rules to include or exclude the line from importation.
Private Function ImportThisLine(ByRef S As String) As Boolean
ImportThisLine = True
End Function
Since the parameter S is passed ByRef, the code in the
ImportThisLine can modify the string to be imported. I don't recommend this, as it
complicates the code, but it can be done if necessary.
|
This page last updated: 2-Sept-2011.
|