asked on

Using ADO to open a CSV File...

Hi Experts,

I am trying to access a CSV File using ADO and having some trouble.

The connection string that i'm using is as follows:

Set con = New ADODB.Connection
con.ConnectionString = _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DefaultDir=" & txtDir.Text
con.Open

SQLStr = "Select * FROM " & txtFile.Text & " WHERE 1 = '2006/11/15'"

The format of the file is as follows and has over 65535 lines (currently at 4Mb and growing on a daily basis):

2006/05/25;08:17:37;0;1;0;0;1
2006/05/25;08:17:40;0;1;0;0;1
2006/05/25;08:17:44;0;1;0;0;1
2006/05/25;08:18:26;0;1;0;0;0
2006/05/25;08:18:42;0;1;0;0;0

I would like to be able to search for results using the first two fields using SQL statements. Please note however that the CSV file does not have a header (is this a problem??).

Any sample code for this would be appreciated.

Regards,
robbrownuk

rockiroads

Have u considered linking this csv file into MS Access (linked table, not import) then running access queries?
or import into another db?

Not sure what default fields are, is it F1, F2, F3 ? try that

ASKER CERTIFIED SOLUTION

leonstryker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

leonstryker

You could also use the Jet driver, which not require you to use a schema.ini file:

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJetText

Leon

robbrownuk

ASKER

Thanks leonstryker,

Your second suggestion seems to be a step in the right direction, However it only works if i use a comma instead of the semicolon.

Also I cant seem to filter the records for a specific date. I have the following code which returns no results:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & PathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

Dim FilterDate As Date
FilterDate = CDate("24/05/2006")

rs.Open "select * from log2.txt WHERE 0 = " & FilterDate, _
cn, adOpenStatic, adLockReadOnly, adCmdText

'Make sure we have some records to display
If rs.EOF Then GoTo NoData

MsgBox rs(0)

NoData:
rs.Close
cn.Close

Anymore suggestions??

Thanks,
robbrownuk

leonstryker

Well, this:

WHERE 0 = " & FilterDate

is guarantied to return you nothing, since FilterDate is never going to be 0

>it only works if i use a comma instead of the semicolon.

Right, because CSV implies that it is a Comma Seperated Values. In your case its just a text file with CVS extension.

>HDR=YES

In you case the HDR should be NO, since you have no header row.

Leon

robbrownuk

ASKER

> WHERE 0 = " & FilterDate

> is guarantied to return you nothing, since FilterDate is never going to be 0

I was under the impression that "0" represents field 0 (the date). So, how do you run queries on a CSV without any headers (i.e. how do you reference the data columns)?

---
robbrownuk

robbrownuk

ASKER

Thanks again leonstryker,

You pointed me in the right direction with your first post, so the points are yours...

The "Microsoft Text Driver" with the schema.ini file work very well. For info, here is what my schema.ini file contains:-

============================
[log.txt]
ColNameHeader=False
Format=Delimited(;)
MaxScanRows=0
CharacterSet=OEM
Col1=F1 Char Width 10
Col2=F2 Char Width 8
Col3=F3 Char Width 1
Col4=F4 Char Width 1
Col5=F5 Char Width 1
Col6=F6 Char Width 1
Col7=F7 Char Width 1
============================

Thanks!
robbrownuk