Thursday, November 10, 2016
3 Tips For A Successful ERP Data Conversion
ERP, NetSuite, Tips & Tricks
Written by Chris Wu
When it comes to an ERP implementation, one of the most important (and yet most overlooked) processes your organization will go through is data conversion. Data is not only critical to every facet of your business, it’s something that’s constantly being created, accessed, and modified. Over time, data issues and unresolved errors accumulate (which is probably a big part of the reason you are looking to move to a new platform to begin with) and this can cause unreliable information, issues with processing transactions, and many hours of wasted time in finding and fixing errors. I have seen businesses crippled and senior executives fired over bad data.
If you’re in an ERP implementation project or considering one, I can’t stress enough that planning for your data conversion will make all the difference in the success and efficiency of your implementation. Too often I see this process rushed (“Let’s just take what’s in our old system and dump it into the new system”) and not fully tested, and that exposes your project and your company (and your career) to enormous risk.
Here are my top 3 tips for making your data conversion process smooth and successful:
#1: Ensure your project team knows their responsibilities and carries them out.
Identify the owners of the data (eg. Customer Service Manager or VP of Sales would own the customer master, Controller/CFO would own the Chart of Accounts, Operations Director would own the Item Master, etc.). Your IT Manager or NetSuite System Administrator would own the data conversion process overall. Hold the owner(s) accountable for the integrity of the data under their responsibility. DO NOT, REPEAT DO NOT accept the excuse “I’m not going to sit there and trawl through thousands of customers and pull out the duplicates and the spelling errors. Do you know how many customers we have in the database? I don’t have time, I’m too busy!”
You MUST have clean data, validated by those who know the data best and whose operational responsibilities are dependent on the data, or you will waste countless hours dealing with the inevitable import errors. And if any errors make it through undetected, you could be setting yourself up for the whole business coming to a grinding halt in the worst case. Ask yourself if the discomfort of “making” your key people clean their data compares with trying to explain to the Board why Customer Service staff can’t enter orders after spending hundreds of thousands of dollars on the fancy new system.
Appoint the owners, make them aware of their responsibilities, get their agreement and commitment, hold them accountable for cleaning and formatting the data for import.
#2: INDEX MATCH is your friend.
Index Match is an Excel formula that functions somewhat like VLOOKUP, but is much more reliable.
Often during data conversion, mapping a field based on name works fine. However, sometimes you find that no matter how much you check and double check that the names are correct, for some reason the CSV Import keeps error-ing out because it can’t seem to match correctly to the name. I have seen this occur in a customer import where the Sales Rep assignment on each customer is the source of the import error (i.e., it errors on not being able to find “John Smith” even though you have checked a million times that “John Smith” in the data extracted from the old system is spelled EXACTLY the same as Sales Rep “John Smith” in NetSuite). In this case you can solve the error by matching on the Internal ID instead of the name.
But how do you find the Internal ID, since it is a NetSuite field and does not exist in the legacy data? You do it by enabling display of Internal ID in NetSuite preferences, then run an Employee search or list, and export it to Excel. Copy the resulting data to another sheet in your import file. Then use Index Match to return the correct Internal ID from Sheet 2 into a column on Sheet 1. Here is a handy link on how to use Index Match, as well as an explanation of why it’s better than VLOOKUP.
3: Deal with Leading 0’s.
Your method for extracting data from your legacy system may output files in CSV format. Some of your data may be in numeric format and contain leading 0’s. If you need to manipulate the file before importing to NetSuite, you may find that opening the CSV file in Excel drops the leading 0’s. Therefore Item no. 09999 becomes 9999 once you open it in Excel, and if you have another Item no. 9999 (which didn’t have a leading 0 to begin with) now you have duplicate item numbers and this will be flagged as an error in your NetSuite CSV import, resulting in wasted time finding it, correcting it, re-saving the CSV file, running the import again, waiting for it to finish, then checking it again. Even if it doesn’t result in a duplicate, you still have incorrect data being introduced and this may cause mismatch problems when you go to import open Orders, PO’s, Invoices and Bills.
Best way to deal with this? If you have legacy data for import with leading 0’s which is output to a CSV file which needs to be opened in Excel, open a BLANK Excel file first. On the Data tab, select Get External Data From Text. Browse to your CSV file, select Delimited and My Data has Headers, then Next. In the next screen, select Comma as the Delimiter, then click Next. In the next screen select each column in turn and specify the Column Data Format type of Text. When you click Finish the data from the CSV file will be loaded into the new Excel file and the column containing the leading 0’s will be in Text format, preserving any leading 0’s from numeric fields.
When it comes to working with an ERP implementation partner, it’s wise to ask them what their approach is to data conversion. How much help will they give you? Are they just going to give you CSV templates and leave you to it? Will they train you on the CSV import tool and hold your hand while you get familiar with it? Will they do the imports for you? Will they be responsive if you have questions or need help? Do they have the skills to resolve the hard problems?
When I’m working with customers to upgrade them to NetSuite Cloud ERP, I take a consultative approach where we discuss in depth the volumes and types of data to be imported, the skills and availability of the internal resources, and the impact to the project budget and timeline. The team at Luxent can be very hands on with data conversion, working in close partnership with our customers or equipping our more strongly resourced customers to shorten the learning curve and be self-sufficient on the NetSuite tools very quickly. We work with our customers’ project teams to guide them on best practices for how to bring over their data, and then we stick with them as data is cleaned and imported to help troubleshoot any errors. Because we believe that good data makes for a good ERP implementation, we focus on getting this critical step right so that the rest of the implementation goes to plan.
Contact our team of ERP experts for additional advice on data conversion.