How to read Multiple Sheets from an excel dynamically using Snaplogic

[MallikarjunNuli]

Hi ,

I have requirement to read a single sheet at a time and load the data in to a table from the same excel file which contains more than 40+ of sheets. Is it possible to do this through Snaplogic. Can some one suggest.

Thank You,
Mallikarjun

[aditya_sharma]

If you have little bit of flexibility in implementation, you can split that single excel file with multiple sheets into multiple excel files using few lines of code in Python (Note: Assuming schema is same in each sheet) and then you can just use multi file reader to read the excel files.

[tgarcia]

I ran into this same problem, and found a solution for my scenario. For this, you’ll need to know the names of the sheets that will be in the workbook, or the maximum number of sheets in the workbook. In my example, I knew the names of all the sheets I would need to read.
This process requires a child pipeline since the excel parser can only use sheet names from parameters and not values passed from an upstream document.

My process was:

• Read excel file via REST GET
• Generate list of all sheet names I want to read with a JSON Generator. If using sheet numbers, generate a numeric sequence instead.
• Cross join the file to the list of sheets to read by setting the join condition of 1=1
• Use a pipeline execute to call a child pipeline and pass the sheetname or number as a parameter
• In the child pipeline, read the excel sheet specified by the parameter. If its unknown if the sheet name or number is in that workbook,set your error settings in the parser to ignore errors.

[Puru]

can we get a sample pipeline of this example?

[erase_ego]

A FileReader snap that reads Excel from a URL. A Binary Router which splits the Output as shown below might work