We can help you data transfer from other systems such as MYOB, QuickBooks, Quicken, Excel Spreadsheets or any other data compliant accounting software system. We have experience in extracting data from legacy systems and even UNIX based systems.
Data conversions between financial, customer relationships, and other transactional systems have always represented a fairly risky proposition. In conversations with clients, they typically start with wanting all their data to be converted into their new system. We discuss the ways we can minimize the scope of data that needs to come over to keep costs and time to implement low. In general, we start with these suggestions:
- Only active customers, vendors and inventory related master information.
- Historical month end (or quarterly, if possible) general ledger balance information going back the minimum number of years that comparative financial analysis is meaningful. Usually this is one to two years.
- Chart of accounts with minimal changes. Changing the chart though is usually a key opportunity to enhance future reporting and often, this can’t be avoided. However, some systems possess the capacity to change the chart structure while keeping transaction integrity. In these cases, we modify the chart after the data is converted.
Of course, these suggestions may not be realistic and we typically enter into a meaningful conversation about the scope which gets to the root of business concerns.
It is also important you cleanse the data as much as possible before BM Online transfer the data for you into NetSuite via the Import Assistant. This includes removing junk records and ensuring the spreadsheets are formatted corrected with fields from you current system in the correct columns of an Excel spreadsheet. Our consultants can also guide you through this process. The import structure of NetSuite is very user friendly and is Table Mapping based.
CSV Import Tips, Tricks and Best Practices
Build New External ID
This is the single most important point of any migration work. ALL record types should have a new External ID constructed, which will be used in all CSV migration efforts to positively and uniquely identify the record. The New External ID column should be built by taking a existing unique identifier and tagging on suffixes or prefixes to make it further unique and classify the data. For example, in a simple scenario of migrating Item records from one Netsuite account into another account, the New External ID could simply be [old internal id]-[some suffix] (ex: 12345-USA). For more complicated imports, such as the case of One World migration, it is best to identify per-subsidiary data by using as suffix the internal id of the target subsidiary. For example, if migrating an invoice to a Europe subsidiary whose internal id is 5 in the new OW account, the New External ID might be 12345-005. It is also acceptable to use a country suffix (for example 12345-UK). If possible, this Next External ID should be built into the source account’s Export Search, using Formula (text) field. The formula should be something like concat({internalid},’-UK’)
Map New External ID as key
To quickly and effectively verify lines of data, we use the concept of a “data key”. This is basically done by concatenating all the fields that are validation points into one giant string, and then running a VLOOKUP between your two files. For example, let’s say I have the following CSV snippet
A | B | C | D | E | |
1 | External ID | Department | Customer ID | Amount | Date |
2 | 1123-AAA | Admin | 1002 | 54.99 | 10-25-2011 |
=A2&” “&B2&” “&C2&” “&D2&” “&E2 |
Which will result in the following string:
1123-AAA Admin 1002 54.99 10-25-2011 |