** Updated with C# example -- see below **

From the "Did you know (or care)" file, Did you know that you can connect to a comma separated value (csv) file using the OleDb Data Provider?

[Able Consulting, Inc]

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
       "Data Source=c:\somepath\;" & _ 
       "Extended Properties=""text;HDR=Yes;FMT=Delimited"""

Then open a recordset based on a select on the actual file

oRs.Open "Select * From MyTextFile.txt", oConn, _
         adOpenStatic, adLockReadOnly, adCmdText 

This also from one of my favorites [connectionstrings.com]

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties=""text;HDR=Yes;FMT=Delimited""" 
"HDR=Yes;" indicates that the first row contains columnnames, not data
 

 Also see: [Microsoft Knowledge Base Article - 262537]

Here is an example app for those that asked:

using System;
using System.Collections;
using System.Data.OleDb;
using System.Data;


public class MyClass
{
  public static void Main()
  {
    OleDbConnection conn = new OleDbConnection();
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\temp;Extended Properties=\"text;HDR=Yes;FMT=Delimited\"";
    try
    {
      conn.Open();
      OleDbCommand myCommand = conn.CreateCommand();
      string commandString = "select * from test.csv where firstname='John'";
      myCommand.CommandType = CommandType.Text;
      myCommand.CommandText = commandString; 
      OleDbDataReader dataReader = myCommand.ExecuteReader();
      dataReader.Read();
      Console.WriteLine("FirstName: {0} \t LastName: {1}",dataReader["FirstName"],dataReader["LastName"]);
    }
    catch( Exception ex )
    {
      Console.WriteLine(ex.Message);
      Console.WriteLine(ex.StackTrace);
    }  
    finally
    {
      conn.Close();
    }
    Console.WriteLine("all done.");
  }
}
 
 And here is the associated “test.csv” file that goes in “c:\temp”:
 
 FirstName,LastName
 John,Doe
 James,Fox
 Adam,Smith
 
I guess there is supposed to be a schema.ini file that goes in the same directory, but I didn't have it and it worked.  But here it is:
 [test.csv]
 ColNameHeader=True
 Format=CSVDelimited
 MaxScanRows=25
 CharacterSet=ANSI
 Col1=FIRSTNAME Char Width 20
 Col2=LASTNAME Char Width 20
 
Special thanks to [ASPAlliance] for their article on this as well!