Due to recent updates, all users are required to create an Altair One account to login to the RapidMiner community. Click the Register button to create your account using the same email that you have previously used to login to the RapidMiner community. This will ensure that any previously created content will be synced to your Altair One account. Once you login, you will be asked to provide a username that identifies you to other Community users. Email us at Community with questions.

Wrinting an excel file with multiple sheets

mandeep_kumarmandeep_kumar Member Posts: 2 Contributor I
edited December 2018 in Help

Hi

 I'm looking for an option to write an excel/csv file with multiple sheets in it. The current "Write Excel" operator in Rapidminer can only write to a single sheet (under sheet name).

 

Is it possible to merge 2 excel files with different tab names into 1 file containing data from all tabs (across files) in Append mode ?

 

Thanks

Mandeep   

Tagged:

Answers

  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager

    hello @mandeep_kumar - welcome to the community. There are several threads on this topic including one exactly about this two weeks ago. Did you do a search? :) 

     

    https://community.rapidminer.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&q=excel%20multiple%20sheets

     

    Scott

     

  • mandeep_kumarmandeep_kumar Member Posts: 2 Contributor I

    @sgenzer 

      I have gone through these threads and i don't think there is any option except to have Execute R/python script that can perform.

    Do you know if there are any operators (in rapidminer) that can write data in multiple sheets in a single excel file.

     

    thanks

    Mandeep

     

  • lionelderkrikorlionelderkrikor RapidMiner Certified Analyst, Member Posts: 1,195 Unicorn

    Hi @mandeep_kumar,

     

    To perform this task, you will need of Advanced Reporting Extension (to download and install from the MarketPlace).

    Here a process, where 2 exampleSets are written in two differents sheets of a same Excel file.

    <?xml version="1.0" encoding="UTF-8"?><process version="8.0.001">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="8.0.001" expanded="true" name="Process">
        <process expanded="true">
          <operator activated="true" class="operator_toolbox:create_exampleset_from_doc" compatibility="0.7.000" expanded="true" height="68" name="ExampleSet_1" width="90" x="45" y="34">
            <parameter key="Input Csv" value="Att1,Att2&#10;1,4&#10;2,5&#10;3,6"/>
          </operator>
          <operator activated="true" class="operator_toolbox:create_exampleset_from_doc" compatibility="0.7.000" expanded="true" height="68" name="ExampleSet_2" width="90" x="45" y="136">
            <parameter key="Input Csv" value="Att1,Att2&#10;1,8&#10;2,10&#10;3,12"/>
          </operator>
          <operator activated="true" class="collect" compatibility="8.0.001" expanded="true" height="103" name="Collect" width="90" x="179" y="85"/>
          <operator activated="true" class="loop_collection" compatibility="8.0.001" expanded="true" height="82" name="Loop Collection" width="90" x="313" y="85">
            <parameter key="set_iteration_macro" value="true"/>
            <parameter key="macro_name" value="sheetNumber"/>
            <process expanded="true">
              <operator activated="true" class="rmx_adv_reporting:open_report_excel" compatibility="2.1.693" expanded="true" height="103" name="Open Report (2)" width="90" x="447" y="34">
                <parameter key="form_template_file" value="C:\Users\Lionel\Documents\Formations_DataScience\Rapidminer\Tests_Rapidminer\Test_export_Excel_Extension\RM_Export_Excel.xlsx"/>
                <parameter key="target_file" value="C:\Users\Lionel\Documents\Formations_DataScience\Rapidminer\Tests_Rapidminer\Test_export_Excel_Extension\RM_Export_Excel.xlsx"/>
                <process expanded="true">
                  <operator activated="true" class="rmx_adv_reporting:write_data_entry_excel" compatibility="2.1.693" expanded="true" height="68" name="Write Data Entry (2)" width="90" x="514" y="34">
                    <parameter key="sheet" value="%{sheetNumber}"/>
                    <parameter key="fit_to_range" value="true"/>
                    <parameter key="ignore_names" value="false"/>
                  </operator>
                  <connect from_port="input 1" to_op="Write Data Entry (2)" to_port="example set"/>
                  <connect from_op="Write Data Entry (2)" from_port="exampe set" to_port="output 1"/>
                  <portSpacing port="source_input 1" spacing="0"/>
                  <portSpacing port="source_input 2" spacing="0"/>
                  <portSpacing port="sink_output 1" spacing="0"/>
                  <portSpacing port="sink_output 2" spacing="0"/>
                </process>
              </operator>
              <connect from_port="single" to_op="Open Report (2)" to_port="input 1"/>
              <connect from_op="Open Report (2)" from_port="output 1" to_port="output 1"/>
              <portSpacing port="source_single" spacing="0"/>
              <portSpacing port="sink_output 1" spacing="0"/>
              <portSpacing port="sink_output 2" spacing="0"/>
            </process>
          </operator>
          <connect from_op="ExampleSet_1" from_port="output" to_op="Collect" to_port="input 1"/>
          <connect from_op="ExampleSet_2" from_port="output" to_op="Collect" to_port="input 2"/>
          <connect from_op="Collect" from_port="collection" to_op="Loop Collection" to_port="collection"/>
          <connect from_op="Loop Collection" from_port="output 1" to_port="result 1"/>
          <portSpacing port="source_input 1" spacing="0"/>
          <portSpacing port="sink_result 1" spacing="0"/>
          <portSpacing port="sink_result 2" spacing="0"/>
        </process>
      </operator>
    </process>

    NB : You have to create preliminarily in the Excel file, the sheets in which you want to write.

     

    I hope this help,

     

    Regards, 

     

    Lionel

  • Brian_WellsBrian_Wells Member Posts: 6 Contributor II
    So it appears that this is still a shortcoming even in RM 9.2 and I have to say that I am stunned by such a glaring omission in the base feature set.  If I did not love RapidMiner Studio so much I probably wouldn't care but simply use Alteryx or some other tool when I need to do large scale data reporting.  Given how common the format is, how much it is used throughout most companies and the fact that there is no reasonable alternative format to Excel's collection of tabular data tables within a single file, this should be #1 on the feature enhancement list with no close second.  Having to purchase a third party extension to do this, even if the fee is nominal, is completely unacceptable for a piece of software that I pay $10,000/yr for, as is having to create my own operator using R or Apache POI, Python, etc.

    Please address this soon, guys.  I am fighting for wide-spread RapidMiner adoption in my company and I can guarantee that this will be a major stumbling block for a lot of folks.
  • kaymankayman Member Posts: 662 Unicorn
    edited March 2019
    If you don't mind using python this can be easily be achieved using the xlsxwriter package.
    I share you point that having it out of the box available would be a great thing, but till then this may help also.

    Find attached a working sample writing different datasets to different tabs 

    <?xml version="1.0" encoding="UTF-8"?><process version="9.1.000">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.1.000" expanded="true" name="Process">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="2001"/>
    <parameter key="send_mail" value="never"/>
    <parameter key="notification_email" value=""/>
    <parameter key="process_duration_for_mail" value="30"/>
    <parameter key="encoding" value="SYSTEM"/>
    <process expanded="true">
      <operator activated="true" class="generate_data" compatibility="9.1.000" expanded="true" height="68" name="Generate Data" width="90" x="112" y="136">
        <parameter key="target_function" value="random"/>
        <parameter key="number_examples" value="100"/>
        <parameter key="number_of_attributes" value="5"/>
        <parameter key="attributes_lower_bound" value="-10.0"/>
        <parameter key="attributes_upper_bound" value="10.0"/>
        <parameter key="gaussian_standard_deviation" value="10.0"/>
        <parameter key="largest_radius" value="10.0"/>
        <parameter key="use_local_random_seed" value="false"/>
        <parameter key="local_random_seed" value="1992"/>
        <parameter key="datamanagement" value="double_array"/>
        <parameter key="data_management" value="auto"/>
      </operator>
      <operator activated="true" class="generate_data" compatibility="9.1.000" expanded="true" height="68" name="Generate Data (2)" width="90" x="112" y="238">
        <parameter key="target_function" value="random"/>
        <parameter key="number_examples" value="100"/>
        <parameter key="number_of_attributes" value="5"/>
        <parameter key="attributes_lower_bound" value="-10.0"/>
        <parameter key="attributes_upper_bound" value="10.0"/>
        <parameter key="gaussian_standard_deviation" value="10.0"/>
        <parameter key="largest_radius" value="10.0"/>
        <parameter key="use_local_random_seed" value="false"/>
        <parameter key="local_random_seed" value="1992"/>
        <parameter key="datamanagement" value="double_array"/>
        <parameter key="data_management" value="auto"/>
      </operator>
      <operator activated="true" class="generate_data" compatibility="9.1.000" expanded="true" height="68" name="Generate Data (3)" width="90" x="112" y="340">
        <parameter key="target_function" value="random"/>
        <parameter key="number_examples" value="100"/>
        <parameter key="number_of_attributes" value="5"/>
        <parameter key="attributes_lower_bound" value="-10.0"/>
        <parameter key="attributes_upper_bound" value="10.0"/>
        <parameter key="gaussian_standard_deviation" value="10.0"/>
        <parameter key="largest_radius" value="10.0"/>
        <parameter key="use_local_random_seed" value="false"/>
        <parameter key="local_random_seed" value="1992"/>
        <parameter key="datamanagement" value="double_array"/>
        <parameter key="data_management" value="auto"/>
      </operator>
      <operator activated="true" class="python_scripting:execute_python" compatibility="9.1.000" expanded="true" height="145" name="Execute Python (2)" width="90" x="313" y="136">
        <parameter key="script" value="import pandas as pd&#10;import xlsxwriter&#10;import os&#10;&#10;user = os.getlogin()&#10;tpath = 'C:\\Users\\' + user + '\\data\\tmp'&#10;&#10;def rm_main(d3,d1,d2):&#10;&#10;    writer = pd.ExcelWriter(tpath + '/multitab.xlsx', engine='xlsxwriter')&#10;&#10;    d1.to_excel(writer, 'set1')  &#10;    d2.to_excel(writer, 'set2')&#10;    d3.to_excel(writer, 'set3')&#10;    # Save the result &#10;    writer.save()&#10;&#10;    return"/>
        <parameter key="use_default_python" value="true"/>
        <parameter key="package_manager" value="conda (anaconda)"/>
      </operator>

      <connect from_op="Generate Data" from_port="output" to_op="Execute Python (2)" to_port="input 1"/>
      <connect from_op="Generate Data (2)" from_port="output" to_op="Execute Python (2)" to_port="input 2"/>
      <connect from_op="Generate Data (3)" from_port="output" to_op="Execute Python (2)" to_port="input 3"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
    </process>
  </operator>
</process>


  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager
    stay tuned @Brian_Wells :wink:
Sign In or Register to comment.