combine some excel files

sara20

Hello

I need to read some excel rows in different excel files and make one new excel file with those rows. Now how is it possible with RM?

jacobcybulski

You can use several Read Excel operators to get your spreadsheets in, then Append, and Write Excel.

MarcoBarradas

Hi @sara20 you could use a Loop Files operator  with a Read Excel inside and and an Append and a Write Excel angt the end.
You could copy this basic setup
Inside the Loop Files you could do any type of data cleansing and filtering that you could need.

<?xml version="1.0" encoding="UTF-8"?><process version="9.7.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.7.001" 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="concurrency:loop_files" compatibility="9.7.001" expanded="true" height="82" name="Loop Files" width="90" x="313" y="34">
        <parameter key="filter_type" value="regex"/>
        <parameter key="filter_by_regex" value=".*xls"/>
        <parameter key="recursive" value="false"/>
        <parameter key="enable_macros" value="false"/>
        <parameter key="macro_for_file_name" value="file_name"/>
        <parameter key="macro_for_file_type" value="file_type"/>
        <parameter key="macro_for_folder_name" value="folder_name"/>
        <parameter key="reuse_results" value="false"/>
        <parameter key="enable_parallel_execution" value="true"/>
        <process expanded="true">
          <operator activated="true" class="read_excel" compatibility="9.7.001" expanded="true" height="68" name="Read Excel" width="90" x="246" y="34">
            <parameter key="sheet_selection" value="sheet number"/>
            <parameter key="sheet_number" value="1"/>
            <parameter key="imported_cell_range" value="A1"/>
            <parameter key="encoding" value="SYSTEM"/>
            <parameter key="first_row_as_names" value="true"/>
            <list key="annotations"/>
            <parameter key="date_format" value=""/>
            <parameter key="time_zone" value="SYSTEM"/>
            <parameter key="locale" value="English (United States)"/>
            <parameter key="read_all_values_as_polynominal" value="false"/>
            <list key="data_set_meta_data_information"/>
            <parameter key="read_not_matching_values_as_missings" value="true"/>
            <parameter key="datamanagement" value="double_array"/>
            <parameter key="data_management" value="auto"/>
          </operator>
          <connect from_port="file object" to_op="Read Excel" to_port="file"/>
          <connect from_op="Read Excel" from_port="output" to_port="output 1"/>
          <portSpacing port="source_file object" spacing="0"/>
          <portSpacing port="source_input 1" spacing="0"/>
          <portSpacing port="sink_output 1" spacing="0"/>
          <portSpacing port="sink_output 2" spacing="0"/>
        </process>
      </operator>
      <operator activated="true" class="append" compatibility="9.7.001" expanded="true" height="82" name="Append" width="90" x="447" y="34">
        <parameter key="datamanagement" value="double_array"/>
        <parameter key="data_management" value="auto"/>
        <parameter key="merge_type" value="all"/>
      </operator>
      <operator activated="true" class="write_excel" compatibility="9.7.001" expanded="true" height="103" name="Write Excel" width="90" x="581" y="34">
        <parameter key="file_format" value="xlsx"/>
        <enumeration key="sheet_names"/>
        <parameter key="sheet_name" value="RapidMiner Data"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
        <parameter key="number_format" value="#.0"/>
        <parameter key="encoding" value="SYSTEM"/>
      </operator>
      <connect from_op="Loop Files" from_port="output 1" to_op="Append" to_port="example set 1"/>
      <connect from_op="Append" from_port="merged set" to_op="Write Excel" to_port="input"/>
      <connect from_op="Write Excel" from_port="file" 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>

sara20

jacobcybulski,                         
MarcoBarradas,

Hello 

Thank you very much for your answers. I will try them.

Best regards
Sara