Pages

2011/10/24

CSV Import Tips, Tricks and Best Practices

The following are Tips and Tricks from ERP Guru's CSV Import Manual, which documents our collective learnings about doing CSV Import migrations in NetSuite. For simplicity's sake, I have extracted the top few things that should be helpful 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

ABCDE
1External IDDepartmentCustomer IDAmountDate
21123-AAAAdmin100254.9910-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.


1 comment:

  1. I enjoy the tips, but some screen shots or an actual video might be more helpful. Keep up the good work!

    ReplyDelete