Assistance with CSV import and manipulation

jmann1

Greetings!

I am trying to create a print auditing app that will display how much each user is printing on a daily basis.

I am using Novell iPrint for auditing. The audit report is in .csv format.
The trouble I am having is every print job is logged individually. See below.

USER_1,5,54022
USER_1,1,54506
USER_1,24405
USER_1,1,18943
USER_1,1,12947
USER_1,3,12947
USER_1,1,76747
USER_1,1,18943

USER_2,4,456130

USER_3,1,99425
USER_3,1,98308
USER_3,1,170148
USER_3,3,129331
USER_4,1,283553
USER_4,1,430808

The first field represents username, the second field represents page count for that job, the third field represents total bytes for that job.

I want to combine each user into a single row and then sum the page count and byte count for that user.
For example:

USER_1,15,273460

USER_2,4,456130

USER_3,8,1211573

I plan to store this info in a mySQL database and overwrite the page count and byte counts every time the audit is run.

Anyone have any ideas how to do this?

Thanks!

paulnaj

Hi, what have you got so far?

jmann1

Regretfully, nothing.

I can manage to get the file opened, but I have no idea how to go about the rest. I could do all this manually with excel macros before sending it to the webserver, but I simply don't have time.

paulnaj

You might be lucky and get someone to wite it for you, but the usual thing is for you to show your code and get people to help you fix it!!

jmann1

I'm not asking for anyone to write it for me. I'm simply asking for a gentle nudge in the right direction. I've only been playing with PHP for a few months and I'm unfamiliar with everyting except the basics.

paulnaj

Fair enough, but still, show us how far you've got.

Seeing that you were able to open the file, try just echoing out each line of the file to the screen to start with. There are plenty of examples of how to do this in the manual under file input/output.

Once you can do this, we can see about storing the data in variables as you've described.

P 🙂

jmann1

Ok. Here's what I have so far. File is opened and dumping everything in it.

<?php

$audit_file = fopen('vol1:ndps/4891E9F4.PSM/audit/audit.csv','r');
echo "<table border='1' cellpadding='2' cellspacing='1' width='100%' style='font-family: Arial; font-size: 14'>\n";
while($line = fgetcsv($audit_file,1024)) {
echo "<tr>";
for ($i = 0, $j = count($line); $i < $j; $i++) {
echo "<td nowrap>".$line[$i].'</td>';
}
echo "</tr>\n";
}
echo "</table>\n";
fclose($audit_file);

jmann1

I should mention that there are several columns in this csv that I would like to omit completely. All I want to display is the username, total page count, and total byte count.
Can these manipulations be done before mySQL entry?

paulnaj

can these manipulations be done before mySQL entry?

Yes, first I'd try is to use the explode() function to split each line into an array of comma-separated values. Column 1 will index 0, column 2 will index 1, etc. so you need to know which indices to use to pick out just the values you want.

To keep a record of what's going on over the course of the loop:

create two associative arrays before the loop starts. One for pages, one for bytes. Keys for both will be the user ids, the values will be the running sum of the pages and bytes, resectively.
in each iteration of the loop, check whether or not the key (user id) has already occurred. If it has, add the current page/byte number to the running sum.
If not, create the new entries in the assoc. arrays and set the initial values to the current page/byte number.
At the end, you'll have two assoc. arrays, with totals of pages/bytes.

See how far you get.

jmann1

Easy for you to say..

paulnaj

Doesn't sound like you want my help, mate.

jmann1

If I didn't want help I wouldn't be here. I'm researching your reply right now. I have never used any of these functions before, so what you said may as well be written in klingon.

paulnaj

Well Klingon or not, you still have to know what these things are.

As I said, see how you get on and I'll try and help - but please, cut the comments - I don't need them.

Paul

PS. you won't actually need the explode() function here since fgetcsv() does that bit for you.