Dynamically parse the excel without having sheet index or sheet name

[rashmi]

Hi,
Anyone aware of how do we parse the excel with multisheet without even knowing sheet index / sheet name with having certain number of records in each sheet.
I am having requirement to remove the email ids from first row of first sheet and rest of the sheets as is upload. Please let me know is there any other approach instead of parsing each sheet

Any help is appreciated.

[pmancevski]

Hi @rashmi,

This maybe it’s not the best solution but you could try with recursion, and you will control it from “Excel Parser” snap from error view, you will wait/loop until you receive particular error - Sheet with the provided index does not exist:

Here are some links which can guide you when developing recursive pipelines:
https://community.snaplogic.com/t/recursive-pipeline-solution/6202
https://community.snaplogic.com/t/pipeline-execute-maximum-recursion-calls-depth/12245
https://community.snaplogic.com/t/recursive-logic-in-snaplogic/12921

Thanks,
Pero M.

[darshthakkar]

Thank you @pmancevski for sharing those links. I believe that would be a lot to check on @rashmi’s end.
Instead, lemme loop in the experts who can help her on this one.

CC: @dmiller @koryknick @ptaylor @bojanvelevski @siwadon

[rashmi]

Thank you so much @darshthakkar,@pmancevski

[koryknick]

@rashmi - this is a fairly complicated issue for a couple reasons. First, SnapLogic does not have a snap to read multiple sheets into a single stream. Second, the Excel Multi Sheet Formatter requires pre-determined, named views as input to write multiple sheets. So if you have input files with different number and names of sheets, there is no out of the box solution for you here.

This does not mean there is no hope; it’s just a more technical discussion. Excel files are basically zip archives, which you can read using the ZipFile Read snap and use a Binary Router snap to check the
$['content-location'] value for the first ‘xl/worksheets/*’ reference. From here, it will take some work on your part on how you can remove that string from the content and push it back with the rest of the Excel file content into the ZipFile Write to reconstitute the file.

This work is more than I can spend time to provide a working example for you here, but is an interesting problem. I hope you share your findings!