If you’re a Microsoft CRM Consultant and ever needed to work with Microsoft Excel for cleansing data you’ve probably encountered the unwanted formatting of certain characters like leading and trailing spaces, and more to the point, leading zeros. Of course any Office guru can remind you about the ‘special’ formatting on cells in excel like ‘zip code,’ but what if your experience in excel is just one stop in your data cleansing experience, and you don’t plan on using excel as the data source?
If you’re using Scribe as an ERP migration/integration tool like Unitek Consulting does, you’re probably in the habit of opening the .csv file to make sure your formatting kept the leading zeros on your zip codes intact – but of course excel does not store cell formatting changes in .csv files so you won’t see your leading zeros.
So let’s say you don’t open that .csv file, and use it as a source for your Scribe import (Scribe has an Excel Driver, but you can’t run SQL queries for your pre-operation step controls or data translations) without opening the file in Excel – you STILL don’t see your leading zero!
The problem there exists when your ODBC driver fails to capture the leading zero because it would rather see a VARCHAR field…
Force Scribe to read your zip code fields that only have 4 characters by using the following formula expression:
IF( LEN( S1 ) < 5, SUBSTITUTE( PAD( S1, “R”, 5), ” “, “0”), S1 )
Where S1 is your zip code source field.
Please note that this is only one of several solutions, but if you are in the Scribe Workbench you can avoid lots of data cleanup in various other applications.
For more information about working with .csv files converted from Excel, please visit: Creativyst Docs.
Microsoft CRM Consultant
Unitek Microsoft CRM Services