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.
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.