Reading csv files with % and $ data

vans

I am trying to read a csv file.  The column names and first row of data are below. I need to convert data that contain % or $ to real numbers, not polynomials. How can I do this?

"id";"rank";"name";"employees";"previous";"revenues";"revenuechange";"profits";"profitschange";"assets";"marketvalue"

"2";"1";"Walmart";"2,300,000";"1";"$485,873";"0.8%";"$13,643.0 ";"-7.2%";"$198,825";"$218,619"

I am sure there is an easy way to do this, but I can't find it.  Thanks in advance.

jwpfau

Hi vans,

delete the Create Document and Write Document operators of Marco Barradas example and choose your local file for the csv file parameter of the Read CSV operator.

Check the LEARN section at the bottom of this webpage and/or complete the in-product tutorials to get a feeling for the process workflow.

Greetings,
Jonas

jwpfau

Hi vans,

Just remove the $ or % and convert it into a number you can do this with the the Replace and Nominal to Numerical operator.
Or you could use the Generate Attributes operator with it's replace and parse functions.

Greetings,
Jonas

Marco_Barradas

Hi @vans

Here is a little example on how you can do what you asked.

<?xml version="1.0" encoding="UTF-8"?><process version="9.10.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.10.001" expanded="true" name="Process">
    <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="text:create_document" compatibility="9.3.001" expanded="true" height="68" name="Create Document" width="90" x="112" y="187">
        <parameter key="text" value="&quot;id&quot;;&quot;rank&quot;;&quot;name&quot;;&quot;employees&quot;;&quot;previous&quot;;&quot;revenues&quot;;&quot;revenuechange&quot;;&quot;profits&quot;;&quot;profitschange&quot;;&quot;assets&quot;;&quot;marketvalue&quot;&#10;&quot;2&quot;;&quot;1&quot;;&quot;Walmart&quot;;&quot;2,300,000&quot;;&quot;1&quot;;&quot;$485,873&quot;;&quot;0.8%&quot;;&quot;$13,643.0 &quot;;&quot;-7.2%&quot;;&quot;$198,825&quot;;&quot;$218,619&quot;"/>
        <parameter key="add label" value="false"/>
        <parameter key="label_type" value="nominal"/>
      </operator>
      <operator activated="true" class="text:write_document" compatibility="9.3.001" expanded="true" height="82" name="Write Document" width="90" x="246" y="187">
        <parameter key="overwrite" value="true"/>
        <parameter key="encoding" value="SYSTEM"/>
      </operator>
      <operator activated="true" class="read_csv" compatibility="9.10.001" expanded="true" height="68" name="Read CSV" width="90" x="380" y="187">
        <parameter key="column_separators" value=";"/>
        <parameter key="trim_lines" value="false"/>
        <parameter key="use_quotes" value="true"/>
        <parameter key="quotes_character" value="&quot;"/>
        <parameter key="escape_character" value="\"/>
        <parameter key="skip_comments" value="false"/>
        <parameter key="comment_characters" value="#"/>
        <parameter key="starting_row" value="1"/>
        <parameter key="parse_numbers" value="true"/>
        <parameter key="decimal_character" value="."/>
        <parameter key="grouped_digits" value="false"/>
        <parameter key="grouping_character" value=","/>
        <parameter key="infinity_representation" value=""/>
        <parameter key="date_format" value=""/>
        <parameter key="first_row_as_names" value="true"/>
        <list key="annotations"/>
        <parameter key="time_zone" value="SYSTEM"/>
        <parameter key="locale" value="English (United States)"/>
        <parameter key="encoding" value="SYSTEM"/>
        <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"/>
      </operator>
      <operator activated="true" class="replace" compatibility="9.10.001" expanded="true" height="82" name="Replace" width="90" x="514" y="187">
        <parameter key="attribute_filter_type" value="all"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="nominal"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="file_path"/>
        <parameter key="block_type" value="single_value"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="single_value"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="replace_what" value="[%$]"/>
        <parameter key="replace_by" value=""/>
      </operator>
      <operator activated="true" class="parse_numbers" compatibility="9.10.001" expanded="true" height="82" name="Parse Numbers" width="90" x="648" y="187">
        <parameter key="attribute_filter_type" value="subset"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value="revenuechange|marketvalue|profits|profitschange|revenues"/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="nominal"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="file_path"/>
        <parameter key="block_type" value="single_value"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="single_value"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="decimal_character" value="."/>
        <parameter key="grouped_digits" value="true"/>
        <parameter key="grouping_character" value=","/>
        <parameter key="infinity_representation" value=""/>
        <parameter key="unparsable_value_handling" value="fail"/>
      </operator>
      <connect from_op="Create Document" from_port="output" to_op="Write Document" to_port="document"/>
      <connect from_op="Write Document" from_port="file" to_op="Read CSV" to_port="file"/>
      <connect from_op="Read CSV" from_port="output" to_op="Replace" to_port="example set input"/>
      <connect from_op="Replace" from_port="example set output" to_op="Parse Numbers" to_port="example set input"/>
      <connect from_op="Parse Numbers" from_port="example set output" 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>

vans

Thanks. I don't really understand, but I pasted the example into the process window and ran it. It produced the results I wanted for the example row, but I don't know how to get it to parse a file that contains many rows with the same formatting. I tried to link an external file to the process, but couldn't get it to work.  How can I read a text file from the repository or from a directory on my PC?

What is the best source of information on coding processes like these?

jwpfau

Hi vans,

delete the Create Document and Write Document operators of Marco Barradas example and choose your local file for the csv file parameter of the Read CSV operator.

Check the LEARN section at the bottom of this webpage and/or complete the in-product tutorials to get a feeling for the process workflow.

Greetings,
Jonas