I have a network of ~80 users from 12 departments and 14 printers, some of which are colour.
Each month I generate a report in CSV format from my print charger application.
The report has column headings.
The report includes all print jobs to all printers on the network and it is sorted alphabetically by user name where the user
name format is lastname.firstname
I wish to create a summary of the report that indicates which users printed to the colour printers.
I presently 'massage' the CSV file to a point where I can use the subtotals feature of excel to yield the report I need.
By 'massage' I mean,
-there are several column fields that I don't need so I delete them
-I sort by printer and delete all jobs to all BW printers
-there is an 'attributes' field and I delete all 'failed' and all 'gray' print jobs NB: The 'attributes' field may indicate
null (blank) or 'colour' for a successful colour print job
-I sum the 'pages' each user printed then multiply by $0.50 to yield a cost per user.
The report does not include the user's department so I use a vlookup table to cross-reference the department then I sort by
department, then by user with user and department totals.
This report takes a long time (many hours) and I believe I can use a script or an Access query on the CSV file to achieve the
same outcome in the blink of an eye.
15 years ago I wrote code using 'Clipper'. Presently I tinker with the odd VB script. I understand the 'logic' of what I want
to achieve, but I do not know the beginnings of extracting the information from my CSV file.
The CSV file is consistent in that if a field is blank, there is simply another comma delineating the next field. (I have
seen cases where this is not the case in so-called CSV files, eg. online phone-calls-made statements)
Essentially, what I think I need to learn is
1.How to 'read' the header row and how to relate the header to the rows below it.
2.How to extract the user data that pertains to successful colour print jobs and sum the number of pages printed
3.How to corelate this parsed data with the user-department table and print it in a report.
I might have to produce the February report the old way but I am sure I will achieve my goal of automating the process by my
sheer bloodyminded perseverence. If you can help me, I'd be very appreciative.