Strip records from large textfile with VBA
 

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

----
I hope that this is clear enough !!


--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands
--
E:
W: www.spreadsheetsolutions.nl
--

Strip records from large textfile with VBA
 

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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175311

Microsoft Office Help

Strip records from large textfile with VBA
 

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.


"Spreadsheet Solutions" wrote:

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

----
I hope that this is clear enough !!


--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands
--
E:
W: www.spreadsheetsolutions.nl
--

.

Strip records from large textfile with VBA
 

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.


"Mike H" wrote:

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.


"Spreadsheet Solutions" wrote:

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

----
I hope that this is clear enough !!


--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands
--
E:
W: www.spreadsheetsolutions.nl
--

.

Strip records from large textfile with VBA
 

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

Strip records from large textfile with VBA
 

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

--
Met vriendelijk groet;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Nederland
--
E:
W: www.spreadsheetsolutions.nl
--
"Spreadsheet Solutions" wrote in message
...
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

----
I hope that this is clear enough !!


--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands
--
E:
W: www.spreadsheetsolutions.nl
--