Learn how to Automate NetSuite CSV File Imports

This article is relevant if you need to produce trouble free automated NetSuite CSV file imports.

Background

A number of our clients have leveraged our NetSuite FTP service to move files back and forth between remote systems. This utility has solved their concerns for bi-directional file distribution. Receiving files hands-free is the first step; the next step is to import the files into the platform. Two common use cases is to import eCommerce orders or shipment information from a third party logistics company. Because a file based system naturally produces queues and batches, these operations should be managed on a timely basis.

When speaking with business people, they generally don’t want to think about IT related matters — especially because they have invested to move away from IT infrastructure by adopting the NetSuite cloud based business system. Yet, in any system integration, there needs to be some degree of care to ensure algorithms run as expected. While there are a number of operational challenges in any file based system integration, the key is to have the proper logging and error message visibility so you can develop NetSuite dashboards or alerts to help the business team learn they need to take action.  The challenge however is NetSuite’s CSV file import queue doesn’t quite give enough information to produce exception based management reporting.

Introducing a CSV File Import Log Record Manager

To get in front of the challenge, we need to make sure that all comma separated value (CSV) files import into the target business object as expected. As such, we developed a utility with supporting scheduled scripts to drive automation. The key to making this work is to gain access to two key information elements that is otherwise hard to learn:

1. Expected Records: how many records do we expect to import?
2. Actual Records: how many records actually imported?

For every CSV file, we produce an associated import log record to support the file import operation. The record will hold all information needed to support our work including CSV import file map, the target table, and the field in the target table that will be used to help cross reference back the log record.  Using a SuiteScript 2.0 utility class, the operation can be driven by using saved search to look for candidate files in a source document folder. As we feed each CSV file into the library, it is opened and the number of rows are counted to determine the expected number of CSV records to import. This can then trigger another job to actually act on the CSV file. Along the way, we update job status in case we encounter issues. Finally, after we import the records to the target table,  and move it to a finish folder, we perform a search to determine how many records were actually imported. Thus, we are armed with everything we need to monitor the situation. See related image.

Get Help Automating CSV File Imports

The CSV File Import Record system is part of our general library we give to all of our clients. See the related bundle. If you have internal NetSuite support, we can help them learn the utility so they can program it for all your situations. Naturally, we use this utility and others to get all kinds of NetSuite system integration concerns done. If you see a situation where our tools can help, let’s have a conversation.