pl c sv text format wrap

[edrew04]

Hi can you help me on creating a pl of c sv text format example:

AMS.csv

1,Go,Manuel,V.,1/22/2015,8:30AM,5:30PM,1001
2,Calinisan,Peter,S.,1/22/2015,8:30AM,5:30PM,1002
3,Sioting,Michael,S.,1/22/2015,8:30AM,5:30PM,1003

After running the ksh wrapper it should become like this:

2002,Go,Manuel,V.,1/22/2015,8:30AM,5:30PM,IT
2001,Calinisan,Peter,S.,1/22/2015,8:30AM,5:30PM,HR
2003,Calinisan,Peter,S.,1/22/2015,8:30AM,5:30PM,PURCHASING

here are my ff file names:

WRAPPER.KSH
./EXPORT_AMS.KSH -MOVE FROM SOURCE TO OPS FOLDER
./TRANSFORM_AMS_PPS.KSH - transform AMS format to PPS
./IMPORT_PPS.KSH - MOVE FROM OPS TO DEST

WRAPPER

TRANSFORM AMS.CSV TO PPS.CSV
AMS ---> PPS

[pme]

Hi edrew04

Welcome to the monastery.

At least two things are unclear to me:
- 'Sioting,Michael' is replaced with 'Calinisan,Peter'. Is it a typo?
- Where do 'IT', 'HR', 'PURCHASING' come from?

[edrew04]

yes it is a typo

here is where i should get the data

AMS.csv

1,Go,Manuel,V.,1/22/2015,8:30AM,5:30PM,1001
2,Calinisan,Peter,S.,1/22/2015,8:30AM,5:30PM,1002
3,Sioting,Michael,S.,1/22/2015,8:30AM,5:30PM,1003

1,2002
2,2001
3,2003

1001,IT
1002,HR
1003,PURCHASING

to become PPS.csv

2002,Go,Manuel,V.,1/22/2015,8:30AM,5:30PM,IT
2001,Calinisan,Peter,S.,1/22/2015,8:30AM,5:30PM,HR
2003,Sioting,Michael,S.,1/22/2015,8:30AM,5:30PM,PURCHASING

I had made my initial scripting here but it generally duplicates it

use strict;
use warnings;
use Text::CSV;
use Data::Dump qw(dump);

my @data;   # 2D array for CSV data
my $file = 'AMS.csv';

my $csv = Text::CSV->new({ binary => 1, quote_null => 0 });
open my $fh, '<', $file or die "Could not open $file: $!";

while( my $column = $csv->getline( $fh ) ) { 
#shift @$column;
push @data, $column;
}

open $fh, ">:encoding(utf8)", "PPS.csv" or die "PPS.csv: $!";
for (@data) {
$csv->print($fh, $_);
print $fh "\n";
}
close $fh or die "PPS.csv: $!";

[poj]

Try

use strict;
use warnings;
use Text::CSV;

my @data;   # 2D array for CSV data
my %dict=();

my $file = 'AMS.csv';
my $csv = Text::CSV->new({ binary => 1, quote_null => 0 });
open my $fh, '<', $file or die "Could not open $file: $!";

while( my $ar = $csv->getline( $fh ) ) { 
  if (@$ar > 2){
    push @data,$ar;
  } elsif (@$ar == 2) {
    $dict{$ar->[0]} = $ar->[1];    
  }
}

open $fh, ">:encoding(utf8)", "PPS.csv" or die "PPS.csv: $!";
for my $ar (@data) {
  $ar->[0] = $dict{$ar->[0]};
  $ar->[-1] = $dict{$ar->[-1]};
  $csv->print($fh, $ar);
  print $fh "\n";
}
close $fh or die "PPS.csv: $!";

poj

[pme]

Hi edrew04, here is a working version. The newly added lines are marked with '###'.

use strict;
use warnings;
use Text::CSV;

my @data;   # 2D array for CSV data
my $file = 'AMS.csv';

my %dept_code = (          ###
  1001 => 'IT',            ###
  1002 => 'HR',            ###
  1003 => 'PURCHASING',    ###
);

my $csv = Text::CSV->new({ binary => 1, quote_null => 0 });
open my $fh, '<', $file or die "Could not open $file: $!";

while( my $column = $csv->getline( $fh ) ) {
  push @data, $column;
}


open $fh, ">:encoding(utf8)", "PPS.csv" or die "PPS.csv: $!";
for (@data) {
  $_->[0] += 2000;                    ###
  $_->[7] = $dept_code{$_->[7]};      ###
  $csv->print($fh, $_);
  print $fh "\n";
}
close $fh or die "PPS.csv: $!";

[LanX]

What did you try?

[Anonymous Monk]

You should almost certainly use Text::CSV to handle the CSV input and output.

[karlgoethebier]

Your data seems a bit weird (different amount of columns) - but try this ugly solution using paragraph mode:

#!/usr/bin/env perl

use strict;
use warnings;
use Data::Dump;

my $file = qq(AMS.csv);

local $/ = "";

open( my $fh, "<", $file ) || die $!;
my @array = <$fh>;
close $fh;

# dd \@array;

my @one   = split /\n/, $array[0];
my @two   = split /\n/, $array[1];
my @three = split /\n/, $array[2];

# dd \@one, \@two, \@three;

my ( $result, $year, $department );

for my $item (@one) {
    if ( $item =~ /(^1,)/ ) {
        $item =~ s/$1//;
        $year       = ( split /,/, $two[0] )[1];
        $department = ( split /,/, $three[0] )[1];
        $result .= qq($year,$item,$department\n);
    }
    if ( $item =~ /(^2,)/ ) {
        $item =~ s/$1//;
        $year       = ( split /,/, $two[1] )[1];
        $department = ( split /,/, $three[1] )[1];
        $result .= qq($year,$item,$department\n);

    }
    if ( $item =~ /(^3,)/ ) {
        $item =~ s/$1//;
        $year       = ( split /,/, $two[2] )[1];
        $department = ( split /,/, $three[2] )[1];
        $result .= qq($year,$item,$department\n);

    }
}

#print $result;
open my $out, ">", "PPS.csv" || die $!;
print $out $result;
close $out;

__END__

karls-mac-mini:monks karl$ ./ksh.pl

karls-mac-mini:monks karl$ cat PPS.csv 
2002,Go,Manuel,V.,1/22/2015,8:30AM,5:30PM,1001,IT
2001,Calinisan,Peter,S.,1/22/2015,8:30AM,5:30PM,1002,HR
2003,Sioting,Michael,S.,1/22/2015,8:30AM,5:30PM,1003,PURCHASING

Update: I skipped the wrapper thing, OK...

Update2: Actually it isn't "ugly".

Best regards, Karl