Tech Recommendation

[Dante Hicks]

I have two sets of reports, which are currently run daily.

One is a special phone system report, which produces graphs on how many calls (in a call centre type environment) are taken each day, who by, etc, etc. This program produces a report which can then be printed or saved in .CSV format. The data kinda looks like this (in the CSV file):

"Ext#", "Name", "Date", "CallsTaken", "BreakTime", "WorkTime", "Blah"

The second report logs how many particular tasks a user has done (e.g. repairs raised or whatever). The results of this report can be outputted to Excel and then saved as whatever you like. The report will look like :

John Smith, 21
Tom Brown, 12
etc.

Now, at the moment both reports are printed and then the numbers transferred manually into a spreadsheet which records the daily record. Stupidly inefficient obviously.

What tech would you recommend using to try and streamline this somewhat? Please bare in mind that the people doing this aren't exactly computer experts, so that limits it somewhat. Also a purely windows environment, and there's a limit to what they could do on a daily basis.

Two thoughts I had :

1. Get them to run the reports as normal, save the contents into a set directory. Then have a specific Perl script to parse the sets of data and combine them in one .CSV (or whatever).

2. They do have an internal intranet which I could use to do an .ASP style "battlecalc" (i.e. they simply paste the reports in, and then it puts it into a seperate access file).

[zenopus]

Well, excel will open a csv file.

If you want some automation then I agree, perl is very suited for that.
You can either do the sums in perl or use formulas. Formulas go in in the usual way. Use tab as field separator and give the output file a .xls extension and all you have to do in excel is formatting (or copy the content into an already formatted spreadsheet).

I've done similar things before, the excel spreadsheet version of the stats (posted here) is generated from the published stats using a perl script.

[virogenesis]

what about using xml?

[Dante Hicks]

Originally posted by virogenesis
what about using xml?

Well, I'd need to parse the existing data (i.e. from the reports) into XML and then present the XML somehow - so I'm not sure how that helps my problem.

If this was something from home, I'd simply use PHP to present the data (kinda like Pilkara) using a MySQL db as a backend. However, I don't have access to either PHP or MySQL so I'm looking for the easiest alternative.

And yes, I can use Excel to open up .CSV files, but the key is automation. These people run the reports every day, and a solution which requires too much effort/thought is doomed for failure. The weakness of simply building a Perl parser is (a) I don't know Perl yet, (b) parsers can be easy to break, and I'm not sure how I could make it "user friendly" or adjustable.

[zenopus]

Originally posted by Dante Hicks
... I can use Excel to open up .CSV files, but the key is automation. These people run the reports every day, and a solution which requires too much effort/thought is doomed for failure. The weakness of simply building a Perl parser is (a) I don't know Perl yet, (b) parsers can be easy to break, and I'm not sure how I could make it "user friendly" or adjustable.

Well, I learned perl while doing a project for a friend.

Excertp from the "Manual"
Put the log file to be processed in <dir>, make sure it has a .<ext> extension.
Double-click the parse.pl script.
The script asks for the name of the log file (the name includes the extension).
Default value is the most recent file with a .<ext> extension that has not yet been processed.

etc.

Apparently the script is now in production...

[CjC]

Originally posted by Dante Hicks
And yes, I can use Excel to open up .CSV files, but the key is automation.

In that case wouldnt an Excel Macro make sense?

[Dante Hicks]

Originally posted by CjC
In that case wouldnt an Excel Macro make sense?

Is that likely to have a lot of flexibility? I've never used Excel macros for anything beyond ridiculously menial tasks.

This would need to handle changes in personnel, plus other random changes.

[Gayle29uk]

Originally posted by Dante Hicks
Is that likely to have a lot of flexibility? I've never used Excel macros for anything beyond ridiculously menial tasks.

This would need to handle changes in personnel, plus other random changes.

You can do some fairly serious stuff with VBA these days

[MT]

http://search.cpan.org/author/ALANCI...SV-0.01/CSV.pm

and Robert is your mothers brother.

[Not_RIT]

The easiest and most sensible would be a VBA script to parse the .CSV files and input them into the spreadsheet.

[MT]

Originally posted by Not_RIT
The easiest and most sensible would be a VBA script to parse the .CSV files and input them into the spreadsheet.

in some alternate universe where easy means hard and hard means easy?