1

I have a large CSV file (300MB+) and I want to remove only columns 2,3 and 6-8 using Perl AND remove duplicated rows:

Note 1: all columns are separated by , (commas) but sometimes my cell values contains a , or multiple , and are separated by " (see last row, columns 9 and 10); thus I'd like still be able to process the input.csv file even if it has , inside the cells:

Note 2: I've added links to the input.csv and output.csv files:

input.csv

Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10
info 1,info 2,info 3,...,info 10
address 1,address 2,....,address 10
city 1,city 2,city 3,city 4,city 5,city 6,city 7,city 8,"city 9, extra","city 10, new"

output.csv

Col1,Col4,Col5,Col9,Col10
info 1,info 4,info 5,info 9,info 10
address 1,address 4,address 5,address 9,address 10
city 1,city 4,city 5,"city 9, extra","city 10, new"

I've found a Perl command that would remove the last column using regex, but don't know if it's good enough or how to tweak it to suit my case (any other suggestion is more than welcomed!):

perl -pe 's/.*\K,.*//'

Is it possible to remove only columns 2,3 and 6-8 using Perl and remove any duplicated rows?

PS: updated the input.csv file to include duplicate rows as well

Thank you!

5
  • Please edit your question and make sure you show us an example that accurately recreates your file. We need to be able to test our solutions and we can't do that with the file you have given us. Also, if this is a complex CSV with , inside fields protected by quotes, we need to know this.
    – terdon
    Mar 14, 2022 at 9:24
  • @terdon I will update my question, thank you!
    – Lernisios
    Mar 14, 2022 at 11:45
  • question was updated with more accurate info and links to input.csv and output.csv files
    – Lernisios
    Mar 14, 2022 at 12:03
  • Removing duplicates is simple if the input is already sorted (pipe through uniq), or if you don't mind it being sorted (use sort -u). Otherwise your perl script will need to remember each line that it has seen (e.g. in a hash variable, %seen is a good name for this purpose), which can take significant amounts of RAM (300MB input file is unlikely to be a problem on any modern system). If there is a unique identifier in each row, you can use that, otherwise you'll probably have to use the entire row - e.g. next if $seen{$_}++; or perhaps an md5sum of the row to reduce RAM at cost of CPU
    – cas
    Mar 14, 2022 at 23:53
  • thanks a lot @cas , yes, there's no unique identifier for my rows, so i will have to use the entire row
    – Lernisios
    Mar 15, 2022 at 0:14

2 Answers 2

2

The easiest way to do this would be to use miller aka mlr, which is a great tool for working with data in CSV, json, and a few other input or output formats. For example:

$ mlr --csv --implicit-csv-header --headerless-csv-output \
    cut -x -f 2,3,6,7,8 \
    then uniq -a input.csv  
Col1,Col4,Col5,Col9,Col10
info 1,info 4,5,9,info 10
address 1,4,5,9,address 10
city 1,4,5,9,city 10

Using both the --implicit-csv-header and --headerless-csv-output options effectively ignores the header line (i.e. treat it the same as the other data lines) and allow me to specify the fields to be cut by number rather than by name.

I had to edit your sample input.csv file to add some junk data in the missing fields. mlr would have complained otherwise. I also added a duplicate input line to test that the dupe elimination was working.

$ cat input.csv 
Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10
info 1,info 2,info 3,info 4,5,6,7,8,9,info 10
info 1,info 2,info 3,info 4,5,6,7,8,9,info 10
address 1,address 2,3,4,5,6,7,8,9,address 10
city 1, city 2,3,4,5,6,7,8,9,city 10

If you want to do it with perl:

  1. If you only need to handle simple comma-delimited input:
$ perl -F, -lane '
  next if $seen{$_}++;
  splice @F,5,3;
  splice @F,1,2;
  print join ",", @F' input.csv
Col1,Col4,Col5,Col9,Col10
info 1,info 4,5,9,info 10
address 1,4,5,9,address 10
city 1,4,5,9,city 10

This uses perl's -a option to auto-split each input line into an array called @F. The -F option tells it what delimiter to use.

Note 1: perl arrays start from zero, not one...so array element 5 is column 6. splice @$row, 5, 3 removes three elements from the array starting from element 5 (i.e. columns 6,7,8). See perldoc -f splice for details.

Note 2: I am deleting the columns in reverse order here (i.e. higher-numbered columns before lower-numbered). Otherwise, if I deleted columns 2 & 3 before deleting columns 5,6,7, the first deletion would cause those columns to be renumbered (to 3,4,5)

  1. Using Text::CSV to handle any valid CSV (including things like multi-line quoted columns containing commas):
$ perl -MText::CSV -e '
  my $csv = Text::CSV->new();
  while (my $row = $csv->getline(*ARGV)) {
    next if $seen{join ",", @$row}++;
    splice @$row, 5, 3;
    splice @$row, 1, 2;
    $csv->say(*STDOUT, $row);
  }' input.csv
Col1,Col4,Col5,Col9,Col10
"info 1","info 4",5,9,"info 10"
"address 1",4,5,9,"address 10"
"city 1",4,5,9,"city 10"

There are four things worth noting here:

  1. Text::CSV is not a core perl module, so it needs to be installed. It's packaged for most, if not all, Linux distributions. e.g. on Debian, you can install it with sudo apt-get install libtext-csv-perl. Otherwise, you can install it with the cpan command which comes with perl.

  2. Text::CSV's getline() method (as in $row = $csv->getline(*ARGV) above) returns a reference to an array, or arrayref. That's a scalar value that points to an entire array (see man perlref and man perldata for more info).

  3. $row in the code above contains the arrayref. Using/manipulating $row works on the reference itself, not the data it is referencing. So, e.g., $row2 = $row makes a copy of the reference, not the data. Both refs point to the same data. @$row "de-references" the arrayref as an array, so that it can be used just like any other array.

  4. The *ARGV in getline(*ARGV) is a special file handle that reads input from ALL filename arguments given on the command line (which are stored in an array called @ARGV in perl). It is assumed that non-filename arguments (e.g. options, if your script has code to handle options) have already been processed and removed from @ARGV. Filenames that don't exist or can't be opened (e.g. due to permissions) will produce an error message. In short, it reads from one or more filenames that you give it. An argument of - is treated as stdin, so it can read input from file(s), stdin, or both.

This is a really simple & primitive example of what Text::CSV is capable of and how it can be used. Read the man page for more details and examples.

As you can see in the sample output above, Text::CSV will, by default, quote text fields if they contain a space. If you don't want it to do that, you can override that by setting the quote_space attribute to zero....either when you create the $csv object with the new method:

my $csv = Text::CSV->new({ quote_space => 0 });

or afterwards:

my $csv = Text::CSV->new();
$csv->quote_space(0);

The output would then be like this:

Col1,Col4,Col5,Col9,Col10
info 1,info 4,5,9,info 10
address 1,4,5,9,address 10
city 1,4,5,9,city 10
3
  • thanks a lot @cas!!! both methods 1 & 2 worked great and deduplicated the rows and removed the columns even when having commas in some cells. Can you please confirm I got this right? so this command splice @$row, 5, 3; will remove Col 6 (which is actually Col 5 in Perl) and the next 3 columns, so it'd remove Col 6,7,8; next remove Col 2,3 so this command splice @$row, 1, 2; removes Col 1 (which is actually Col 2 in Perl) and the next 2 columns. I've made a screenshot here with this here link, is it correct?
    – Lernisios
    Mar 20, 2022 at 8:30
  • Not exactly. it deletes three columns, not four. splice @$row, 5, 3 will remove three columns starting from the sixth column (with column numbering starting from zero) , not col 6 and the next 3 cols, so columns 6-8 not 6-9. This form of the splice function is summarised as splice ARRAY,OFFSET,LENGTH. There are other forms that allow splice to be used to insert or replace columns. See perldoc -f splice for more details. Really, the only slightly tricky thing you need to remember is that perl arrays start from zero, and that's common to lots of languages, including C and bash.
    – cas
    Mar 20, 2022 at 23:01
  • thank you so much for the explanation @cas , greatly appreciated! got it!
    – Lernisios
    Mar 21, 2022 at 1:47
2

Turn it into an array, think about, recreate it as a csv:

perl -pe '@c = split(","); splice(@c, 1, 2); splice(@c, 3, 3); $_ = join(", ", @c)

If you're fields are quoted, you can use Text::CSV:

$ cat in.csv 
Col1,Col2,Col3,Col4,Col5
one,two,three,four,five
six,"se,ven","ei,ght",nine,ten
$ perl -MText::CSV -e 'Text::CSV::csv( in => "in.csv", headers => false, on_in => sub { splice( @{@_[1]}, 1, 2) } )'
Col1,Col4,Col5
one,four,five
six,nine,ten

You ask about perl, but for the sake awareness also consider the cut tool: cut -f '1,4,5,9,10' -d ,

10
  • so I tried cut but if the CSV has commas in it , then it ends up not processing the whole file and stopping
    – Lernisios
    Mar 14, 2022 at 2:44
  • I've also seen other users use this module metacpan.org/pod/Text::CSV to read and write CSV files in Perl which is apparently great for CSV files, but I don't know how to use it; ideally i'd do it through a perl script that uses that module
    – Lernisios
    Mar 14, 2022 at 2:54
  • OK, yes. if your fields have embedded ','s (presumably wrapped in "s), life get's a bit harder. The doc for that module looks pretty good, with some examples that should get you going pretty quick. Mar 14, 2022 at 3:10
  • See edit for an example using Text::CSV. Mar 14, 2022 at 3:38
  • Thanks a lot @ garethhumphriesgkc. I tried this Perl terminal command to remove those specific columns and output to a new output.csv file $ perl -MText::CSV -e 'Text::CSV::csv( in => "input.csv", headers => false, on_in => sub { splice( @{@_[1]}, 1, 2, 3, 6, 7, 8) } )' >> output.csv but it's just adding numbers in the columns like 3,6,7,8 and not removing them
    – Lernisios
    Mar 14, 2022 at 4:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .