perl - text processing - output to csv file

Question

Hi,

Is there a way in perl to export data from a file to csv file. What I mean is,

Say I have a file as follows..

field1=value1,filed2=value2
field1=value3,filed2=value4
field1=value5,filed2=value6

I want to export this to excel format as follows.

field1  field2
value1  value2
value3  value4
value5  value6

anyway of doing this??

Also, there is a little problem here. Suppose I export one more file to same CSV file...which has contents like..

field1=value1,fields2=value8

my CSV should look like

field1  field2
value1  value2
        value8
value3  value4
value5  value6

is there a simpler way to do it. Right now I am doing it manually. Any idea if there is a better way.

Thank you.

Answer 1

Take a look at Text::CSV (for reading your CSV files into perl data structures) and Spreadsheet::WriteExcel (for writing that data back out in Excel format).

Answer 2

Before you read in a new file, read in the target csv file (if it is not empty). Create a hash of hashes. The key for the first hash HashA will be the values of field1. The value for a key like value1 will be a hash HashB. HashB will have keys that are the values of field2 that correspond to value1. The value of a key in HashB will just be '1'. (It doesn't matter what it is.)

Now when you want to export a second file, you just add new keys to HashB for each key for HashA.

Here's your example.

First you do the first file to get

value1 -> { value2 -> 1 }
value3 -> { value4 -> 1 }
value5 -> { value6 -> 1 }

Now when you read in the second file with line field1=value1,field2=value8, you just add a key to HashB for value1

value1 -> { value2 -> 1, value8 -> 1 }
value3 -> { value4 -> 1 }
value5 -> { value6 -> 1 }

Now when you iterate over HashA you print the keys in column1 (for field1) and print each key from HashB.

#!/usr/bin/perl

%hashA = ();

$hashA{"value1"}{"value2"} = 1;
$hashA{"value3"}{"value4"} = 1;
$hashA{"value5"}{"value6"} = 1;

foreach $value1 ( sort keys %hashA ) {
    foreach $value2 ( sort keys %{ $hashA{$value1} } ) {
        print "$value1, $value2\n";
    }
}

$hashA{"value1"}{"value8"} = 1;

print "\n\n";
foreach $value1 ( sort keys %hashA ) {
    foreach $value2 ( sort keys %{ $hashA{$value1} } ) {
        print "$value1, $value2\n";
    }
}

Answer 3

Here you go

#!/usr/bin/env perl

open (IMPORT, "import.txt") || die "Unable to read import file";

my @lines = <IMPORT>;
my @formatted = ();

for my $line (@lines) {
    $line =~ s/^.*=(.*?),.*?=(.*?)/$1\t$2/g;
    push(@formatted, $line);
}

my $current_field;

for my $format_line (sort @formatted) {
    my($field1, $field2) = (split(/\t/, $format_line));
    if ($field1 ne $current_field) {
        print "$field1";
    }
    print "\t$field2";

    $current_field = $field1;
}

import.txt contains

field1=value1,filed2=value2
field1=value3,filed2=value4
field1=value5,filed2=value6
field1=value1,fields2=value8

which results in

value1  value2
        value8
value3  value4
value5  value6