Strip records from large textfile with VBA

[Spreadsheet Solutions]

Dear members;

I'm stuck with the following chalange.
I must read some (not all) records from a textfile into a workbook.
But......

The Excel version is 2003 (can and may not be 2007) and the textfile
contains over 250.000 records !!
The fields from the textfile are seperated by semi-colons and I only need
those records where the 5th field exceeds 0.
The textfile is not fixed-width, so I can't fix this position in a record
(but it is the position I need to find).

What method should I use to extract those records using VBA.

Is it possible to alter this module ?

Private Sub Extract_Records()
Open ThisWorkbook.Path & Application.PathSeparator & "Invoice.txt" For
Input As #1
r = 1
Do While Not EOF(1)
Line Input #1, Data
'Do not import a record that begins with Total
If Not Left(Data, 5) = "TOTAL" Then '----> I think I should change
this line of code, but How ? It must find the 5th field > 0
'Import record
r = r + 1
Cells(r, 1).Value = Data
End If
Loop
Close #1
End Sub

 

[joel]

try this code. I'm using split and texttocolumns.


Private Sub Extract_Records()
Open ThisWorkbook.Path & Application.PathSeparator & _
"Invoice.txt" For Input As #1
RowCount = 1
Do While Not EOF(1)
Line Input #1, Data

'create a zero index base array
splitdata = Split(Data, ",")

'Do not import a record that begins with Total
If Not splitdata(4) = "TOTAL" Then
Range("A" & RowCount) = Data
RowCount = RowCount + 1
End If
Loop
Close #1
LastRow = Range("A" & RowCount).End(xlUp).Row
Range("A1:A" & RowCount).TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True

End Su

 

[Mike H]

Hi,

This works for me

Private Sub Extract_Records()
Open ThisWorkbook.Path & Application.PathSeparator & "Invoice.txt" For Input
As #1
r = 1
Do While Not EOF(1)
Input #1, Data$
If UCase(Left(Data$, 5)) <> "TOTAL" And Val(Mid(Data$, 5, 1)) > 0 Then
r = r + 1
Cells(r, 1).Value = Data
End If
Loop
Close #1
End Sub


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

No it doesn't I created my test .txt file badly formatted
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[GS]

Hi Mark,

I normally associate a text file such as you describe here as being a 'data
dump'. Reason is that I can't imagine using a text file for storing large
amounts of data when a database has so much more to offer. However, if your
text file includes the field names in the first row then you could use ADO
and query it same as a database table, where the filename is the table name.
You could only return those records you want by specifying criteria in your
SQL statement. This way it only returns only the records you want, which is
better than searching every record to see if it contains what you want.

Be aware that Excel2003 only has 65,536 rows per sheet so you'll have to
start a new sheet [or column] if all 250,000 records are returned. Excel2007
will handle it without parsing to another sheet [or column].

HTH
Kind regards,
Garry

 

[Spreadsheet Solutions]

Thank you all.
It didn't solve my problem, but it did give some interesting hints for
possible workarounds.