Conditional Import Text Files

August 26, 2008 – 2:11 pm by JP

If you have a text file to import, but only need some of the data, you could just import the whole thing and then manually scrub the data to find what you want. Text files can contain a lot more lines than an Excel worksheet, but much of it may be fluff. Here's a method for importing only the data you need.

First I created a text file with some sample data, here's a screenshot:

I realize it's short, but it serves our purpose. Here is the import sub:

plain text version

VBA:

Sub ConditionalImport()
Dim lNum As Long
Dim strLine As String
Dim lNextRow As Long
Application.ScreenUpdating = False
' get number of next free file
lNum = FreeFile
' open text file for input, loop through each line,
' only import rows we need
Open "C:\testfile.txt" For Input As #lNum
Do While Not EOF(lNum)
lNextRow = WorksheetFunction.CountA(Range("A:A"))
Line Input #lNum, strLine
'
' test "strLine" here
'
Loop
Close lNum
Application.ScreenUpdating = True
End Sub

The part right after "Line Input #lNum, strLine" is the part you would customize. Each line of the text file is read into the string variable strLine. Now you can test to see if it is a number (IsNumeric), has a certain number of characters (Len), the way you would do with any other String to see if meets the criteria you want to specify.

For example, if I only wanted to import the data rows that start with "ABC", I would use this:

plain text version

VBA:

Sub ConditionalImport()
Dim lNum As Long
Dim strLine As String
Dim lNextRow As Long
Application.ScreenUpdating = False
' get number of next free file
lNum = FreeFile
' open text file for input, loop through each line,
' only import rows we need
Open "C:\testfile.txt" For Input As #lNum
Do While Not EOF(lNum)
lNextRow = WorksheetFunction.CountA(Range("A:A"))
Line Input #lNum, strLine
If (Left$(strLine, 3) = "ABC") Then
Cells(lNextRow + 1, 1).Value = strLine
End If
Loop
Close lNum
Application.ScreenUpdating = True
End Sub

Enjoy,
JP

Share and Enjoy:

Print This Post |

Email This Post | Permalink |

Subscribe now!

Filed Under: Excel
Tags: conditional import, EOF, read text file

This post has 72 views since August 26, 2008 – 2:11 pm.

3 Responses to “Conditional Import Text Files”
Instead of writing each line to the sheet as it is approved, write it to an array. Then at the end of the procedure, dump the array in one operation to the worksheet.

(Also, your original code increments lNextRow even if the input value for that row is not approved.)

Sub ConditionalImport()
Dim lNum As Long
Dim strLine As String
Dim lNextRow As Long
dim vArray() as Variant

Application.ScreenUpdating = False

' get number of next free file
lNum = FreeFile

' open text file for input, loop through each line,
' only import rows we need

Open "C:\testfile.txt" For Input As #lNum
lNextRow = 0
ReDim vArray(1 To 1, 1 To 1)
Do While Not EOF(lNum)
Line Input #lNum, strLine

If (Left$(strLine, 3) = "ABC") Then
lNextRow = 1 + lNextRow
ReDim Preserve vArray(1 To lNextRow, 1 To 1))
Cells(lNextRow + 1, 1).Value = strLine
End If

Loop

ActiveSheet.Cells(1, 1).Resize(lNextRow).Value = vArray

Close lNum

Application.ScreenUpdating = True
End Sub

By Jon Peltier on Aug 26, 2008
I like your code better, but where do you see lNextRow being incremented? It's based on "WorksheetFunction.CountA(Range("A:A"))", which wouldn't change if we didn't write the line to the worksheet.

--JP

By JP on Aug 27, 2008
JP - lNextRow updates here after the condition is tested and passes:

If (Left$(strLine, 3) = “ABC”) Then
lNextRow = 1 + lNextRow

By Jon Peltier on Aug 27, 2008