Read File line to line

lebela

Hi every body. I want to read a excel file line to line.

sgenzer

hi @lebela can you a bit more specific? What exactly are you trying to do?

lebela

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

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

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

thanks