How to define columns and lines in CSV files?

Question

I have two CSV files that look like this:

File1:

Name            Type     ID   Language  Text    Comment
App_Item125     normal   1       en      init   [empty]
App_Item167    good     66      fr      init     
App_Item1222    none     23      it      init

File2:

Name            SID
App_Item122     SID_Foo
App_Item1345    SID_Bar
App_Item1222    SID_None

I want to write a Perl script that inserts the SIDs from File2 into File1. SIDs should be put in the 5th column (named "Comment") of the row with the matching "Name" field. The first column of both files contains the same data but in a different order.

This is the code I wrote:

my @File2 = "File2.csv";
open(FILE2, @File2) || die "Could not open file";
my @File1 ="File1.csv";
open(FILE1, @File1) || die "Could not open file";

my $SID;

while( <FILE2>) {
    if($File2[0] eq $File1[0]) { # this is probably totally wrong: if the elements of the first columns of file2 match with the ones of File1
        $SID = $File1[1]; #this is the SID in the 2nd column of File2
        print $File2[5] $SID; #print that matching Element into column 6 of File2
    }
}

close FILE1;
close FILE3;
close FILE2;

My question is, how do I define columns and lines in CSV files in Perl?

There are no commas in this "comma-separated" data. Is it tab-separated, space-separated, or fixed-width columns? — Borodin
Don't use arrays to store your filenames. Use 3-argument open and lexical filehandles. Include the filename and error string in your die statements. my $file = 'foo.csv'; open my $fh, '<', $file or die "$file: $!"; — ThisSuitIsBlackNot

Miller Miller · Accepted Answer · 2014-04-07T18:22:29

A few style tips first.

Include use strict; and use warnings; at the top of every perl script.
Because you're doing file processing, include use autodie; at the top of your perl script as well.
Use lexical filehandles instead of type globs.
Use Text::CSV if you're actually processing CSV files. Based off what you've shown, they could be tab separated or just spacing separated.

As for how to approach this task:

Start with processing all of File2 and saving the key value pairs in a data structure, a hash.
Confirm you have this working properly before continuing.
Next create a loop for processing File 1. For tips how how to modify a file, take a look at: How do I change, delete, or insert a line in a file, or append to the beginning of a file?

To help you get started, here's how I'd probably do the processing for file2:

use strict;
use warnings;
use autodie;

my $file2 = 'foobar.txt';

open my $fh, '<', $file2;
<$fh>; # Skip header row;
my %hash;
while (<$fh>) {
    chomp;
    my ($key, $val) = split /\s+/, $_, 2;
    $hash{$key} = $val;
}

use Data::Dump;
dd \%hash;

How to define columns and lines in CSV files?

1 Answers