excelvbacsv

Can I use a custom function to pull data from a csv file?


I need to extract data from a csv file into an existing excel file. I have little knowledge of custom functions and the deeper mechanics of excel as a whole so with the help of GPT 3.5 I developed a custom function to extract data from a test csv file with 2 records.

Here is the function GPT gave me:

Function GetCSVCellValueFromRecord(csvFilePath As String, recordIndex As Long, targetColumnName As String) As Variant
    Dim csvContent As String
    Dim lines() As String
    Dim headers() As String
    Dim columnIndex As Long
    Dim i As Long
    
    ' Read the entire CSV file into a string
    Open csvFilePath For Input As #1
    csvContent = Input$(LOF(1), 1)
    Close #1
    
    ' Split the CSV content into lines
    lines = Split(csvContent, vbCrLf)
    
    ' Get the headers from the first line
    headers = Split(lines(0), ",")
    
    ' Find the column index of the target data
    columnIndex = -1
    For i = LBound(headers) To UBound(headers)
        If Trim(headers(i)) = targetColumnName Then
            columnIndex = i
            Exit For
        End If
    Next i
    
    ' Return an error if the column name is not found
    If columnIndex = -1 Then
        GetCSVCellValueFromRecord = CVErr(xlErrValue)
        Exit Function
    End If
    
    ' Check if the requested record index is within bounds
    If recordIndex >= 1 And recordIndex <= UBound(lines) Then
        Dim fields() As String
        fields = Split(lines(recordIndex), ",")
        If UBound(fields) >= columnIndex Then
            GetCSVCellValueFromRecord = Trim(fields(columnIndex))
            Exit Function
        End If
    End If
    
    ' Return an error if the record or data is not found
    GetCSVCellValueFromRecord = CVErr(xlErrValue)
End Function

The function calls I tried are :=GetCSVCellValueFromRecord("potter.csv",1,"time") (the csv is in the same folder as the worksheet) =GetCSVCellValueFromRecord(Potter.csv,2,comment) both returned a #Value! error. I'm unsure what went wrong or where to go from here

My ultimate goal is to be able to type the csv file name into a field and have function calls refer to that cell in order to update all relevant fields with information from the csv.


Solution

  • If the issue is you need the full path but only want to pass in the filename, then this change

    Function GetCSVCellValueFromRecord(csvFilePath As String, recordIndex As Long, _
                                      targetColumnName As String) As Variant
        Dim csvContent As String
        Dim lines() As String, headers() As String
        Dim columnIndex As Long, i As Long, ff As Integer
        
        ' Read the entire CSV file into a string
        ff = FreeFile 'don't use a hard-coded value...
        Open ThisWorkbook.Path & "\" & csvFilePath For Input As ff
        csvContent = Input$(LOF(ff), ff)
        Close ff
        '...etc
    
    

    ...should do what you want as long as all CSV files are in the same folder where the workbook with the UDF is saved.