topic Re: OPA: Excel connectivity in Workato Pros Discussion Board

OPA: Excel connectivity

jelle-vanloock — Fri, 15 Jul 2022 13:30:24 GMT

I noticed that Workato offers a build-in functionality to connect to Excel. However, this features includes a connection to oneDrive, Sharepoint or Google Drive. This is not what I want to achieve...

I want to read an Excel file from an on-premises system. Does anyone has an idea to solve my problem? Or will a new feature request be the answer?

Re: OPA: Excel connectivity

roland-daane — Fri, 15 Jul 2022 13:42:34 GMT

Will your IT department allow that?

There is documentation on Workato how to setup a service so it can access on premisse

Get Outlook for iOS

Re: OPA: Excel connectivity

jelle-vanloock — Fri, 15 Jul 2022 13:44:17 GMT

Do you have any reference towards the documentation? Normally IT won't have any issues with it.

Re: OPA: Excel connectivity

tco — Fri, 15 Jul 2022 13:44:58 GMT

Hi Jelle, Nice to e-meet you. We are a new Workato partner, so my answer will maybe be not the one you need. Workato offer a on-prem connector, is this something you have try. I have done some receipt to connect to some file that I stored on my local network and on a Cloud Server that we have on OVH. Also, if you can give more information about what you are trying to achieve, I try this in my environment, Have a nice day, Thierry

Re: OPA: Excel connectivity

tco — Fri, 15 Jul 2022 13:50:37 GMT

This is a slide that show the On-Prem Workato Agent that allow to connect to local files.

Re: OPA: Excel connectivity

jelle-vanloock — Fri, 15 Jul 2022 13:55:12 GMT

Thierry, thanks for your reply. However, it is not exactly what I'm looking for. Let me explain the case more clearly: we are currently using OPA's for quite a while now, and the 'On-Prem File' connector is only supporting CSV it seems. I want to extend this functionality to read other types of files (besides flat files), in my case: Excel.

Re: OPA: Excel connectivity

tco — Fri, 15 Jul 2022 13:58:42 GMT

ok. Let me check on my side. I keep you posted if I found a solution, Thierry

Re: OPA: Excel connectivity

sunaif-aziz — Fri, 15 Jul 2022 14:25:41 GMT

Hi Jelle,

Did you try the trigger "New file in folder", it does not say only CSV files so presume it should take all types of files but I did not use it myself so cannot confirm. Just a hint.

Re: OPA: Excel connectivity

jelle-vanloock — Fri, 15 Jul 2022 14:28:28 GMT

I tried this approach as well and thought it was going to work, but the question remains: if I found a file in a folder (on-premise) and it is an Excel file, how do I parse the downloaded bytes?

Re: OPA: Excel connectivity

tco — Fri, 15 Jul 2022 19:18:42 GMT

Hello Again Jelle, did you try to save your excel as a csv ? If I try to access my on prem spreadsheet with extension xlsx, it does not work however if I save my excel as a csv, It works fine. Thierry

Re: OPA: Excel connectivity

jelle-vanloock — Fri, 15 Jul 2022 19:23:27 GMT

The business refuses this approach... They want to work with Excel. I tried to confince them, but did not yet succeed...

Re: OPA: Excel connectivity

jelle-vanloock — Fri, 15 Jul 2022 19:29:58 GMT

Currently, I'm trying to user the On-prem file - new file trigger, and then I'm trying to parse it with Ruby or Python action. However, not yet successful...

Re: OPA: Excel connectivity

tco — Fri, 15 Jul 2022 19:47:46 GMT

OK. I have tried to rename the file using the action "Rename" from XLSX to CSV but that does not work too. I continue searching and let you know if I found something.

Thierry

Thierry Cools

Managing Partner

Mail/Skype: tco@pse.be

Tel: +32 2 790 25 00

Fax: +32 2 790 25 00

Mobile: +32 495 29 05 25

Avenue Franklin Roosevelt, 104 Bte 11, 1332 Rixensart - Belgium

This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission.

Re: OPA: Excel connectivity

tco — Fri, 15 Jul 2022 19:58:23 GMT

Are you using Office 365 and/or sharepoint ?

Thierry Cools

Managing Partner

Mail/Skype: tco@pse.be

Tel: +32 2 790 25 00

Fax: +32 2 790 25 00

Mobile: +32 495 29 05 25

Avenue Franklin Roosevelt, 104 Bte 11, 1332 Rixensart - Belgium

Re: OPA: Excel connectivity

jelle-vanloock — Fri, 15 Jul 2022 21:00:32 GMT

Thierry,

Most of our accounts are connected to Office 365.

FYI: I found a usefull product hour post: https://www.workato.com/product-hub/javascript-connector-the-freedom-to-transform-your-way/

Recipe URL: https://app.workato.com/recipes/1381928-simple-xlsx-to-csv#recipe

Re: OPA: Excel connectivity

tco — Fri, 15 Jul 2022 21:14:22 GMT

Thanks for the information.

On my side, using the Excel connector I was able to parse correctly the file that that was saved on my OneDrive.

Have a nice weekend,

Thierry

Thierry Cools

Managing Partner

Mail/Skype: tco@pse.be

Tel: +32 2 790 25 00

Fax: +32 2 790 25 00

Mobile: +32 495 29 05 25

Avenue Franklin Roosevelt, 104 Bte 11, 1332 Rixensart - Belgium

Re: OPA: Excel connectivity

tco — Fri, 15 Jul 2022 22:11:18 GMT

Hi Jelle,

I have been testing the JavaScript Recipe that you sent me, and it is working fine.

Of course, it works if you know the format of the XLS file. I did a short POC with a spreadsheet having 2 column with Products information and the result was fine, it was converted in a correct CSV.

I hope it solved your issue too,

Thierry

Thierry Cools

Managing Partner

Mail/Skype: tco@pse.be

Tel: +32 2 790 25 00

Fax: +32 2 790 25 00

Mobile: +32 495 29 05 25

www.platinum-services-europe.com

Avenue Franklin Roosevelt, 104 Bte 11, 1332 Rixensart - Belgium

Re: OPA: Excel connectivity

jelle-vanloock — Mon, 08 Aug 2022 12:37:02 GMT

Thierry,

I was not able to test it yet, due to my yearly holiday period. I will try it as soon as possible.

Based on your answer, my expectations are high!

Re: OPA: Excel connectivity

jelle-vanloock — Mon, 08 Aug 2022 20:04:54 GMT

Thierry,

I'm trying to use the trigger 'On Prem Files - new file in folder' but it is not working on my end. Did you change anything in the JS action?

Error message: Line input evaluation error in js_eval. Error calculating input for field 'file_content': undefined method `encode_base64' for #<Workato::Types::InputStreamProxy:0x00007fa13e422818>.

Re: OPA: Excel connectivity

jelle-vanloock — Tue, 09 Aug 2022 15:05:02 GMT

Just a quick FYI for everyone in the community: above solution is working. BUT it is necessary to add the method '.to_s' when using On-Prem Files.

An additional point: when the Excel has some filters applied or calculated fields, this will become an issue: filters are getting discarded for example.

Found my solution over here: https://app.workato.com/recipes/1396791