This article is relevant if you are looking for a way to automatically upload and import CSV files into NetSuite.

Background

It’s not uncommon to find that organizations desire to push information derived from alternatives into NetSuite on a regular frequency. The most rudimentary mechanism offered by NetSuite is via the Comma Separated Value (CSV) file import process. This file import process is what I call the “opening move” in NetSuite electronic integration — it the easiest to understand and generally within the grasp of most computer users.

My firm is invited to all kinds of customer situations. For example, one company recorded its eCommerce customer sales as NetSuite Cash Sales transactions once a month. Sales are recorded, inventory is decremented, and cash is increased.  In this model, NetSuite is a “follower” system in that it learns late about the transaction and the platform is not used to manage the business operation. We often see this “follower” model when NetSuite is used as a financial system replacement for QuickBooks or other small business accounting system.

In these “follower” models, the general concern is the timeliness of getting the data into NetSuite. The patterns I often see is to upload information monthly (or better, accounting period), weekly or daily. As the need to get information into NetSuite becomes more frequent, the desire to automate electronic uploads becomes more valuable.

A Comment about NetSuite Follower vs. Leader Approaches

While this article is about automating CSV file imports into NetSuite, I thought it would be valuable to spend a moment to speak about the Follower vs. Leader model.

In my mind, the NetSuite ERP Platform becomes very valuable when organizations adopt the business system to represent the center of its universe. Thus, the idea is that we want to enjoy the power and many benefits derived from having a single database and one version of the truth when we commit to this approach. Let’s make sure that NetSuite is the tool used to lead the business and the repository for all actionable matters. NetSuite becomes the hub while all other systems are a spoke in our “center of the universe” pattern.

Consequently, it means that we want to run our business operations with NetSuite “leading” where all meaningful interpretations, commitments, and assertions are recorded so that all actors, including external parties (such as customers and vendors), have the most current information to act on. To run in this manner, we must be in the mindset that “any piece of information needs to be in NetSuite” in order to act. When we are in this mode, information update latency is naturally minimized because it’s urgent to get data into the database. And, we don’t have any real-world action happening unless it is indeed in NetSuite.

Thus, NetSuite used only as a financial system misses the larger opportunity. In this article, you will notice that I have a bias toward the leader model. We are especially good at working with our client organizations when they sincerely work to make NetSuite run the entire business operation (for which the financial elements will result as a result of good operational practices). As such, when I explore capacities for automation, I am generally thinking about scalability as the demand for information becomes more meaningful and, as more pressure to perform is applied, there is the need for trouble-free day-to-day operational usage.

“SuiteSolutions – CSV Integrator” Bundle 77203 (version 2.20)

As of the beginning of 2019, for existing NetSuite customers, NetSuite has made available, free of license charge, a number of bundles that have been developed by the NetSuite internal SuiteSolutions group (see SuiteAnswers Article). This is great news for the community as the free license makes it much easier to evaluate NetSuite software offerings and adopt the software.

Since my firm produces all kinds of automation for demanding NetSuite clients, I wanted to make sure we understand how the community has a new toolset available without license charge. The CSV Integrator serves the purpose of the “first move” to attempt to automate the importing of CSV files into NetSuite.

Basic NetSuite CSV Integrator Architecture

The first thing to share is that the application demands fairly good knowledge of general IT application concepts. We found that Borko M, our senior consultant, was able to get centered in about a half-day to the inner workings and capacities.

The CSV Integrator is an open-source code Java client program that runs in two modes:

1. GUI: a Graphical User Interface prompts users for file inputs and connection information so that the program can drive action — one import at a time. This is a good mode to run in to get the setup working right.
2. CLI: a Command Line Interface mode, or headless, allows you to use programs, such as shell scripts, to drive the scheduling and automated operation. In my mind, this mode is really required to get the benefits of automation.

Note, we tested version 2.20 of the delivered application in July 2019. At that time, we discovered that we actually needed to run the Java 8 virtual machine as Java 7 does not support TLS 1.2 which more recently became a requirement to connect up with NetSuite SSL delivered services.

The application allows for two primary source file systems:

1. Local File Systems: the files to be imported can be located locally to where the Java application is running.  This can be your desktop computer.
2. FTP Systems: support for the collection of CSV files located on remote FTP or sFTP systems.

The application intelligently moves files from working filesystem directories to help control the job flows.

While the system can use standard password authentication mechanisms, we prefer to use token-based authentication as a means to set up the connection between the Java Application and NetSuite. An application role can be tailored to drive the minimum permissions required to drive the application. Note, we have written other articles about Token-Based Authentication that may be helpful. See Download a NetSuite OAuth Token Based Authentication Sample Node.js Program and Download a Java Application to Connect to NetSuite OAuth TBA Restlet Endpoints.

NetSuite supplies a Restlet (SuiteScript version 1.0) as a hook point between the Java application. CSV files are pushed up through the Restlet. The application allows for specifying which import task queue to use if you purchased the NetSuite SuiteCloud+ multi-threaded add-on. A custom record based NetSuite mapper table is used to help the application bridge the source CSV files to the proper native Saved CSV Import map.

The application is documented and, in our assessments, meet NetSuite’s high-quality standard for providing good guidance information.

Considerations for Enterprise Import Automation

In my mind, I assess the tools provided as a relatively simple application for “first move” efforts to automate CSV file import automation. When we lead our client integrations, we generally produce “Enterprise” quality automation because we hold a larger organizational narrative to ensure that our business operations are trouble-free and ensure that NetSuite is the “Leader” application.  As such, the following considerations may be contemplated in your evaluation:

1. Bi-Directional: the automation supports a single automation direction (to NetSuite, not from NetSuite). However, in most integration scenarios, a response acknowledgment model is used to help ensure that the coordination between information sharing is complete. Readers may be interested in our article on Apache Camel Middleware
2. Information Shaping: the CSV file pattern is limited by the single header approach. In many cases, information needs to be expressed in more complex shapes (e.g., header/detail, hierarchical, and non-CSV formats).  You are only able to use “Single file” vs. “Multiple file” uploads. Readers may be interested in our free of license charge bundle to learn how you can target multiple record types while splitting, validating, and consolidating data, as needed.
3. Job Control: in a scalable model, the orchestration for moving data into the database demands knowing, at all points of interaction, did all, part, or none of the import succeed? NetSuite’s CSV import patterns and related support tools do not have sufficient API level mechanism to see and control this.  If any system goes offline, as they invariably will, how do you recover from where you left off?  Readers may be interested in our article Shopify to NetSuite Integration: Your Way! for how batch systems can be architected. In this CSV Integrator application, any failure requires information to be resubmitted which may be difficult to do depending on the problem encountered.
4. Scheduling: the application has no built-in capacities for scheduling. Java and related frameworks support powerful scheduling abilities with accompanying logging/monitoring. However, without those enhancements, custom operating system shell scripts or other executable task managers need to be conceived, developed, and tested. We take care of these concerns in our Apache Camel based Open Source Middleware bundle.

CSV Integrator Video (23:19) Overview

The video below (23:19) is an interview between me and our consultant, Borko M., and will be valuable for anyone seeking to understand how the application works.  You will see a demo and the last part include our assessments.

Work with Expert NetSuite Leadership

My hope is that this article helped you evaluate NetSuite’s CSV Integrator application.  The NetSuite SuiteSolutions group developed this application for their clients during implementation engagements. The tools have matured to the point they are making them available free of charge to the community. It’s really a good illustration of the power of the NetSuite platform.

However, the power of the platform is really about capacity available for all. Capacity is only potential in the hands of capable actors. Depending on how you articulate concerns, see logic patterns, and then design solutions, you will attack challenges differently. In our firm, we look at our client situations and ask more questions. We hold standards for care which mean we plan before we act. We will use first everything NetSuite gives us before we reach beyond. We are different because we give our clients, free of license charge, all the algorithms we have previously built, to solve our client challenges.  Our offer really is enterprise guidance to help our clients realize the promise of NetSuite acting in a leader model so they indeed get the promised benefit of the application investment.

If you found this article beneficial, feel free to sign up to get notifications of new articles. If you have a situation that demands an Enterprise solution, let’s have a conversation.

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