Hi sgenzer, thanks for your answer. I want to insert into tha database table the nformation off the Excel File. In this case, i think i need read the file line to line and make the sql sentence

sgenzer

Altair Employee

May 24, 2019

hmm ok that's still vague but basically if you want to get information from an Excel file line-by-line, you can use Loop Examples. Maybe something like this?

<?xml version="1.0" encoding="UTF-8"?><process version="9.2.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="6.0.002" expanded="true" name="Process" origin="GENERATED_TUTORIAL">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="-1"/>
    <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="read_excel" compatibility="9.2.001" expanded="true" height="68" name="Read Excel" width="90" x="45" 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="America/New_York"/>
        <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>
      <operator activated="true" class="loop_examples" compatibility="9.2.001" expanded="true" height="82" name="Loop Examples" origin="GENERATED_TUTORIAL" width="90" x="179" y="34">
        <parameter key="iteration_macro" value="example"/>
        <process expanded="true">
          <operator activated="true" class="filter_example_range" compatibility="9.2.001" expanded="true" height="82" name="Filter Example Range" width="90" x="45" y="34">
            <parameter key="first_example" value="%{example}"/>
            <parameter key="last_example" value="%{example}"/>
            <parameter key="invert_filter" value="false"/>
          </operator>
          <operator activated="true" class="jdbc_connectors:write_database" compatibility="9.2.001" expanded="true" height="68" name="Write Database" width="90" x="246" y="34">
            <parameter key="define_connection" value="predefined"/>
            <parameter key="database_system" value="MySQL"/>
            <parameter key="use_default_schema" value="true"/>
            <parameter key="overwrite_mode" value="none"/>
            <parameter key="set_default_varchar_length" value="false"/>
            <parameter key="default_varchar_length" value="128"/>
            <parameter key="add_generated_primary_keys" value="false"/>
            <parameter key="db_key_attribute_name" value="generated_primary_key"/>
            <parameter key="batch_size" value="1"/>
          </operator>
          <connect from_port="example set" to_op="Filter Example Range" to_port="example set input"/>
          <connect from_op="Filter Example Range" from_port="example set output" to_op="Write Database" to_port="input"/>
          <portSpacing port="source_example set" spacing="0"/>
          <portSpacing port="sink_example set" spacing="0"/>
          <portSpacing port="sink_output 1" spacing="0"/>
        </process>
      </operator>
      <connect from_op="Read Excel" from_port="output" to_op="Loop Examples" to_port="example set"/>
      <connect from_op="Loop Examples" from_port="example set" to_port="result 1"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="90"/>
      <portSpacing port="sink_result 2" spacing="0"/>
    </process>
  </operator>
</process>

Scott

rfuentealba

New Altair Community Member

May 26, 2019

Hi @lebela,

Check this:

Use the Read Excel operator, and connect it to the end.
Use the Select Attributes operator to select only the attributes you want from your Excel file.
Use the Rename operator to rename the attributes to the fields in your database.
Use the Generate Attributes operator if you need to generate a new attribute (unlikely).
Use the Write Database operator to write the data directly to the database.

You don't normally have to write line by line, as the Write Database operator does the INSERT process for you. There is another operator named Update Database if you have the ID and want to update the database with the values from your Excel files.

All the best,

Rod.

lebela

New Altair Community Member

May 31, 2019

thanks

🎉Community Raffle - Win $25

Read File line to line

Find more posts tagged with

Quick Links