CODEWITHSUNDEEP
vbaexcel
Arun Kalyanaraman
Arun Kalyanaraman

Reputation: 648

Parsing Data in Excel Causes Crash

I was wondering if anyone knew a way to parse rather large data files in Excel VBA because whenever I try the simple data parse it crashes the program. The data is formatted as such

593972,Data,15:59:59.820,9519,9519,Px(25.5),9519,9500,10001,10226,10451,0,0,0,0,0,28.7604,25.4800,25.4841

and there are about 3 million lines formatted exactly the same and I want to pull out certain values in the line if the first value (in the case above it is 593972) is a specific number. I am rather new to VBA so any help would be much appreciated. Thanks so much for your time!

Upvotes: 1

Views: 275

Answers (2)

D_Bester
D_Bester

Reputation: 5931

Try using FSO; modify to suit your needs.

Sub ParseFile()

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Dim strLine As String
Dim arrLine() As String
Dim objFile
Const ForReading = 1, ForWriting = 2, ForAppending = 8

Set objFile = fso.OpenTextFile("C:\Temp\Text File.txt", ForReading) '<modify path as needed

Do Until objFile.AtEndOfStream
    strLine = Trim(objFile.Readline)
    If (strLine <> "") Then
        arrLine = Split(strLine, ",") 'one dimensional array

        'parse the arrLine to test for the data you need
        Dim FirstValue as String
        FirstValue = arrLine(0)

        If FirstValue = "593972" Then

            'put the data in Excel if desired/needed

        End If

    End If
Loop

objFile.Close
Set objFile = Nothing

End Sub

Upvotes: 1

user2271770
user2271770

Reputation:

The Sub below opens a text stream, reads it line by line, and verifies if the first field has a certain value for each line; adapt it to do what you'd want:

Public Sub ReadAndValidate( _
   ByVal FileName As String, _
   ByVal FieldKey As String _
)
        ' This function doesn't do error handling, assumes that the '
        ' field separator is "," and that the key field is first.   '
        ' It uses the "Scripting" lib; "Microsoft Scripting Runtime"'
        ' needs to be referenced by the containing  workbook.       '

        Dim line    As String
        Dim keylen  As Long

        Dim fs      As Scripting.FileSystemObject
        Dim f       As Scripting.TextStream

        Let FieldKey = FieldKey & "," ' add the separator to the key '
        Let keylen = Strings.Len(FieldKey)

        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.OpenTextFile( _
           FileName:=FileName, _
           IOMode:=IOMode.ForReading _
        )

        While Not f.AtEndOfStream
                Let line = f.ReadLine()
                If Strings.Left$(line, keylen) = FieldKey Then
                       ' replace the statement below with your code '
                       Debug.Print line
                End If
        Wend

        f.Close

End Sub

Upvotes: 0

Related Questions