Is it possible to create a VBA QueryTable outer join between a CSV file and a worksheet?

Question

I'm creating an excel workbook to manage my personal finances. My banks provide transaction data in CSV format and I found a way to import that data into excel using a QueryTable (using a "TEXT" connection.)

I'd like to automatically apply transaction category rules to each imported transaction. I have a worksheet with two columns - a string to match against the transaction "details" provided in my bank's CSV file and the category to apply to the matching transactions.

Is it possible to create an outer join between the CSV data and the categories worksheet and dump the resulting table into another worksheet?

For example (SQL pseudocodeish): SELECT csv.date, csv.details, csv.debit, csv.credit, ws.category FROM [csvfile] csv LEFT OUTER JOIN [worksheet] ws ON csv.details ~= ws.details

~= above would be some kind of string match. I can figure out the SQL, my question is really how to combine the CSV file and worksheet in the same QueryTable.

Fionnuala Fionnuala · Accepted Answer · 2011-02-11T20:48:12

Excel will open CSV files without blinking, but you can use a connection string, if you prefer. It is even possible to write a query that compares an existing worksheet or named range with a text file using an Excel connection. All you need is a little VBA.

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

'Note HDR=Yes, that is, first row contains field names '
'and FMT delimted, ie CSV '
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Docs\;" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"

cn.open strcon

'You would not need delimiters ('') if last field is numeric: '    
strSQL="SELECT FieldName1, FieldName2 FROM The.csv " _
& " WHERE LastFieldName='SomeTextValue'"

rs.Open strSQL, cn
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

You can use any suitable Jet SQL queries against the connection, just be careful about case sensitivity. For example, working with a connection to the current workbook:

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open strCon

strSQL = "SELECT * " _
       & "FROM [Sheet1$] a " _
       & "LEFT JOIN [Text;FMT=Delimited;HDR=Yes;" _
       & "DATABASE=C:\Docs].Import.txt b " _
       & "ON a.[Id]=b.[Id] "

rs.Open strSQL, cn, 3, 3


''Pick a suitable empty worksheet for the results

Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Is it possible to create a VBA QueryTable outer join between a CSV file and a worksheet?

3 Answers