How can run an operation on one column of an CSV file in Perl?

Question

Hi,

I need to parse and convert an entire column in a simple CSV file that has two columns.

John, 128222971326628000
Paul, 128491909317205000
Greg, 128160037933161000

Basically I need to perform some arithmetic on the second column and update the value in the column with the result of this operation while leaving the first column untouched.

Can you please give me some hints on how would I go about doing this?

I have looked at some examples and none of them explains how to update an entire column.

Thank you in advance for your help.

Answer 1

It sounds like you need Tie::File:

#!/usr/bin/perl

use strict;
use warnings;

use Tie::File;

my $filename = shift; #get filename from commandline

tie my @records, "Tie::File", $filename
    or die "could not open $filename: $!";

for my $record (@records) {
    my @rec = split /, /, $record;
    $rec[1] /= 2; #reduce the second column by half
    #prevent perl from using scientific notation
    $rec[1] = sprintf "%18.0f", $rec[1]; 
    $record = join ", ", @rec;
}

Answer 2

It sort of depends on the arithmetic you want to do, but you might be able to get away with something like this. Here's a command line example that adds 1 to every number in the second column of the file.

perl -a -n -i -e '$F[1]++;print "$_ " for @F;print "\n"' csv_file

Note though that this is splitting on the space in your example, rather than on the ','.

Answer 3

In addition to the answers you have already, I would note that the size of the numbers could pose a problem for you: you might lose some precision when you try to perform math (that's what happened on my system when I ran the answer by Chas. Owens, for example). If that's a problem on your system, you can look into Perl's modules for working with large numbers: Math::Big, Math::BigInt, etc.

Answer 4

1. Use Text::CSV to extract the data (for complex CSV files) http://search.cpan.org/~makamaka/Text-CSV-1.13/lib/Text/CSV.pm

2.Do whatever you need to do.

1. Write it back to file

Hope this helps