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.
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