Have you experienced to create an MS Excel reports wherein the source of data is a .CSV file that is extracted from an Oracle system? If yes, then maybe you have also experienced the situation that I have been through.
A few months ago, I closed a contract to develop Purchase Order (PO) performance reports and Sales Order (SO) performance reports with one of my old clients. They looked very simple at first because there was no need for me to create data entry modules for the program. The source of the data would be coming from their Oracle system. Records would be entered from different countries across Asia Pacific and stored them into one central database server. Weekly, they would extract the records from it and convert it to Comma Separated file or .CSV file. My Macro program would import those contents of that .CSV file to my worksheets and somehow needed to consolidate them for reports and analysis purposes.
At first, everything went smoothly. One day, the project liaison called and told me that there have been errors in almost all the reports. She sent me a copy of the macro file and source data file to check on the problems. As I was checking the reports of the Excel file one-by-one, reports were devastated. I investigated for hours all the macro modules but I hadn’t seen any clue of what happened to them. So, I asked myself; “How come a program in a good running condition suddenly gives incorrect figures in almost all reports?” The answer is actually right under my nose. When I checked the records in details, I saw the null values of all the Date Fields like “Actual Shipment Date”, etc. were changed. Originally, if the null date field was converted to .CSV file, the content was “NULL”. It was changed to dash (“-“) and eventually would give incorrect values in reports. To correct the problem, I spent days to modify the program modules and worksheet cell formulas that are related to all the date fields. Whew! It was tedious.
That was a learning experience for me. I didn’t see that coming and hadn’t prepared for it. Partly, it was my fault because I presumed that the records format was final and didn’t realize that the data-entry program and data-conversion program were not mine and it could be changed anytime.
For that particular reason, I requested the project liaison to collaborate with their Oracle programmer to advice us if records structures were altered.
| Click here to subscribe and receive Pro-business Excel VBA Programming tips. |
0 comments:
Post a Comment