2. PHPRunner General questions
  3. Data Import
This topic is locked

Data Import

 		5/10/2007 4:37:58 AM
PHPRunner General questions
T
 thesofa author

Hi

I use PaperCut Print Logger on my servers here to record all the print jobs going through the servers. This allows me to bill each department for their printing costs.

One of the features of this rather excellent program is the daily or monthly logs can be exported into csv or Excel format

I would like to build a set of pages allowing staff to see how much printing has been done by departments or even by individual users.

To do this I need to be able to draw the data from the print logger, either as a csv file or as an excel file, automatically on demand.

This then needs to be pulled into MYSQL tables automatically, the data then has to have some simple calculations performed on it and the reports or charts will be produced by PHPRunner.

Is there a way to do this?
J
 		Jane 5/10/2007

Hi,
you need to use CSV to MySQL importing tool to convert your cvs files to the MySQL database.

We plan to add this feature to one of the next PHPRunner version.
X
 		xliner 5/10/2007

Think, the content of the link:
http://www.webyog.com/en/sqlyog_migration_toolkit.php
is doing of what you are looking for, here the "time intervalled automated" conversion of CSV data formatted files seen within the PaperCutPrintLogger subdirectories into MySQL file format while the needed calculation things could most probably rounded out a PHPR custom code snippet, e.g. see under http://www.xlinesoft.com/articles/amount_to_single_field.htm
<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=17790&image=1&table=forumreplies' class='bbc_emoticon' alt='B)' /> HTH and stay tuned ..., Hajo
kujox 5/12/2007

This will take the file called $filename and import a csv into the mysql called TABLENAME, it does expect it to be on the server but you can do this with the PHPr functions

$handle = fopen("$filename", "r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

$sql = "INSERT INTO TABLENAME VALUES ('','". implode("','", $data) ."')";

mysql_query($sql) or die(mysql_error());

}

fclose($handle);
X
 		xliner 5/12/2007

Kujox,
many thx 4 the code snippet (have a static CSV file issue to work on ...), yet, any further idea on how to solve the "real time" request raised by "thesofa"... ??? I understand, running the code snippet out of PHPr would pile up "tons of duplicate records" once used on several occasions, so, a workaround could be to run things only at the end the month on the targeted "staff information point of sale" printing cost transparency issue.
kujox 5/12/2007

Without knowing a bit more about print logger software I can't say how to solve in real time unless you can schedule a task in the print logger software and then get the PHPr app to upload any changed logs from the csv file.
X
 		xliner 5/12/2007

Kujox,
PaperCut Print Logger is a simple server based tool, generating DAILY and MONTHLY log files, seen within two separate subdirectories..., no more program options available, as far as I could see, further I do not know whether:
http://www.papercut.biz/products/chargeback/
could be the right "available ex stock" product for "thesofa" anyway..., finally, a small headline on top of the logged data, namely "PaperCut Print Logger - http://papercut.biz", would be needed to be manually trashed first prior any further data treatment...
kujox 5/12/2007

Hajo
This is how I get rid of any header lines when the data is direct from an excel file with headers, the only thing to watch out for is if you import currency from an excel file make sure the cell format does not the the comma to denote thousands

$header_line = 0;# this is the line that counts number of header lines

$handle = fopen("$filename", "r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

$sql = "INSERT INTO TABLENAME VALUES ('','". implode("','", $data) ."')";
# check if we have reached the data

if ($header_line == 0) {

$header_line = 1;

} else {

mysql_query($sql) or die(mysql_error());

}
}

fclose($handle);
T
 		thesofa author 5/12/2007

phew, thanks guys, plenty for me to work on there.

The "posh" or paid for version of print logger may be able to schedule a task, sadly paying for it is not an option.

If i sort an answer, I will get back to you all.

Cheers
X
 		xliner 5/13/2007

Okay..., all well understood, do your homework now..., if it finally runs out to be a total mess, I am signalling (as experienced user of the antic Symantec Q&A integrated software package) to own some pretty good CSV import related programming knowledge which is serving up to 500 "", along with the option to DELETE duplicates in breaks of seconds, in order to honour KUJOX´s lines, I am listing the field names of the log file to enable him to maybe contribute with another sophitiscated solution here.
quote
"PaperCut Print Logger - http://www.papercut.biz/"

"Time"

"User"

"Pages"

"Copies"

"Printer"

"Document Name"

[b]"[color=#FF0000]Client"

"Paper Size"

"Language"

"Height"

"Width"

"Duplex"

"Grayscale"

"Size"