CODEWITHSUNDEEP
vbacsvms-word
user2883071
user2883071

Reputation: 980

Searching for a row in a csv file using VBA

What kind of functions can I use to search for a line in a vba file, and then store this file to an array. Example:

job,boe,sig,dive,mag,num,
blah,blah,,,,,
wuttt,ais,,sidji,,,

look for the line that has "blah" in the first column and store it to an array.

Currently I am using the bash nawk command to find the line in a csv file, and then put this into a text file. Then I use:

Open "C:\file2.csv" For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, ",")

To store the line in an array. To make it efficient, I want to cancel out the bash portion and read the line in directly from the csv file using VBA.

Upvotes: 0

Views: 4946

Answers (1)

Tmdean
Tmdean

Reputation: 9299

In VBA, there are two standard options for reading files.

  1. The built in VBA file access statements and functions: Open, Input, Write, Print, Get, Put, et al. Consult your favorite VBA reference for details.
  2. Scripting.FileSystemObject. Add a reference to "Microsoft Scripting Runtime". This should be available on every Windows PC since around Windows 98, so there isn't a compatibility risk.

Both methods have benefits and drawbacks. The built in commands support binary files, but the API is strange and based off of very old versions of BASIC. FileSystemObject has a more modern API, but only supports text files.

Example using method 1:

Function FindEntry(file_path As String, _
                   field_index As Long, field_value As String) As Variant
    Dim file_number As Integer
    Dim raw_line As String
    Dim csv_line As Variant

    file_number = FreeFile
    Open file_path For Input As #file_number

    Do Until EOF(file_number)
        Line Input #file_number, raw_line
        csv_line = ProcessCsvData(raw_line)

        If UBound(csv_line) < field_index Then
            ' Guard against index out of bounds
        ElseIf csv_line(field_index) = field_value Then
            FindEntry = csv_line
            Exit Do
        End If
    Loop

    Close #file_number
End Function

Example using method 2:

Function FindEntry(file_path As String, _
                   field_index As Long, field_value As String) As Variant
    ' use the type Scripting.FileSystemObject if you include a reference
    ' to "Microsoft Scripting Runtime"
    Dim fs As Object
    Dim txt_file As Object
    Dim csv_line As Variant

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set txt_file = fs.OpenTextFile(file_path)

    Do Until txt_file.AtEndOfStream
        csv_line = ProcessCsvData(txt_file.ReadLine)

        If UBound(csv_line) < field_index Then
            ' Guard against index out of bounds
        ElseIf csv_line(field_index) = field_value Then
            FindEntry = csv_line
            Exit Do
        End If
    Loop

    txt_file.Close
End Function

Both methods are using a function called "ProcessCsvData", which is a function that you need to write that will split a CSV line into fields. A very naive implementation of this function would be like this.

' Warning: This code is wrong!
Function ProcessCsvData(csv_line As String) As Variant
    ProcessCsvData = Split(csv_line, ",")
End Function

Of course, that doesn't take into account fields that include commas in them, which are typically marked by enclosing the field in quotes in most CSV formats.

Upvotes: 2

Related Questions