The Current Process:
- Users (from various locations) ftp files containing financial data to a central repository in Tab delimited files
- Someone (by hand) opens these files one at a time and Copy/Pastes them into Excel.
- Final combined file is saved out to One Big Frickin TAB file(tm)
- Two tables are created prior from a Duns & Bradstreet SAM dB export into MS Access
- Using queries as filters, said TAB file is loaded into Access, filtered and exported back out
- Final file is uploaded to the SAM database
- Rinse-Repeat, once a week. Process takes 3-7 hours from start to finish
WHAT?!? Who the hell thought that one up?!?
My solution? - Write 200 Lines of code (exactly, as a matter of fact) that:
- Traverses the Dir tree (File::Find)
- Validates each entry from the files against Enterprise SQL server tables (Automated / Scheduled Import into SQL tables from SAM)
- Creates proper upload file and updates an 'unknown' table for unknown entries for user validation via a web based interface
- One button click, or automated scheduled process (still in debate)
- Total Time from start to upload - 34 seconds to 2 minutes, depending on number of files and file size.
I can't stand inefficiency...It's chaps my ass.
C-.
