Using csv files with ado

I posted a question on how to do this a while back but never quite got the answer I was looking for (although I do appreciate the responses I got).

Anyway, found this connection string on a site somewhere, tested it out, and it seems to work.

You can use the ODBC text driver to connect to a flat file via ADO.

Here's how:
cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=\somepath\;" & _
"Extensions=,asc,csv,tab,txt;" & _
"Persist Security Info=False"

where cn is an ADO connection object and the Dbq parameter is the path to the directory where your flat files are stored.

You specify which file you want to access in the SQL statement, ie.

sql = "SELECT * FROM myfile.txt"

*note: I haven't been able to get this to work yet with csv files that don't have a file extension.

Also, if you're interested in how to set up formats other than comma seperated, print out the connection string. ADO or ODBC seems to fill in a lot of default values which may guide you in how to configure different formats. (I haven't looked into this yet.)