🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.
Join and Win

"read in csv file skip first n lines"

josef_frankUser: "josef_frank"
josef_frank
New Altair Community Member
Updated by Jocelyn

Hi,

when importing csv files, is there any method to skip lines in the beginning of a file?
Something that behaves like R's "read.table"-option "skip".

Quite some time I'd like to read in csv files that have a structure like windows' ini-files:

[Header]
some garbage here
some non-relevant info here
[Data]
Var1,Var2,Var3,...
0,1,0,...
....

I'd like to start reading the file in the line "Var1,Var2,.."
Just can't find any option to get this done :-(

Best
Josef

Find more posts tagged with

AI Studio
CSV
Sort by:
1 - 14 of 141
    JEdwardUser: "JEdward"
    JEdward
    New Altair Community Member
    Hi Josef,

    I think step 3 of 5 of the import wizard offers this as you can set lines as name, comment or unit but I haven't used it before. 
    Give that a try and see if it does what you expect.

    Best,
    JEdward.

    el_chiefUser: "el_chief"
    el_chief
    New Altair Community Member
    ya, this has been annoying me as well. I usually delete the header line in notepad first.

    Simon, can you pleeeeeeeeeeeease fix this? And in Excel importer too

    josef.frank wrote:

    Hi,

    when importing csv files, is there any method to skip lines in the beginning of a file?
    Something that behaves like R's "read.table"-option "skip".

    Quite some time I'd like to read in csv files that have a structure like windows' ini-files:

    [Header]
    some garbage here
    some non-relevant info here
    [Data]
    Var1,Var2,Var3,...
    0,1,0,...
    ....

    I'd like to start reading the file in the line "Var1,Var2,.."
    Just can't find any option to get this done :-(

    Best
    Josef

    JEdwardUser: "JEdward"
    JEdward
    New Altair Community Member
    Hi guys,

    I gave a quick test with a csv with the following format:

    Line1- RandomText
    Line2 - [Var1], [Var2] (column headings)
    Line3 - RandomText
    Line4 - RandomText
    Line5 - RandomText
    Line6+ - [data1], [data2] (data)

    The below process worked fine for me.  What have I missed? 
    <process version="5.1.006">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.1.006" expanded="true" name="Process">
        <process expanded="true" height="370" width="145">
          <operator activated="true" class="read_csv" compatibility="5.1.006" expanded="true" height="60" name="Read CSV" width="90" x="45" y="210">
            <parameter key="csv_file" value="C:\Users\jedward\Desktop\RapidiCSVTest.csv"/>
            <parameter key="column_separators" value=","/>
            <parameter key="skip_comments" value="true"/>
            <parameter key="first_row_as_names" value="false"/>
            <list key="annotations">
              <parameter key="0" value="Comment"/>
              <parameter key="1" value="Name"/>
              <parameter key="2" value="Comment"/>
              <parameter key="3" value="Comment"/>
              <parameter key="4" value="Comment"/>
              <parameter key="5" value="Comment"/>
            </list>
            <list key="data_set_meta_data_information"/>
          </operator>
          <connect from_op="Read CSV" from_port="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>
    josef_frankUser: "josef_frank"
    josef_frank
    New Altair Community Member
    OP
    JEdward wrote:

    Hi JEdward

    The below process worked fine for me.  What have I missed?
    The suggested method promising at a first glance
    (had already tried that before the original post).
    This procedure however has two drawbacks:
    1st: it gets quite unwieldy when 80 or 100 lines have
    to be annotated manually.
    2nd: It just doesn't work as intended:

    Maybe youl'd like to try another file: 
    none1,none2,none3
    nothing
    var1,var2,var3,var4,var5
    1,2,3,4,5
    2,3,4,5,6
    3,4,5,6,7
    When annotating the first two lines as comments and the 3rd line
    with "Name", in the example above, the result set starts indeed with var1,
    but the number of fields that are read in is still determined by the first line,
    so this time the number of fields is determined by a comment :o
    Perhaps I'm just using it incorrectly. Could you give any hint?

    Best Josef
    JEdwardUser: "JEdward"
    JEdward
    New Altair Community Member
    Hi Josef,

    Point 1.- I agree that it's unwieldy.  One way around this would be using Excel & concatenate formulae to build the number of lines that you need and then pasting it into the XML of the process. 
    For example::
    A                         B         C                                 D
    1 <parameter key=" 0       " value="Comment"/> =CONCATENATE(A1,B1,C1)
    2 <parameter key=" =B1+1 " value="Comment"/> =CONCATENATE(A2,B2,C2)
    3 <parameter key=" =B2+1 " value="Comment"/> =CONCATENATE(A3,B3,C3)
    4 <parameter key=" =B3+1 " value="Comment"/> =CONCATENATE(A4,B4,C4)
    Not the most elegant solution & having an option to type a range of rows in RapidMiner would be very nice.
     
    Point 2. -Ah! I see the problem, that's a pain.
    The below worked for me after I named the required columns in "data_set_meta_data_information".  Try this. 
    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="5.1.006">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.1.006" expanded="true" name="Process">
        <process expanded="true" height="370" width="279">
          <operator activated="true" class="read_csv" compatibility="5.1.006" expanded="true" height="60" name="Read CSV" width="90" x="179" y="165">
            <parameter key="csv_file" value="C:\Users\jedward\Desktop\TestFile.csv"/>
            <parameter key="column_separators" value=","/>
            <parameter key="skip_comments" value="true"/>
            <parameter key="first_row_as_names" value="false"/>
            <list key="annotations">
              <parameter key="0" value="Comment"/>
              <parameter key="1" value="Comment"/>
              <parameter key="2" value="Name"/>
            </list>
            <list key="data_set_meta_data_information">
              <parameter key="0" value="var1.true.integer.attribute"/>
              <parameter key="1" value="var2.true.integer.attribute"/>
              <parameter key="2" value="var3.true.integer.attribute"/>
              <parameter key="3" value="var4.true.integer.attribute"/>
              <parameter key="4" value="var5.true.integer.attribute"/>
            </list>
          </operator>
          <connect from_op="Read CSV" from_port="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>
    User: "Telcontar120"
    Telcontar120
    New Altair Community Member
    So, is it really true that in 6 years, no one has found a better way to solve this problem??? I just added a product idea for the "skip n lines" for Read CSV.
    This appears to be the only relevant thread from the forum using the obvious search terms.
    In the meantime, if anyone else has found a more effective way to do this that doesn't involve going into csv files manually to add comment characters (or simply delete the header lines), I would love to hear about it. @sgenzer, any tricks up your sleeve here?
    User: "sgenzer"
    sgenzer
    Altair Employee

    darn you, @Telcontar120 - now that you've thrown down the gauntlet I am going to tackle this.  You know my weakness for bizarre ETL puzzles.  :)  Stay tuned.

     

    Scott

     

    User: "sgenzer"
    sgenzer
    Altair Employee

    how's this?  test2.csv is attached

     

    <?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="7.6.001" expanded="true" name="Process">
        <process expanded="true">
          <operator activated="true" class="set_macro" compatibility="7.6.001" expanded="true" height="68" name="Set Macro" width="90" x="45" y="34">
            <parameter key="macro" value="skip"/>
            <parameter key="value" value="4"/>
            <description align="center" color="transparent" colored="false" width="126">set n rows to %{skip}</description>
          </operator>
          <operator activated="true" class="read_csv" compatibility="7.6.001" expanded="true" height="68" name="Read CSV" width="90" x="179" y="34">
            <parameter key="csv_file" value="/Users/GenzerConsulting/Desktop/test2.csv"/>
            <parameter key="column_separators" value=","/>
            <parameter key="first_row_as_names" value="false"/>
            <list key="annotations"/>
            <list key="data_set_meta_data_information"/>
          </operator>
          <operator activated="true" class="extract_macro" compatibility="7.6.001" expanded="true" height="68" name="Extract Macro" width="90" x="313" y="34">
            <parameter key="macro" value="NEx"/>
            <list key="additional_macros"/>
            <description align="center" color="transparent" colored="false" width="126">get number of examples %{NEx}</description>
          </operator>
          <operator activated="true" class="generate_macro" compatibility="7.6.001" expanded="true" height="82" name="Generate Macro" width="90" x="447" y="34">
            <list key="function_descriptions">
              <parameter key="startRow" value="1+eval(%{skip})"/>
            </list>
            <description align="center" color="transparent" colored="false" width="126">%{startRow} = skip + 1</description>
          </operator>
          <operator activated="true" class="filter_example_range" compatibility="7.6.001" expanded="true" height="82" name="Filter Example Range" width="90" x="581" y="34">
            <parameter key="first_example" value="%{startRow}"/>
            <parameter key="last_example" value="%{NEx}"/>
            <description align="center" color="transparent" colored="false" width="126">filter startRow to NEx</description>
          </operator>
          <operator activated="true" class="rename_by_example_values" compatibility="7.6.001" expanded="true" height="82" name="Rename by Example Values" width="90" x="715" y="34"/>
          <connect from_op="Read CSV" from_port="output" to_op="Extract Macro" to_port="example set"/>
          <connect from_op="Extract Macro" from_port="example set" to_op="Generate Macro" to_port="through 1"/>
          <connect from_op="Generate Macro" from_port="through 1" to_op="Filter Example Range" to_port="example set input"/>
          <connect from_op="Filter Example Range" from_port="example set output" to_op="Rename by Example Values" to_port="example set input"/>
          <connect from_op="Rename by Example Values" 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>
    test2.csv
    User: "Telcontar120"
    Telcontar120
    New Altair Community Member

    Brilliant, I really appreciate you working on this @sgenzer!  Very creative use of macros to do this.  This approach is promising, but I think it only works if there are enough delimiters in the first line so it reads the correct number of columns.  If you modify the csv so the first line has only text (unfortunately more typical with title rows), then it only reads one column in, and the rest of the process works but only on that first column. And after a bit of testing it seems like it will generally read however many columns as it detects from the first row.  So if you can figure out how to tell RapidMiner how many columns to read (not based on the first row of data), then something like this should do the trick.  

    Happy Thanksgiving!

    test2 mod.csv
    User: "Telcontar120"
    Telcontar120
    New Altair Community Member
    Accepted Answer

    @sgenzer So Martin posted a comment in the related product idea that I think showed me the solution.  You can use the "Edit Annotations" parameter to manually specify that certain lines are to be treated as comments (even if they don't have the comment character, which is where I was getting hung up before).  See the following:

    annotations.PNG

    That effectively skips those lines on import and it starts reading on the specified line, and interprets the correct number of columns!  And at that point, the extra work with the macros to rename the attributes isn't needed either, since you can specify the relevant line to be read as names--but I'm sure going to save that process anyways because it could come in handy in other more complex data files in the future.  Thanks again!

    annotations.PNG
    User: "sgenzer"
    sgenzer
    Altair Employee
    <?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="7.6.001" expanded="true" name="Process">
        <process expanded="true">
          <operator activated="false" class="set_macro" compatibility="7.6.001" expanded="true" height="68" name="Set Macro" width="90" x="45" y="187">
            <parameter key="macro" value="skip"/>
            <parameter key="value" value="4"/>
            <description align="center" color="transparent" colored="false" width="126">set n rows to %{skip}</description>
          </operator>
          <operator activated="true" class="read_csv" compatibility="7.6.001" expanded="true" height="68" name="Read CSV" width="90" x="179" y="34">
            <parameter key="csv_file" value="/Users/genzerconsulting/Desktop/test2 mod.csv"/>
            <parameter key="first_row_as_names" value="false"/>
            <list key="annotations"/>
            <list key="data_set_meta_data_information"/>
          </operator>
          <operator activated="true" class="filter_examples" compatibility="7.6.001" expanded="true" height="103" name="Filter Examples" width="90" x="313" y="34">
            <list key="filters_list">
              <parameter key="filters_entry_key" value="att1.contains.,"/>
            </list>
          </operator>
          <operator activated="true" class="split" compatibility="7.6.001" expanded="true" height="82" name="Split" width="90" x="447" y="34"/>
          <operator activated="false" class="extract_macro" compatibility="7.6.001" expanded="true" height="68" name="Extract Macro" width="90" x="581" y="187">
            <parameter key="macro" value="NEx"/>
            <list key="additional_macros"/>
            <description align="center" color="transparent" colored="false" width="126">get number of examples %{NEx}</description>
          </operator>
          <operator activated="false" class="generate_macro" compatibility="7.6.001" expanded="true" height="82" name="Generate Macro" width="90" x="715" y="187">
            <list key="function_descriptions">
              <parameter key="startRow" value="1+eval(%{skip})"/>
            </list>
            <description align="center" color="transparent" colored="false" width="126">%{startRow} = skip + 1</description>
          </operator>
          <operator activated="false" class="filter_example_range" compatibility="7.6.001" expanded="true" height="82" name="Filter Example Range" width="90" x="849" y="187">
            <parameter key="first_example" value="%{startRow}"/>
            <parameter key="last_example" value="%{NEx}"/>
            <description align="center" color="transparent" colored="false" width="126">filter startRow to NEx</description>
          </operator>
          <operator activated="true" class="rename_by_example_values" compatibility="7.6.001" expanded="true" height="82" name="Rename by Example Values" width="90" x="983" y="34"/>
          <connect from_op="Read CSV" from_port="output" to_op="Filter Examples" to_port="example set input"/>
          <connect from_op="Filter Examples" from_port="example set output" to_op="Split" to_port="example set input"/>
          <connect from_op="Split" from_port="example set output" to_op="Rename by Example Values" to_port="example set input"/>
          <connect from_op="Extract Macro" from_port="example set" to_op="Generate Macro" to_port="through 1"/>
          <connect from_op="Generate Macro" from_port="through 1" to_op="Filter Example Range" to_port="example set input"/>
          <connect from_op="Rename by Example Values" 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>
    User: "sgenzer"
    sgenzer
    Altair Employee

    oh darn - of course I work on the next one and then see your note. Leave it to @mschmitz to one-up me.  :)  Perfect solution.

     

    Happy Thanksgiving.


    Scott

    matt_yauchUser: "matt_yauch"
    matt_yauch
    New Altair Community Member

    I don't see how the macros can manage a blank row in the csv file. Looking to skip the first 2 lines (first is comment, second is blank) without editing the csv file. I didn't have luck with blank row via annotations, but perhaps I overlooked a setting. Any help with skipping blank lines between comment and column names? 

    MartinLiebigUser: "MartinLiebig"
    MartinLiebig
    Altair Employee

    Hi @matt_yauch,

     

    you can manually say in the operator parameters to skip first x lines and just read it. RM will then automatically detect the att names etc. 

     

    BR,

    Martin

Quick Links