INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

Use ODBC to read CSV text files with SQL by taupirho
Posted: 29 Jun 04

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


Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close