Fully Automate Complex NetSuite Data Imports

This article is relevant if you need a hands-free, reliable and troublefree method for importing data into NetSuite.

Background

Most patrons in the NetSuite community understand how to use the provided CSV file importer tool to insert and update data.  The tool generally works well, especially after you become accustomed to its specific ways of data handling.  I often share with clients learning NetSuite to plan an extra one to two hours more than you might expect to get up to speed on any transactional data imports.

Yet, there are important situations where the CSV file importer simply does not have enough “enterprise” power to go all the way to have a complete operational solution.  Thus, I will outline some of the challenges and how my firm has created a free tool that leverages the power of the SuiteScript platform to solve complex yet trouble-free data import demands.

As a note, I would like to thank Boban D. and Carl Z. on the Prolecto technical team.  Over the years, we started with one pattern I had invented and then the team perfected as we took on more-and-more demanding client situations and leveraged NetSuite platform-tools as they became more capable (e.g., the map/reduce framework).

Challenges with Complex NetSuite Data Imports

While CSV data import logic is not the only way to get data into NetSuite, it is the most commonly used and understood — and its tools are within reach of the power user.    Thus, I will outline a number of considerations that we solved when working to produce file-based integration that will be reliable and require minimal assistance:

1. Data Reach: the CSV data imports are subject to what NetSuite provides you.  If there is a record that you wish to create or update, such as rates on multiple price levels in one shot, create an item fulfillment, or produce a bank transfer, you are out of luck.  However, if the record structure is available with SuiteScript, this is now solved.
2. Data Complexity:  CSV data tends to be organized flat in nature.  But what do you do if you have hierarchical data that is expressed in XML or JSON formats?
3. Multiple Records: what if your data source needs to create multiple related records?  Consider a file full of order data that needs first to generate customer records and then sales orders.
4. Projecting Anticipated Records: when you get a file full of data, how do you know if the entire file processed successfully?  Why can’t we understand the number of business records that will be produced before the batch of data is processed?
5. Data Pickup: how do you get NetSuite to find a data file and start to process it?  I spoke about an add-on tool NetSuite provides, the CSV Integrator, that gets close but does not address these earlier discussed elements.
6. Monitoring: NetSuite’s CSV task status pages are not exposed in an easy-to-query manner.  Thus, how do you know that any automated job is running as expected?
7. Throughput: NetSuite’s CSV import architecture has finally been improved to take advantage of more threads.  If you have much data to process, we need to be able to run operations in parallel.

When building a NetSuite file-based integration, each of these considerations become questions to address.  If the provided import tools do not have the capacity, and you want to stay on the platform, you are stuck with developing a SuiteScript program to work around limitations.

Scalable Pattern for Processing Large Batches of File-Based Data

When processing file-based data that is complex, I will propose the following pattern that ensures trouble-free and works at scale:

1. Batch Job: each file represents a batch of work that needs to be processed.  Thus, a custom record is used to hold information about the file so the import work can be monitored.
2. Splitting and Grouping: information in the file needs to be split logically.  The general pattern is that there is something about the data that allows it be logically grouped into transactions or atomic unit-of-work.  For example, if a file contains 100 orders within it, then the information is to be split based on an “Order Number” grouping key.
3. Staging Detail: the file is split into its respective logical elements and the data is recorded in another custom record which is then linked to the Batch Job record.
4. Record Generator: for each staging detail, a record generator program contains the logic of mapping the respective data to the business record(s) that must be inserted or updated (or even deleted); here the use of specific SuitScript is most important because it has the maximum capacity to handle any mapping complexity.

Once you have this pattern down, then status information both on the batch job and on each staging detail record can tell you if you have any issues that must be addressed.  You can zero on the specifics of the issue which may be one record among many that are problematic.

Introducing the Record Import Export Manager (RIEM) Framework

We call the free tool provided to all of our clients “RIEM” as it stands for the Record Import Export Manager.  In this article, I am only going over the import pattern.

As you study the situation, it becomes clear that all the following points are simply “plumbing”:

1. Batch Job: look for files of a particular type in a respective folder:  we describe the nature of the file in a Job Type record to teach the processor how to find the file and process it.  Once a file has been scooped up by a batch job and its contents are analyzed (as a “blob”), it can move the file to an archive folder on the NetSuite file system to demonstrate it has been consumed by the engine.
2. Splitting and Grouping: using syntax to found the grouping element, we can express a column name, XPath or JSONPath to teach the processor how to split.
3. Staging Detail: the splitting of the file into respective elements is easy to do and now can be pushed into a custom record to hold atomic “blob” data before it is given to a record generator.

I call these three concerns “plumbing” and, while they are essential, they are not very interesting.  Thus, with the tool we invented, we basically describe the nature of the file in a Job Type record and the system does all the essential work to hand it off to produce valuable mapping work.

Record Generator Mapping Effort

The record generator is a NetSuite Plug-in implementation that has a single contract implementation called “createRecord”.  Here, you use SuiteScript to map the atomic “blob” to produce any number of business records.  With proper error handling and messaging, you raise events to inform the framework of the status of the work.

Monitoring the Effort

With this pattern and our framework which is a number of scheduled map/reduce scripts, we then can build dashboards via saved-search technology that help an administrator review the following:

1. Existence of Files: we can monitor a set of folders for expected or unexpected situations
2. Batch Job Status: we can monitor if batch jobs have the expected status information.
3. Staging Detail Status: we can also monitor if any staging detail records have the expected status.

With these elements, we can now build alerts or other controls to know if our jobs are working.

A Word about File Distribution

The discussion here has been about picking up a file that is sitting in a NetSuite file cabinet. One should wonder, “how did the file get to NetSuite file cabinet?”  I call this a distribution question and can be answered a number of ways depending on your situation and ambition:

1. Manual Upload: at worst, a user could upload the file to a specific folder and the job will just begin.
2. sFTP Transfer:  we have tools we give our clients without license charge to transfer files from a remote sFTP file system.  See article, NetSuite sFTP Transfers Made Easy.
3. Other Remote File System: if your data is in Box, Google Drive, S3, old school FTP, or in a remote database, we can get to it.  Our clients are provided yet another license-free tool that is based on the open-source Apache Camel middleware framework.  See article, Link NetSuite to FTP via Open Source Middleware which we have used in many different situations.
4. Email: let’s not forget, email is a way that the data can be transmitted.  Built into the RIEM framework is a NetSuite email plugin that listens on a specific NetSuite provided address and will process the file hands-free.

Thus, its important to not conflate the data distribution question from the import concern when producing a file-based NetSuite systems integration.

Leverage the NetSuite Platform working with NetSuite Experts

NetSuite is a platform and is designed to be enhanced and adapted to solve business problems.  Our firm’s philosophy is to add value by working with competent actors that can listen to concerns and leverage previous efforts to get to a solution.  Thus, our tools are really accelerators that continue to become more robust as we tackle more and more client challenges.  The key is that our clients are not stuck with any recurring charges to get to their solutions — we can educate their administrators to solve matters on their own or we do it for them — it’s about the right relationship according to our clients’ wishes.

If you found this article meaningful, feel free to receive notifications of new articles as I post them.  If you are ready to tackle your file import challenges, let’s have a conversation.

Be Sociable, Share!

• 
• 

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube