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:
- 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)
- 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:
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.
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).
$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.
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
,
inside fields protected by quotes, we need to know this.uniq
), or if you don't mind it being sorted (usesort -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