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.