cancel
Showing results for 
Search instead for 
Did you mean: 

Excel XLSM Splitting

canderson
Deputy Chef I
Deputy Chef I

Hey Everyone! I'm new to Workato and wanted to see if anyone has solved for the ability to split larger Excel workbooks into singular CSV's to use the utility Workato CSV Parser action? Or if anyone has some pro tips on tackling large excel workbooks, I'm all ears!!

8 REPLIES 8

mroldanvega
Executive Chef I
Executive Chef I

Will the Javascript also work to CREATE an excel from CSV?

ben-urbanski
Workato employee
Workato employee

It appears so. It supports reading a CSV and then writing a XSLX:

 

https://www.npmjs.com/package/exceljs#file-io

 

I’ll give it a try, and we’ll see. I’ll post my results here with a guide that you can use for doing the same.

 

ben-urbanski
Workato employee
Workato employee

Hello Manuel,

 

Following the attached guide, I was able to create a script that uses the exceljs library to convert from a CSV file string to an Excel spreadsheet. Unfortunately, our Javascript action does not support a file/binary output type, and when using a string output I believed it’s converting the Excel spreadsheet to UTF-8 encoding. That’s a problem because the Excel spreadsheet is actually a zipped folder, and converting to UTF-8 corrupts the file. I’m not sure there’s a way around this, but I’ll continue to explore.

 

Here’s the script I used with the example:

 

constExcel =require('exceljs');

const {Readable } =require('stream');

 

constmyFunction =async (input)=> {

   constoutput = {};

   conststream =Readable.from(input.string_in);

   constworkbook =newExcel.Workbook();

   constworksheet =awaitworkbook.csv.read(stream);

   constbuffer =awaitworkbook.xlsx.writeBuffer();

   output.string_out = buffer.toString('binary');

   console.log(output.string_out);

   returnoutput;

}

 

exports.main = async (input)=> {

   returnawaitmyFunction(input);

}

 

Regards,

 

mroldanvega
Executive Chef I
Executive Chef I

Thanks a lot! I will give it a try!