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

	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.

October: Tips & Tricks

1 - Preferred Vendors

You can assign a preferred vendor for items for purchase or resale. This can make things easier in that when you create a new purchase order, if the preferred vendor of the first item will be selected. In addition, if you dropship an item, NetSuite will use the preferred vendor automatically.

You can also link multiple vendors to an item. With administrator rights, click on the Setup tab, then Company->Enable Features. From the “Items & Inventory” tab, select the “Multiple Vendors” checkbox, then click Save.

Now when you edit an item, you will see a drop-down list of vendors on the basic tab. Select the “Preferred” box next to one of these to make it your preferred vendor for that item.

2 - Optimizing for search engines

If you are using NetSuite to host your website, there are a number of things you can do to make sure you get the best-possible rating from search engines:

• Change and add new content regularly.
• Ensure any page on your website can be accessed in five clicks or fewer so that customers can find the page they’re looking for more easily.
• Ensure the names of website sections and links are descriptive. The more descriptive the links are, the more likely the search engine will bring people to the right page.
• Make use of page titles by entering text in the Page Title field on website items.
• Use meta tags to add text describing the content on a page.
• Use links between pages to make it easier to jump from one page on your website to a related one.
• Add alt-text for images

3 - Dashboards
NetSuite dashboards provide ultimate flexibility in organizing your workspace. Setting up your dashboard to meet your individual needs is one of the most important and effective ways to ensure success in NetSuite and to give you an edge over your competition.

NetSuite offers many kinds of portlets so you can display the data you need:

✓ Activities: Shows daily and upcoming activities, with links to detailed activity records.
✓ Custom Search: Shows the results of a saved search. Custom search portlets display three columns of data when placed in the right or left columns of a dashboard, and eight columns when placed in the center column of your dashboard.
✓ Lists: Shows the type of records you choose with links to view (and edit, if you have permission) individual records.
✓ Key Performance Indicators: Shows summary data and key business metrics, with options to highlight results that don’t meet defined thresholds, and to show comparisons between date ranges. As shown in Figure 4-1, the KPI portlet brings your critical business data to the forefront of the dashboard.
No matter what role you use or what job function you serve, you’ll want at least one KPI on your dashboard. If none of the standard KPIs meet your needs, you can create custom KPIs using saved searches.
✓ KPI Meters: Shows a graphical meter of KPI data, with options to flag metrics that meet a specific threshold and to compare across date ranges.
✓ KPI Scorecard: KPI scorecards can provide complex comparisons among multiple KPIs over multiple date ranges or accounting periods. Scorecards also can include Excel-like formulas with KPIs and functions in their expressions.