Ado Csv Provider Help

[Surjer]

What is wrong with this...

Dim conn As New ADODB.Connection
        With conn
            .Provider = ("Driver={Microsoft Text Driver (*.txt;*.csv)};DBQ=" & fName)
            .Open()
        End With

 

[FoFa]

I found this reference, which stated, must be entered EXACTLY for it to work:
DRIVER={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\Data\MyDir;

Don't know if it will help or not

 

[Surjer]

Cant get that to work either - I can get the jet provider to work but not this csv file provider....

 

[Pat Hartman]

Link a file of that type manually. Then open the MSysObjects table (unhide it if necessary) and copy the connection string for the file you just linked.

 

[Surjer]

Thanks Pat
I went a really weird route with this one lol - Used ADO.NET to get the data and then I populated a virtual recordset with the contents of the data reader..

Dim vPath As String = System.IO.Path.GetPathRoot(fName)
        Dim vFile As String = System.IO.Path.GetFileName(fName)

        Dim conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & vPath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited""")
        conn.Open()
        Dim myCommand As OleDbCommand = conn.CreateCommand()
        Dim commandString As String = "select * from " & vFile
        myCommand.CommandType = CommandType.Text
        myCommand.CommandText = commandString

        Dim dataReader As OleDbDataReader = myCommand.ExecuteReader()

        While dataReader.Read()
            rs.AddNew()
            rs(0).Value = dataReader.GetDateTime(dataReader.GetOrdinal("Date"))
            rs(1).Value = dataReader.GetDateTime(dataReader.GetOrdinal("Time"))
            rs(2).Value = dataReader.GetString(dataReader.GetOrdinal("Feature"))
            rs(3).Value = dataReader.GetString(dataReader.GetOrdinal("Crew ID"))
            rs(4).Value = dataReader.GetString(dataReader.GetOrdinal("Event"))
            rs.Update()
        End While

 

[Pat Hartman]

Date and Time are poor choices for names because they are function names. You are quite likely to run into strange problems when using them in VBA. It is poor practice to use embedded spaces or special characters in field names and you should also avoid property names such as "Name". There are lists of reserved words if you look in help.

 

[Surjer]

Pat, I know - Its an exported csv file poor choice of field names from the developers that created the CSV file...