03 8679 6498 info@bmonline.com.au

Data Transfer

 

data transfer

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:

  1. Only active customers, vendors and inventory related master information.
  2. 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.
  3. 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

 
The following are Tips and Tricks from CSV Import Manual, which documents doing CSV Import migrations in NetSuite. Below are a couple of helpful tips and tricks on any CSV import.
 
VLOOKUP
 
The “vlookup()” Excel function is the single most important tool in your arsenal. It is a cross-referencing function. The syntax is as follows – =vlookup(SOURCE, LOOKUP RANGE, COLUMN TO RETURN, false) SOURCE: This is the value you are looking for in the following LOOKUP RANGE. This is normally a single cell in the sheet you’re running your formula from LOOKUP RANGE: This is a large matrix from which you’re trying to match data. This can be a selection of as many columns as you want. Excell will however run it’s match on the FIRST column of your selected range. All other columns are simply potential values to return. COLUMN TO RETURN: This is a numerical value. Writing 1 here would mean you’re returning the data from the first column from the matched row from your range. Something else (like say 5) would similarly return the value from that column. Obviously, your LOOKUP RANGE must contain enough columns to match the column number you want! false: This is always false, don’t ask yourself any questions. It’s false.
 
As this is a basic Microsoft Excel command, you should be able to find plenty more information and help on it by Googling it. 

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 

As we were very careful to create New External ID keys during the Preparation phase, during our Import phase we now want to make sure to map this everywhere. New External ID should be mapped on ALL records we create. Further, Internal ID should NEVER be used anywhere – always External ID (unless you are specifically running an UPDATE of existing record, as opposed to creating new data). Any joined record being called by its external ID should also be mapped, by pressing on the little pencil icon and setting the reference field to External ID.
  
Verification by Key building
 

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
My verification key will be built using this formula:
=A2&” “&B2&” “&C2&” “&D2&” “&E2

Which will result in the following string:

1123-AAA Admin 1002 54.99 10-25-2011
So, if I do the same in my other file (being careful to use the right value for Internal ID/External ID so the two match), in one VLOOKUP operation I can check if I have perfect matches on my Verification Keys. If I do, then I have just validated the entire record. I can then focus on investigating all the lines that did not match up to understand what my data problems might be. One debugging technique is to scale down the number of fields which my Validation Key comprises of. I could for example take out Department and see if my data matches at that point – if it does, I now know I have a Department problem. for this reason, it is recommended that Validation Key and VLOOKUP formulas be kept “live” (e.g. not copy/pasted to values only) for as long as useful, that way changes can be made quickly without the need to retype the entire formula.