Use ODBC to read CSV text files with SQL

Using ODBC it's possible to read comma separated value(CSV) text files using SQL just as if the data was contained in a relational database. Before doing this though you have to set up a text driver data source name (DSN).Once you have your DSN set up it is simply a matter of making the connection , reading the data via SQL and closing the connection. The data that is read is kept in what's called a record set that can be traversed beginning at record 1 using a simple loop. The following example assumes we have two CSV text files, employee and salary, as shown below.

Employee.txt

Name, ID, Dept
Tom,1234,accounts
Dick,4567,sales
Harry,99,IT

Salary.txt

ID,salary
1234,$20000
4567,$30000
99,$50000

If the above were tables in a relational database and you wanted to retrieve each employee name together with his salary you would use a SQL statement like the one below.

Select name,salary
From employee e,salary s
Where e.id = s.id

Here's what the VB/VBA code would look like, you would replace the dsn=Text Files string with your own DSN name:-

Private sub select_data()

Set db_data = New Connection
db_data.CursorLocation = adUseClient

' set up DSNless connection
db_data.Open "PROVIDER=MSDASQL;dsn=Text Files;uid=;pwd=;database=;"

Set ado_data = New Recordset

' Note exactly the same select statement as would be used in a relational database
'
ado_data.Open "select name, salary from employee.csv e, salary.csv s " & _
"where e.id=s.id" , adOpenStatic, adLockOptimistic

If ado_data.RecordCount < 1 Then
MsgBox "No data found"
Exit Sub
End If

' go to the first record in the set and loop around till no more available
'
Ado_data.MoveFirst
For i = 0 To ado_data.RecordCount - 1
MsgBox (ado_data.Fields.Item(0).Value & " " & ado_data.Fields.Item(1).Value)
Ado_data.MoveNext
Next

Ado_data.Close
Db_data.Close

End sub