Results 1 to 3 of 3
  1. #1
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Extract a variable from a CSV file (VBA / Word 2003 / SP2)

    Hi all,

    I need to extract an element from a big CSV file. Or perhaps I should say wide CSV file. It has a Header and one record. I know the name of the field I want (I *may* I know the relative location of the field in the header string, but I need more info before making that assumption).

    But I just don't know how to grab the info and stuff it into a variable. I've gotten a little way down the road of using Split to break up the pieces and put them in an array, but now I'm stuck. Instructions I've found so far all have commented text at the sticking point saying essentially " 'now do what's required with the substring" and that's precisely what I'd like to do!

    Thanks in advance! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    Kim

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract a variable from a CSV file (VBA / Word 2003 / SP2)

    You could use this function

    Function ExtractFieldFromCSV(strCSV As String, strField As String)
    Dim f As Integer
    Dim strLine As String
    Dim arrLine1() As String
    Dim arrLine2() As String
    Dim i As Integer

    On Error GoTo ErrHandler

    ' Open file
    f = FreeFile
    Open strCSV For Input As #f

    ' Get first line
    Line Input #f, strLine
    ' Split
    arrLine1 = Split(strLine, ",") ' Application.International(wdListSeparator)

    ' Find fieldname in header
    For i = 0 To UBound(arrLine1)
    If arrLine1(i) = strField Then
    Exit For
    End If
    Next i

    If i > UBound(arrLine1) Then
    MsgBox "Field " & strField & " not found in header.", vbExclamation
    Exit Function
    End If

    ' Get second line
    Line Input #f, strLine
    ' Split
    arrLine2 = Split(strLine, ",") ' Application.International(wdListSeparator)

    ' Get element corresponding to field name
    ExtractFieldFromCSV = arrLine2(i)

    ExitHandler:
    On Error Resume Next
    Close #f
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Function

    Example of usage:

    MsgBox ExtractFieldFromCSV("C:MyFile.csv", "LastName")

  3. #3
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Extract a variable from a CSV file (VBA / Word 2003 / SP2)

    Hans!

    It's perfect. Just perfect. I don't completely understand it, but I will. In the meantime, it does exactly what I need.

    Thank you again for your generous help.
    Kim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •