topic Re: Excel XLSM Splitting in Workato Pros Discussion Board

Excel XLSM Splitting

canderson — Fri, 12 Feb 2021 23:42:07 GMT

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!!

Re: Excel XLSM Splitting

ajorde — Sat, 13 Feb 2021 03:16:26 GMT

A few weeks ago, the product hour showed how to use the new javascript action to do something like this. Maybe go watch the recording and see what they did? Otherwise, we've done something like this with a script on OPA and then brought the necessary data back into the recipe from there.

Re: Excel XLSM Splitting

ben-urbanski — Sat, 13 Feb 2021 04:03:17 GMT

Hello Christopher,

Because Office365 and MS Excel don’t have great APIs, it can be tricky to work with them in Workato.

Here are some ideas (and things we’ve seen done):

Use a Javascript action and a node.js library like foundhere to parse the Excel workbook to one or more CSV files in recipe. If you’re interested in this approach, I can share additional details on how you’d include that example library in your Javascript action.
Use Google Drive as a medium for converting Excel to CSV. Google Drive has good APIs that you could use to upload an Excel file and convert/download it to CSV.
Use our on-prem agent (OPA) and a CLI command to call a shell script that use any number of different scripting languages to parse Excel to CSV,https://docs.workato.com/on-prem/agents/profile.html#command-line-scripts-profile.
Use OPA and a Java extension to do the same,https://docs.workato.com/on-prem/agents/profile.html#extensions-profile and https://docs.workato.com/on-prem/agents/extension.html.

Regards,

Re: Excel XLSM Splitting

raina-ahuja — Sat, 13 Feb 2021 04:20:45 GMT

Thanks Amy!

Christopher Anderson here's a link to the product hour that walks through an example of this with the JavaScript connector. Take a look after the 19 minute mark

Re: Excel XLSM Splitting

canderson — Wed, 17 Feb 2021 04:38:54 GMT

Thanks Ben, will be digging into this this week and let you know what I find!

Re: Excel XLSM Splitting

mroldanvega — Sat, 03 Apr 2021 04:22:16 GMT

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

Re: Excel XLSM Splitting

ben-urbanski — Sat, 03 Apr 2021 05:02:33 GMT

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.

Re: Excel XLSM Splitting

ben-urbanski — Sat, 03 Apr 2021 09:09:51 GMT

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,

Re: Excel XLSM Splitting

mroldanvega — Sat, 03 Apr 2021 20:08:38 GMT

Thanks a lot! I will give it a try!