Forum Discussion

rashmi's avatar
rashmi
New Contributor III
3 years ago

Redshift queries with multiple create and 1 select

Hi All, Is it possible to run multiple create statement first to create multiple temp tables and select statement (redshift query) which has to then fetch columns from those temp tables and need to...
Aleksandar_A's avatar
Aleksandar_A
Contributor III
2 years ago
Copy Link

Hello neelamanikandan,

You can use the sl.ensureArray() function to ensure that the FeeReductionDetail field will always be an array.

Just place a Mapper before the Splitter with the following settings:

Expression: sl.ensureArray($FeeReduction.FeeReductionDetail)

Target Path: $FeeReduction.FeeReductionDetail

With Pass through turned on.

Let me know if this helps you.

Regards,

Aleksandar.

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

    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
      Copy Link

      Hi, Can you post an example for this solution.

      Thank you

  • smudassir's avatar
    smudassir
    Employee
    3 years ago
    Copy Link

    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
    Copy Link

    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
      Copy Link

      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
        Copy Link

        can we get a sample pipeline of this example?

  • Jordan1211's avatar
    Jordan1211
    New Contributor
    4 years ago
    Copy Link

    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
        Copy Link

        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.