Import csv data in certain columns

[Ge0K]

Hello ! I would like to make an excel in which I can keep track of my expenses. I would like a code that would import from a CSV file depending on certain keywords the expenses in predefined columns.
Can someone help me with some VBA code?
Thank you in advance !

 

[Yousuf Shovon]

I would like a code that would import from a CSV file depending on certain keywords the expenses in predefined columns.
Can someone help me with some VBA code?

Hello Ge0K,

Welcome to ExcelDemy Forum! We understand you want to import CSV data in certain columns of an Excel file using VBA. It would be great if you could share a sample dataset or the code you have tried so far for a better understanding of your situation. However, the below article has 3 VBA codes with 3 examples that will give you an idea regarding this topic. So, please read it carefully:

Excel VBA to Import CSV File without Opening​

To import a CSV file depending on certain keywords in predefined columns, you can use the below VBA code:

Sub import_csv_and_filter()
    Dim file As FileDialog
    Dim filePath As String
    Dim csv As String
    Dim wsheet As String
    Application.DisplayAlerts = False
    Application.StatusBar = True
    wsheet = ActiveWorkbook.Name
    Set file = Application.FileDialog(msoFileDialogFolderPicker)
    file.Title = "Folder Selection:"
    If file.Show = -1 Then
        filePath = file.SelectedItems(1)
    Else
        Exit Sub
    End If
    If Right(filePath, 1) <> "\" Then filePath = filePath + "\"
    csv = Dir(filePath & "*.csv")
    Do While csv <> ""
        Application.StatusBar = "Converting: " & csv
        Workbooks.Open Filename:=filePath & csv

        ImportExpenses ActiveSheet
        ActiveWorkbook.Close
        Windows(wsheet).Activate
        csv = Dir
    Loop
    Application.StatusBar = False
    Application.DisplayAlerts = True
End Sub

Sub ImportExpenses(ws As Worksheet)
    Dim lastRow As Long
    Dim keyword As String
    Dim categoryColumn As Long
    Dim descriptionColumn As Long
    Dim amountColumn As Long
    Dim dateColumn As Long
    
    ' Define the keywords and corresponding columns
    keyword = "Food" ' Change this keyword as needed
    dateColumn = 1 ' Date column
    descriptionColumn = 2 ' Description column
    amountColumn = 3 ' Amount column
    categoryColumn = 4 ' Category column
    
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    
    For Each cell In ws.Range("B1:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
        If InStr(1, cell.Value, keyword, vbTextCompare) > 0 Then
          
          ws.Cells(lastRow, dateColumn).Value = cell.Offset(0, -1).Value
            ws.Cells(lastRow, descriptionColumn).Value = cell.Value
            ws.Cells(lastRow, amountColumn).Value = cell.Offset(0, 1).Value
            ws.Cells(lastRow, categoryColumn).Value = cell.Offset(0, 2).Value
            
            lastRow = lastRow + 1
        End If
    Next cell
End Sub

In this code, you can locate your CSV file and add predefined columns to import them based on certain keywords. We have added some columns as examples. Do not forget to customize them based on your dataset.

Hope this helps. Let us know your feedback.

Regards,
Yousuf Shovon