A few weeks ago, I wrote a quick article on how to use TextPad to make vertical selections. From the comments I received, transforming raw data seems to be a major preoccupation shared by many. Several years ago I created an Excel Macro (a set of instructions to perform some task in Excel) to traverse a speadsheet, validate its data, and transform it into a certain format. Recently I had to dig it up to clean up some mainframe files. Today I modified it and applied it to the Xactly Order Upload Template to demonstrate how macros can be used.
First, download my order upload staging spreadsheet. When you open it, you will see something like the following image plus several columns. The data is intentionally incorrect.
After executing my Macro, the spreadsheet will look like this:
The macro validates one row at the time. When there are anomalies, the cell is highlighted in red. The content of some "incorrect" cells are originally in bold for you to look more closely at what will happen. You will also notice that some data will be corrected.
Here are the actions performed by the macro for each row:
- First, position the active cell to the first row and check if the mandatory Order Code is present. If this is the case, if it is in the proper format (2 upper-case letters followed by 3 digits in this example).
- Check if the Item Code is an alphanumerical value (only digits and letters allowed)
- Verify that the "Quantity" field is not null.
- Check to see if the "Amount" field only contains an amount (digits, "-" and ".") - I could write something more complex to make sure I don't get records like: "22-..2".
- Verify that the Unit type is allowed. I hardcoded a check for "USD".
- Check that the incentive date is actually in an Excel date format. Remember that it needs to be setup as mm/dd/yyyy for the Xactly upload.
- Verify that the split amount sums up to 100%. I also make sure that if a 2nd split amount is entered, that the associated second employee ID is entered.
- Lookup the "Amounts" column again, verify if there are numbers with the minus sign at the end of the number (2.0-), and transform it into the proper format (-2.0). I often get this problem when importing data from raw text files.
- Finally, verify that the incentive date is unique for every row.
All these steps will only take a few seconds to execute for several hundred rows.
How to Run my Macro
- Download this spreadsheet to your computer
- Click on Tools->Macros->Macro (Alt-F8 to be quicker)
- Select "editFile" (that's the name of my macro) and click on "Run"
- Tada! Your errors should now be highlighted in red.
- Correct the errors, remove the red-fill color and run the macro again if you want to verify your corrections.
How to Modify the Macro
If you are thinking that this spreadsheet is useless for your situation, you are right. The odds for your Order Codes to be exactly in the format xx999 are pretty slim. That's why I commented the code and made it as clear as possible.
To view the Macro, you only need to click on Tools->Macros->Visual Basic Editor. You will be able to see what code does what by reading my comments. You should be able to easily modify it, even without deep technical skills, and try out your changes. You just need to edit the code, save it, click on your spreadsheet, and run the new macro (Alt-F8). If you break it, you can re-download it.
In a few cases I use regular expressions (regex), which can be a bit complicated and tricky. Fortunatly they are popular and you may be able to find one online that does exactly what you want. If you are curious and want to learn more about regex, this website is a good place to get started.
If you are thinking that this spreadsheet is useless for your situation, you are right. The odds for your Order Codes to be exactly in the format xx999 are pretty slim. That's why I commented the code and made it as clear as possible.
To view the Macro, you only need to click on Tools->Macros->Visual Basic Editor. You will be able to see what code does what by reading my comments. You should be able to easily modify it, even without deep technical skills, and try out your changes. You just need to edit the code, save it, click on your spreadsheet, and run the new macro (Alt-F8). If you break it, you can re-download it.
In a few cases I use regular expressions (regex), which can be a bit complicated and tricky. Fortunatly they are popular and you may be able to find one online that does exactly what you want. If you are curious and want to learn more about regex, this website is a good place to get started.
I hope this example will be useful, and I will be glad to answer your specific questions if you have any.
No comments:
Post a Comment