Opening and Testing Large Text File

[Guest]

I have a large (150,000 record) text file that I want to open, test for a
certain condition, and save selected records into Excel. The records have
fixed width fields.
What is the necessary code to:

1. Open the text file.
2. Dimension the fields.
3. Loop through the records and test whether filed #1 = "6403".
4. Save the records found in step three to Excel.

In the old days I could do it with BASIC, but can't get the VBA coding for a
macro.

TIA.

 

[Robin Hammond]

Ken,

Use ADO rather than Excel. It is massively more powerful for this kind of
thing, with the ability to filter a recordset.

1. I created a 2 column csv file with a top row showing ID, Value
2. Remainder of file is in format 2344, 22 with some rows containing the
value 6403.
3. Create a new module, goto Tools, References and set a reference to
Microsoft Activex Data Objects.
4. Code like this:

Option Explicit
'requires a reference to MS ado data objects
Sub OpenRSFromText()
Dim oConn As ADODB.Connection
Dim rsInput As ADODB.Recordset
Dim strPath As String

'change path to folder
strPath = "C:\My Documents\My Excel Files\Temp\"
Set oConn = New ADODB.Connection
Set rsInput = New ADODB.Recordset

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPath & ";" & _
"Extended Properties=""text;HDR=YES;FMT=CSVDelimited"""

'adjust the path to the file
rsInput.Open "SELECT * FROM Book2.csv", _
oConn, adOpenStatic, adLockOptimistic, adCmdText
rsInput.Filter = "ID = 6403"
'change your target destination here
ActiveSheet.Cells(1, 1).CopyFromRecordset rsInput
MsgBox rsInput.RecordCount & " records found with ID 6403"
End Sub

This will give you more information. You might have to create a schema file
if there are no headers in the file.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp

HTH,

Robin Hammond
www.enhanceddatasystems.com

 

[Guest]

Robin, this worked great with the CSV file. With the link you provided I
should be able to make it with with the .ini for my fixed width file.
Thanks a bunch!