0

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.

Improve this question
1
  • @MaciejLos I have edited the post and added my attempt Commented Apr 15, 2015 at 17:55

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

This is an awesome start, however I was looking for a more efficient way of finding for the correct line without storing the whole file in an array or a script. But I guess I can adapt method 1 to loop until the line beginning with "blah" has been found, and then break out of the loop
Yep, that would be the general idea. Loop until csv_line(0) = "blah" and then break and return csv_line from the function. Just don't forget to close the input file.
I've updated the code to match your specification. The functions now take a field index and a field value to search for and return the first line that matches as an array. If a line is not found, they return Empty.
It's a variant array. You should be able to access it like any other array. Declare the variable that receives the result as a Variant and index it like a normal array e.g. result(3)
Don't forget to check if no result was found before you try to index it, though. If IsEmpty(result) Then Debug.Print "no result found"
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.