10 tips on how to improve data conversion quality.
Since data conversion efforts vary substantially, partners often use a time and material basis for billing. You are able to multiply savings since most implementations require at least one mock data load for prototyping, testing or balancing as well as a go-live load. So be smart about how you compile and provide your data – consider these 10 tips:
Utilize ABS-provided QuickStart data templates.Make use of these templates rather than sending data in your own format. Consider the embedded notes, such as data type and field length limitations. If your partner does not provide data templates or clear instructions and examples, ask for them. Requesting data templates with a few sample records added makes it easier to get familiar with the required format.
Clean up obsolete data.Your Microsoft Partner will make every effort to guide and assist you during the data load to ensure it is correctly loaded into the ERP system. You, however, are in the best position to do any cleanup in-house before sending the templates or attempting a load. Now is a good time to get rid of those pesky duplicates and obsolete data. Only provide as much data as you need going forward.
Respect the field length and data type and be smart about using prefixes.Each field has a certain type and length that the data must adhere to in order to be loaded. While sometimes data can be truncated it may not always be the best or desired option. You should decide how to truncate or abbreviate values. Most Code fields have a Description field that goes with it so keep the Code as short as possible. Keep in mind someone may quickly want to enter the code without scrolling or filtering through a list of similar codes. So rather than setting up Locations with Codes “WHSE A”, “WHSE B”, “WHSE C”,… consider using “A WHSE”, “B WHSE”, “C WHSE” instead. The user will only enter one character and hit TAB to set the Location Code.
Avoid special characters, especially in Code fields.Use Excel functions to eliminate tab stops, carriage returns and other special characters that may be included in text strings. The following characters may work in code fields but make filtering on data more difficult and cause other issues: !@$&*()<>?”|`~. If you need a separator character, use a dash (-), underscore (_) or space (˽).
Check the total count of records in the file.After exporting data out of your legacy system and reformatting, make sure the final result includes all records you want to submit. An easy count on total records/rows in Excel helps to ensure completeness. Copy the Code column (or columns) that uniquely identify one record from the data template to a new sheet and “Remove Duplicates” – there should be none.Use Auto-Filter to spot check values in rows.
Clicking through all the fields once on the Auto-Filter button takes little time and should be included in any review – it is also the first step that your Microsoft partner may take to quickly review the template for any obvious issues. This simple yet effective step will help avoiding very long text, special characters and invalid/duplicate Code fields (e.g. “NET 30” vs. “NET30”, “FURN/EQUIP” vs. “FURNITURE”) or Option field values that may be incorrect (e.g. “STRAIGHT-LINE” vs. “Straight-Line”).
Review the data conversion document with your Microsoft Partner.If your partner does the data conversion for you, they should track changes made to your data and discuss with you. ABS will track changes in a Conversion Document by functional area so review is easy and helps future loads and additions, e.g. adding additional master records between the prototype and go-live load.
Store data templates in a data conversion folder.It is good practice to keep all the files that you are sending to your Microsoft Partner in one place or project folder. Also request the latest templates that were used for the import from your partner if not provided periodically. This allows you to always use the most recent format and mitigate the risk of inadvertently changing data back to an earlier state or miss additional fields that were added to the template.
Keep values consistent across data templates.Make sure that a field values referring to another table is spelled the same way in both tables. A Gen. Product Posting Group value for example should be the same in the General Posting Setup as well as in G/L Account, Item or Resource tables.
Provide one template for each company.If you have multiple entities that will be setup in NAV as companies, provide one template for each company. Make sure to apply changes that were made in one template to other templates.