Forum Discussion

MallikarjunNuli's avatar
MallikarjunNuli
New Contributor
8 years ago

How to read Multiple Sheets from an excel dynamically using Snaplogic

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

pipeline

9 Replies

  • erase_ego's avatar
    erase_ego
    New Contributor II
    5 years ago

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

    • Puru's avatar
      Puru
      New Contributor II
      3 years ago

      Hi, Can you post an example for this solution.

      Thank you

  • smudassir's avatar
    smudassir
    Employee
    3 years ago

    Hi, if this is a google spreadsheet then consider using Spreadsheet Browser (that lists all the spreadsheets along with their worksheets as per the given filter) snap, followed by the Worksheet Reader (that reads a specific given sheet) snap. Supply values from the browser to the reader snap.

    And spreadsheet snappack is a premium snappack. FYI.

  • aditya_sharma's avatar
    aditya_sharma
    New Contributor III
    8 years ago

    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's avatar
      tgarcia
      New Contributor II
      7 years ago

      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's avatar
        Puru
        New Contributor II
        3 years ago

        can we get a sample pipeline of this example?

  • Jordan1211's avatar
    Jordan1211
    New Contributor
    4 years ago

    I was able to use this for parsing multiple sheets but it errors if a sheet does not exist. Does anyone know of a way to ignore the specific error for if a sheet is blank and continue processing?

      • Jordan1211's avatar
        Jordan1211
        New Contributor
        4 years ago

        What I did was used the error view plus a router that treated the error I was receiving different than all others. ($error.contains(“Sheet with the provided name does not exist:”)). Which basically classified it as an “Informational Error”. This works for now. I am just wondering if there are other options.