How to Create a Script That Parses CSV Files

Some records are not available for CSV import, but it might access through scripts. Therefore, a script might import records or use a CSV file to perform some other action.

To create a script that parses a CSV file using SuiteScript, some important steps are required.

1. If the goal is to add or modify records in NetSuite check the SuiteScript Records Browser and make sure the record type and fields to be modified are exposed to SuiteScript.

2. Upload the CSV file to the file cabinet. It should be renamed to have a txt extension so that SuiteScript can access the file contents. Then take note of the internal id of the file.

3. Load the CSV file using nlapiLoadFile. Use the internal id from step 2 as fileId, as following:

   var file = nlapiLoadFile(fileId);
   var contents = file.getValue();

   Copy

4. Find a way to break up the file into parts. Below is an example which uses regular expressions:

   delimiter = ",";
   var pattern = new RegExp(("(\\" + delimiter
   + "|\\r?\\n|\\r|^)(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|([^\"\\"
   + delimiter + "\\r\\n]*))"), "gi");
   
   var columnHeadings = [];
   var columnRow = true;
   //Counter for which column the script is on
   var i=0;
   
   // Keep looping over the regular expression matches
   // until we can no longer find a match.
   while (arrMatches = pattern.exec( contents )){
   	// Get the delimiter that was found.
   	var strMatchedDelimiter = arrMatches[ 1 ];
   	// Check to see if the given delimiter has a length
   	// (is not the start of string) and if it matches
   	// field delimiter. If id does not, then we know
   	// that this delimiter is a row delimiter.
   	if (strMatchedDelimiter.length && (strMatchedDelimiter != delimiter)){
   		//Reset the column count
   		i=0;
   		//Next line so it isn't the first row of column headings
   		columnRow=false;
   	}
   
   	// Now that we have our delimiter out of the way,
   	// let's check to see which kind of value we
   	// captured (quoted or unquoted).
   	if (arrMatches[ 2 ]){
   		// We found a quoted value. When we capture
   		// this value, unescape any double quotes.
   		var strMatchedValue = arrMatches[ 2 ].replace(new RegExp( "\"\"", "g" ), "\"");
   	} else {
   		var strMatchedValue = arrMatches[ 3 ];
   
   	}
   
   	if(columnRow == true) {
   		columnHeadings.push( strMatchedValue );
   	} else {
   		//nlapiLogExecution("DEBUG","Heading: "+columnHeadings[i], "Value: "+strMatchedValue);
   		//Do something with the data
   	}
   	i++;
   }

   Copy

5. This should be a scheduled script (It is possible to save and execute from the UI so this script doesn't run on a schedule). Include code to prevent the script from timing out due to a high number of rows by having it reschedule itself. An example can be found in the article Scheduled Script Samples. Make sure the script takes note of where it left off (E.g., a custom record or script parameters).

6. If this script is adding or updating records in NetSuite, it should contain code which can check if the record already exists.  This can be accomplished using Search APIs.

7. Optionally set up the script, so it sends some notification when complete (E.g., sending an email)