Browsed by
Tag: serverside

Boost Efficiency: Convert Large Netsuite CSVs to Excel XLSX

Boost Efficiency: Convert Large Netsuite CSVs to Excel XLSX

I faced a challenging project that involved converting large CSV files stored in Netsuite’s file cabinet into XLSX format, specifically in Excel files. These files varied in size, with some exceeding 30 MB and even 100 MB. Initially, I attempted to use SheetJS, a plugin designed for this purpose (https://docs.sheetjs.com/docs/demos/cloud/netsuite/). However, I encountered difficulties as the plugin loaded the entire CSV file into memory, causing errors and performance issues.

To overcome this limitation, I devised an alternative solution for converting the CSV files to Excel XLSX format. My approach involved iterating through the CSV file line by line and constructing the XLSX file from scratch. Leveraging the fact that XLSX files are essentially zip files containing multiple XML files, I successfully accomplished the conversion. As a result, not only were the CSV files successfully converted, but the resulting XLSX files also demonstrated significant reductions in file size. In some cases, file sizes were reduced by up to 50%.

However, it is important to be aware of certain caveats associated with this conversion method. There are instances where the size of the resulting ZIP file becomes too large to compress, leading to a failure in the conversion process and triggering a timeout error. The exact threshold for this issue is not specified, as I have encountered failures with files around 50 MB, while others as large as 200 MB were processed without any problems. Consequently, it is crucial to consider the possibility of encountering heavy files that may not be compatible with this code. For optimal processing power, it is advisable to execute the code as a Schedule/Mass Reduce script.

 

To utilize the function effectively, ensure you are running it on Suitescript 2.1 and that the following modules are loaded: “N/xml,” “N/file,” and “N/compress.”

Simply provide the function with the File Cabinet path of the CSV file, and it will seamlessly transform it into an Excel file, preserving both the original file name and folder location.
Eg:

 

Snippet to create a delay in a server side script

Snippet to create a delay in a server side script

The motivation behind the creation of this function stems from the absence of a built-in setTimeout functionality in the server-side environment of Netsuite. Although I personally had not encountered the necessity for such a function, I recognized the potential value it could hold for others facing similar circumstances.

It is essential to note that the provided code relies on a busy-wait mechanism, which is widely acknowledged as an antipattern and discouraged for use. This approach involves a loop that continuously checks the system clock until the specified duration elapses. While it serves as a makeshift alternative, it is important to exercise caution and consider alternative solutions whenever possible.

For your convenience, the function is provided below:

 

And then call it like this:

Beware of execution time limits when running this code. Always test first.

By incorporating this function into your code, you can simulate a delayed execution of a specified function after a given time period. However, it is advisable to explore more efficient and reliable alternatives for handling time-based operations in Netsuite’s server-side environment.