Query records from a csv file using ODBC 1

[mnolting]

I am attempting to read records from a csv file using ODBC with the following code:
'-------------------------------------------------------
Public Function ChkAgent(Fname, Lname As String) As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("", True, True, Connect:="DSN=Agents2;")
Set rs = dbs.OpenRecordset("select [first name],[last name] from people.csv where ucase([first name]) =ucase('Jessica') and ucase([last name])=ucase('smith');")
ChkAgent = rst.RecordCount
Set rs = Nothing
Set db = Nothing
End Function
'-------------------------------------------------------
I get an error "You can not use ODBC to import records from Microsoft Jet or ISAM database.........."

The code works for a DSN that is a SQL Lite database.
How can I query a csv file using a text drive for ODBC??

Thanks
Mike

 

[ettienne]

http://support.microsoft.com/kb/149090

 

[keenanbr]

Setup ODBC (CSV in my case), Driver = Microsoft Text Driver(*.txt, *.csv)


Public m_adoCsv As New ADODB.Connection
Public m_adoRst As New ADODB.Recordset


Public Sub Command1_Click()
Dim FileNo As Integer
Dim strSQL As String
Dim i As Integer

m_adoCsv.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=CSV;Initial Catalog=C:\Temp\"
m_adoCsv.Open

strSQL = "SELECT * FROM MyRegData.csv"
m_adoRst.Open strSQL, m_adoCsv
While Not m_adoRst.EOF


Debug.Print m_adoRst.Fields(0)

m_adoRst.MoveNext
Wend

m_adoCsv.Close


End Sub

 

[SkipVought]

http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

[b]My path & filename[/b] below...
Sub adot()
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    Set rst = New ADODB.Recordset
    
    sConn = "Driver={Microsoft Text Driver (*.txt; *.csv)};"
    sConn = sConn & "Dbq=[b]C:\Documents and Settings\ii36250\My Documents\dbText\[/b];"
    sConn = sConn & "Extensions=asc,csv,tab,txt;"

    cnn.Open sConn
    
    sSQL = "Select * From [b]Test.txt[/b]"
    
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    rst.MoveFirst

    ThisDocument.Range = rst(0)
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...and yes, you need a reference to Microsoft ActiveX Data Objects m.n Library

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mnolting]

Skip,
I encounter an error on the second statement (Dim rst As ADODB.Recordset) "user defind type not defined"

???
Mike

 

[SkipVought]

...and yes, you need a reference to Microsoft ActiveX Data Objects m.n Library

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mnolting]

Keenanbr,
I encounter an error on the line "m_adoCsv.ConnectionString = "Provider=MSDASQL...........
"Object required"


?????
Mike

 

[mnolting]

My file is C:\Temp\People2.csv The first row is the field names (as done in a lot of csv files)

I need to query this "Table"

Mike

 

[mnolting]

Skip,
I made a reference to "Microsoft Active X Dat Objects 2.8 Library".
Now I get an error on "cnn.Open sConn" My file is C:\Temp\People2.csv. I changed the following to:
sConn = sConn & "Dbq=C:\temp\people2.csv;"

Is this right?
Mike

 

[mnolting]

keenanbr,
After making the MS active x ref I now get an error on "m_adoCsv.Open" "Data source not found..."
Mike

 

[SkipVought]

sConn = sConn & "Dbq=C:\temp\"

sSQL = "Select * From people2.csv"


[code]


Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[mnolting]

keenanbr,

Your example works!
Although I had to make changes:

This:
Public m_adoCsv As New ADODB.Connection
Public m_adoRst As New ADODB.Recordset

To:
Dim m_adoCsv As New ADODB.Connection
Dim m_adoRst As New ADODB.Recordset

This:
m_adoCsv.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=CSV;Initial Catalog=C:\Temp\"
To:
m_adoCsv.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=AGENTS (MY dsn!);Initial Catalog=C:\Temp\"

And, I did not know that I needed a reference to MS Active X 2.8 Lib for ADO to work.

Thanks Very Much
Mike

What is the reference to C:\Temp?? Thats where my test file is.

 

[mnolting]

I spoke too soon,
I changed the query to "Select * from people2.csv" to ... where fname='bob'" and I get an error "Too few parameters. Expected 1"

Now What??

Mike

 

[SkipVought]

Means that fname is not a valid field name in your table.

I assume that you meant...

sSQL =  "Select * from people2.csv where fname='bob'"

YES? SPACES and all?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mnolting]

Skip,
I copied and pasted the query---"Too few parameters. Expected 1"
Mike

 

[mnolting]

Skip,
"fname" is a valid field name as seen in the catalog function of WinSQL and in the first line of the file.
Is there a different way to specify a field?
Mike

 

[PHV]

Have a look here:

http://www.connectionstrings.com/?carrier=textfile

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mnolting]

All,
MY BAD!

I was looking at the wrong copy of a modified csv file.

The test was to a file where the actual name of the field is "First name" not "fname".

Thanks very much for the help.
Mike

 

[SkipVought]

...
where [First name]='bob'

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]