|Problems? Is your data what you think it is?|
The largest single piece of code that I have written takes the back end database files from our Avaya CMS server, renames them, inserts them to .xls files, and writes them to a database.The first part involves the renaming the files from their default name of chrXXX.XXX where the X's are numbers. One of our remote reporting teams likes to receive them as csv files, so I simply open the directory and copy the files to a directory with the extension csv.
The second part was a bit more difficult, I have been using the Spreadsheet::WriteExcel::Big CPAN module to write the files to an excel spreadsheet (for one manager that only uses MS Excel) containing all of the call records each day. I had some difficulties, but solved them by chopping the files at about 14000 lines.
The last piece involved writing the daily call records to a MS SQL database, then a MYSQL database for our local reporting team to generate reports from.
My main strategies involved trying to go out of my way to get everyone and what format they wanted and to help out local reporting guy who was spending all his time doing this task manually. As for the modules, I found Spreadsheet::WriteExcel::Big to be very good.
My biggest mistake was using MS SQl as a db the first time. The tables csv files have 55 columns, somewhere between 20,000 and 30,000 records each day. I had a terrible time with the DBI, each record was taking around 4 second to write. Finally I wised up and was able to write the all the records in just a couple of hours each day to the MYSQL db with the exact same piece of code, go figure.