Count records in CSV File

[timk5000]

Hi
I want to scan a large csv (3000+line)file and just count the number of lines that meat certain criteria.

18,08/24/14,05:02:00,5056
14,08/25/14,07:05:00,5046
18,08/24/14,05:02:00,5056
12,08/25/14,13:06:00,5076
18,08/24/14,05:02:00,5056
14,08/25/14,05:07:00,5054

For instance I may need to know the number of entries that have 18 as the first column and have a date of 8/24/14 and a time between 5:00:00 and 7:00:00

I have been able to piece together a app that reads in the file and puts the lines in a list box but have not been able to separate the columns and filter them.

any help would be great
Thanks

[Hiroshe]

I have been able to piece together a app that reads in the file and puts the lines in a list box but have not been able to separate the columns and filter them.

You're going in the right direction. After you read in a line as a string, you'll need to split the string wherever a "," appears. Look through the string manipulation functions that vb provides to peice it togeather.

[Reverend Jim]

You can open the CSV as a text based database using ADO then use standard SQL statements to query to records. A detailed explanation can be found here. Here is a short example.

Assuming your CSV file does not have a header line (identifying field names) you can create a Schema.ini file in the same folder as the CSV file. The file would look something like

[db.csv]
Format=CSVDelimited
Col1=F1 Short
Col2=F2 Text
Col3=F3 Text
Col4=F4 Long

Replace the string db.csv with the actual file name (without the path). You can also modify the lines identifying the field names and types as you see fit (see the full documentation in the above link). Here is a short vbScript (9easily translatable to VB6) with a SQL query. I put the Schema.ini and db.csv files in my D:\Temp folder.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set con = CreateObject("ADODB.Connection")
Set rec = CreateObject("ADODB.Recordset")

'open the connection 

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp;Extended Properties=""text;HDR=NO;FMT=Delimited"""

'Get the number of records where field 1 (first field) is 18

qry = "SELECT COUNT(*) FROM db.csv WHERE F1 = 18"
rec.Open qry, con, adOpenStatic, adLockOptimistic, adCmdText

Wscript.Echo rec(0)

rec.Close
con.Close

To step through all the records with 18 as the first field

qry = "SELECT * FROM db.csv WHERE F1 = 18"
rec.Open qry, con, adOpenStatic, adLockOptimistic, adCmdText

Do Until rec.EOF
    Wscript.Echo rec("F1"), rec("F2"), rec("F3"), rec("F4")
    rec.MoveNext
Loop

rec.Close
con.Close

If the file has a header line then you can replace HDR=NO with HDR=YES in the connection string, delete the Schema.ini file and use the field names in the header line.