Excel macro to choose what and where to import from CSV

Question

I have a .csv file exported from my bank account which is currently being imported to Excel via Excel automated import and then processed with my macro (some column deleting, format changes, concat, etc.. nothing special).

However, the .csv file doesn't have consistent format and there are columns which change their location (e.g. column "IBAN" is sometimes column no. 2, sometimes no.5) or aren't there at all, which causes my macro to crash.

What I need is some code which would work with the .csv first, check the .csv for column "IBAN" and import it after the check, so it would always be let's say column no. 1 and my macro would process column no. 1 without problems.

Any ideas?

Answer 1

The following code uses ADODB to perform an SQL query on the CSV file, thus importing just the columns that you want, in the order in which you want them.

Sub SQL_Extract()
    Dim objConnection           As Object
    Dim objRecordset            As Object
    Dim CSVFilename             As String
    Dim CSVFilepath             As String

    CSVFilename = "myCSVFile.csv"           ' Change to name of your CSV file
    CSVFilepath = "C:\Temp\DownloadFolder\" ' Change to location of CSV file

    ' Set up connections & dataset objects
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")    
    objConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                   "Data Source=" & CSVFilepath & ";" & _
                                   "Extended Properties=""text;HDR=YES;FMT=CSVDelimited"";"
    objConnection.Open

    'Create the SQL command to retrieve information
    'The following assumes that the CSV file contains columns with headings of
    ' "IBAN", "Transaction Date" and "Amount".  (Any other columns in the file
    ' will be ignored.)
    sqlCommand = "SELECT [IBAN], " & _
                 "       [Transaction Date], " & _
                 "       [Amount] " & _
                 "FROM [" & CSVFilename & "]"
    'Execute the query
    objRecordset.Open sqlCommand, objConnection, 3, 3, 1

    If Not objRecordset.EOF Then ' Check whether any records were created by the query
        ' Write out the results of the query
        ' Change "A2" to top left cell of area where you want results written
        Range("A2").CopyFromRecordset objRecordset
    End If

    objRecordset.Close
    objConnection.Close
End Sub

Answer 2

Something like this should work, and is rather uncomplicated. You can use additional arguments with the find function as well to specify where you are searching:

Public Function GetColumnRange(ByVal sSearch As String, r As Object, rSearchArea As Range)
If Not rSearchArea.Find(sSearch, , , xlWhole) Is Nothing Then
    Set r = rSearchArea.Find(sSearch, , , xlWhole)
    r.Select
    GetColumnRange = True
End If

End Function Public Sub CSV_Reformat() Dim wb As Workbook Dim ws As Worksheet

Dim arrArgs() As Variant

Dim cColl As Collection

Dim rHolder As Object

Set cColl = New Collection
arrArgs() = Array("IBAN", "Arg2", "Arg3")

' Use the code you have to load the .CSV file and to open it
' Assumes that wb is set to the .CSV file
' Assumes ws is the first sheet in the .CSV file

Set wb = ActiveWorkbook ' Replace this with your actual .CSV file
Set ws = wb.Sheets(1)

For i = LBound(arrArgs()) To UBound(arrArgs())
    If GetColumnRange(arrArgs(i), rHolder, ws.UsedRange) = True Then
        cColl.Add rHolder
    End If
Next

For i = 1 To cColl.Count
    Set rHolder = cColl(i)

    ' Do whatever you need to do with the range here
    ' For example, you could get the column number:

    Debug.Print rHolder.Column
Next

End Sub

I would also recommend considering using an array for this if your CSV file is larger. You can load the array in using:

Dim arrData() as Variant
Dim i as Long, Dim j as Long
Dim lOutput as Long
Dim bool as Boolean

' Assumes, as before, that ws is the worksheet we are working in  

arrData() = ws.UsedRange.Value

You can then create a new array for output something like this:

Dim arrOut() as Variant

redim arrOut(0 to Ubound(arrData()) - 1, 0 to i) 

' Reduce it by one row since we are creating a zero based array. i is the
' number of columns you want in the output.

' Then loop over the data array and put the right columns into your output

For i = 1 to Ubound(arrData(), 2) ' Loop through the headers in your data
    bool = True
    Select Case arrData(1, i)
        Case "IBAN"
            lOutput = 0 ' Allows you to determine where the data will be put in your array
        Case "Arg2"
            lOutput = 1
        Case "Arg3"
            lOutput = 2
        Case Else
            bool = False
    End Select

    If bool = True Then
        For j = 1 to Ubound(arrData(), 1)
            arrOut(j - 1, lOutput) = arrData(j, i)
        Next
    End If
Next

This should allow you to select certain data from the .CSV file and load it into an array. You can then output your data onto a range as needed. For example

With wsOutput
    Set r = .Range("A1").Resize(Ubound(arrOut(), 1) + 1, Ubound(arrOut(), 2) + 1)
    r.Value = arrOut()
End With