0

Connecting multiple Excel selections to Python script 'easily'

I want to use an existing Python function to do calculations on my data in an Excel file in Pyramid. The script uses multiple inputs from the Excel file, which are given in the separate spreadsheets of the xls file.

I think I understand how to slightly modify the Python script so that it can be used by Pyramid, so that is not my issue at the moment. Oddly enough, the bottleneck is connecting the xls source to the Python script...

I tried the following:
1.  Connect the xls source directly to the script. Not possible since 'Data Source can only be connected to Select, Query or MultiSelect nodes'.
2.  Using the regular 'Select' to select the data in the separate tabs. For my file, this leads to 5 selections (see attached png). I want to make a connection from all five selections into the Python block, but this is not possible. It seems that I could use a 'Join' block to join the separate selections, but it can only join 2 selections at a time. So then I'd have to join in 4 steps, which is ridiculous.
3.  Using a 'Multi Select' block (see attached png). This indeed selects the data from all the separate tabs into one 'connection'. However... 'MultiSelect can only be connected to destination node', hence I cannot connect it to the Python block...


Are there any ideas on how to 'easily' connect the xls source to the Pyhon script? I mean, such a connection should be quite straight forward in my opinion...

(Note: I erased the names of the Excel file and the selections for privacy reasons)

1 reply

null
    • Senior Director of Product
    • Ian_Macdonald
    • 5 yrs ago
    • Reported - view

    Hi Frank,

    Just duplicate the Python Block and connect it to each of the select blocks for each of your Excel tabs.

    Multiselect block is designed for when you have multiple tables of data upon which you are doing no transformations and simply loading directly into a target database. Used to simplify the data flow diagram.

    If your Excel tabs contain the same data layouts, i.e. the column names and positions are identical, then use the UNION block to join all the tabs together, then process the unionised data with your Python block.

    Hope that helps.

    Ian

Content aside

  • Status Answered
  • 5 yrs agoLast active
  • 1Replies
  • 47Views
  • 2 Following