Search code examples
sqlscriptingvbscriptadodbjet

Using JET OLEDB Provider, How to query headerless text file using a WHERE clause?

This may be a silly question, but I can't figure out the correct syntax or if this is even possible.

I have a text file that I am querying using the Microsoft.Jet.OLEDB.4.0 Provider. The text file consists is a Headerless CSV that looks somewhat like this:

4/11/2012 9:20:19 AM,USERID,GROUPNAME
4/12/2012 9:20:19 AM,USERID2,GROUPNAME3
4/13/2012 10:45:15 AM,USERID3,GROUPNAME2

I can query the entire text file with a SELECT * FROM... but I'd like to be able to query it on one of the columns. SELECT * FROM myTextFile.txt WHERE column2='USERID'

My problem is, how can I specify the column when there is no header? Is there any way to specify the second column? Something like WHERE {1}='USERID'

Thanks for the responses in advance!

Solution

  • By default the fields are named F1, F2, F3, etc.

    So in your case you can make the SQL string look like this:

    SELECT * FROM myTextFile.txt WHERE F2='USERID'

    [EDIT] I just saw the comment from @onedaywhen...He is correct, I have used this and it works.