glly
Reputation: 107
Transpose select lines in a CSV via Perl
I know there are already threads about this already (This and This) but I can't seem to edit This script to only return certain rows in a CSV. I will point out that I am relatively new to Perl and I am/have read the Perl docs for I/O but I can't seem to see what is wrong with my script.
My script currently looks like the following:
#!/usr/local/bin/perl
use strict;
use warnings 'all';
my @rows = ();
my @pivot = ();
open (FILE, "EDCNDC-Daily-Volume-Forecast_Ops Forecast by Shift.csv") or die $!; #Opens File or returns error thrown ($!)
while (<FILE>) {
if ($. < 4 ) {print ""}
elsif ($. > 8) {print ""}
elsif ($_ =~ /^\,\,\,\,/) {print substr($_,4)}
else {
chomp;
push @rows, [split /,/];
}
}
for my $row (@rows) {
for my $column (0 .. $#{$row}) {
push(@{$pivot[$column]}, $row->[$column]);
}
}
for my $newrow (@pivot) {
for my $newcol (@{$newrow}) {
print $newcol,",";
}
print "\n";
}
close FILE or die $!;
This returns the following:
Site Activity,MI Report Outage 14:30 - 15:30,03:00 - 09:00 - Level 0 Diverter Installation - SCS Loop,03:00 - 09:00 - Level 1 Diverter Installation - SCS Loop,0,0,0,0,MI Report Outage 14:30 - 15:30,0,WCS/WMS Release 16.5 - Outage 03:00 - 11:00,0,0,0,0,MI Report Outage 14:30 - 15:30,0,0,0,0,0,0,MI Re
port Outage 14:30 - 15:30,0,0,0,0,0,0,MI Report Outage 14:30 - 15:30,Bank Hol,0,0,0,0,0,0,0,0,0,0,0,0,0,0,WCS/WMS Release 16.6 - Outage 03:00 - 11:00,0,0,0,0,0,0,0,0,0,0,0,0,0,WCS/WMS Release 16.7 - Outage 03:00 - 11:00,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0
Promotional Activity,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,Sparks Preview to Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale & Cyber,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Sale,Sale,Sale,Sale,Sale,England vs Slovakia,Sale,Sale,Sa
le,Sale,Sale,Sale,0,0,0,0,F&F,F&F,F&F,F&F,F&F,0,0,0,0,0,0,Sparks Preview to Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,England vs Malta,0,0,0,0,0,0,England vs Malta,0,0,England vs Scotland,0,0,0,0,0,0,0,0,F&F,F&F,F&F,F&F,F&F,0,Home Event,Home Event,Home Event,Home Event,Home Event,H
ome Event,Home Event,0,Cyber,0,0,0,0,0,0,0,0,0,0,0,0,0,F&F & Beauty Advent Calendar Launch (TBC),F&F,F&F,F&F,F&F,0,Black Friday,Black Friday,Black Friday,Black Friday,Black Friday,Black Friday,0,0,Gifting Weekend,Gifting Weekend,Gifting Weekend,Gifting Weekend,Gifting Weekend,0,0,Xmas Countdown,Xmas
Countdown,Xmas Countdown,Xmas Countdown,Xmas Countdown,0,0,Wrap Up,Wrap Up,Wrap Up,Wrap Up,Wrap Up,0,0,0,0,0,Sale,Sale,Sale,Sale,Sale,Sale,Sale,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,Cyber,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,F&F,F&F,F&F,F&F,F&F,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,Mothers Day England vs Lithuania,0,0,0,0,0,0
Fiscal week,18,18,18,18,18,18,18,19,19,19,19,19,19,19,20,20,20,20,20,20,20,21,21,21,21,21,21,21,22,22,22,22,22,22,22,23,23,23,23,23,23,23,24,24,24,24,24,24,24,25,25,25,25,25,25,25,26,26,26,26,26,26,26,27,27,27,27,27,27,27,27,27,27,27,27,27,27,28,28,28,28,28,28,28,29,29,29,29,29,29,29,30,30,30,30,30,
30,30,31,31,31,31,31,31,31,32,32,32,32,32,32,32,33,33,33,33,33,33,33,34,34,34,34,34,34,34,35,35,35,35,35,35,35,36,36,36,36,36,36,36,37,37,37,37,37,37,37,38,38,38,38,38,38,38,39,39,39,39,39,39,39,40,40,40,40,40,40,40,41,41,41,41,41,41,41,42,42,42,42,42,42,42,43,43,43,43,43,43,43,44,44,44,44,44,44,44,
45,45,45,45,45,45,45,46,46,46,46,46,46,46,47,47,47,47,47,47,47,48,48,48,48,48,48,48,49,49,49,49,49,49,49,50,50,50,50,50,50,50,51,51,51,51,51,51,51,52,52,52,52,52,52,52
Week,31,31,31,31,31,31,31,32,32,32,32,32,32,32,33,33,33,33,33,33,33,34,34,34,34,34,34,34,35,35,35,35,35,35,35,36,36,36,36,36,36,36,37,37,37,37,37,37,37,38,38,38,38,38,38,38,39,39,39,39,39,39,39,40,40,40,40,40,40,40,40,40,40,40,40,40,40,41,41,41,41,41,41,41,42,42,42,42,42,42,42,43,43,43,43,43,43,43,4
4,44,44,44,44,44,44,45,45,45,45,45,45,45,46,46,46,46,46,46,46,47,47,47,47,47,47,47,48,48,48,48,48,48,48,49,49,49,49,49,49,49,50,50,50,50,50,50,50,51,51,51,51,51,51,51,52,52,52,52,52,52,52,1,1,1,1,1,1,1,2,2,2,2,2,2,2,3,3,3,3,3,3,3,4,4,4,4,4,4,4,5,5,5,5,5,5,5,6,6,6,6,6,6,6,7,7,7,7,7,7,7,8,8,8,8,8,8,8,
9,9,9,9,9,9,9,10,10,10,10,10,10,10,11,11,11,11,11,11,11,12,12,12,12,12,12,12,13,13,13,13,13,13,13
Date,31/7/16,1/8/16,2/8/16,3/8/16,4/8/16,5/8/16,6/8/16,7/8/16,8/8/16,9/8/16,10/8/16,11/8/16,12/8/16,13/8/16,14/8/16,15/8/16,16/8/16,17/8/16,18/8/16,19/8/16,20/8/16,21/8/16,22/8/16,23/8/16,24/8/16,25/8/16,26/8/16,27/8/16,28/8/16,29/8/16,30/8/16,31/8/16,1/9/16,2/9/16,3/9/16,4/9/16,5/9/16,6/9/16,7/9/16
,8/9/16,9/9/16,10/9/16,11/9/16,12/9/16,13/9/16,14/9/16,15/9/16,16/9/16,17/9/16,18/9/16,19/9/16,20/9/16,21/9/16,22/9/16,23/9/16,24/9/16,25/9/16,26/9/16,27/9/16,28/9/16,29/9/16,30/9/16,1/10/16,2/10/16,3/10/16,4/10/16,5/10/16,6/10/16,7/10/16,8/10/16,2/10/16,3/10/16,4/10/16,5/10/16,6/10/16,7/10/16,8/10/
16,09/10/2016,10/10/2016,11/10/2016,12/10/2016,13/10/2016,14/10/2016,15/10/2016,16/10/2016,17/10/2016,18/10/2016,19/10/2016,20/10/2016,21/10/2016,22/10/2016,23/10/2016,24/10/2016,25/10/2016,26/10/2016,27/10/2016,28/10/2016,29/10/2016,30/10/2016,31/10/2016,01/11/2016,02/11/2016,03/11/2016,04/11/2016,
05/11/2016,06/11/2016,07/11/2016,08/11/2016,09/11/2016,10/11/2016,11/11/2016,12/11/2016,13/11/2016,14/11/2016,15/11/2016,16/11/2016,17/11/2016,18/11/2016,19/11/2016,20/11/2016,21/11/2016,22/11/2016,23/11/2016,24/11/2016,25/11/2016,26/11/2016,27/11/2016,28/11/2016,29/11/2016,30/11/2016,01/12/2016,02/
12/2016,03/12/2016,04/12/2016,05/12/2016,06/12/2016,07/12/2016,08/12/2016,09/12/2016,10/12/2016,11/12/2016,12/12/2016,13/12/2016,14/12/2016,15/12/2016,16/12/2016,17/12/2016,18/12/2016,19/12/2016,20/12/2016,21/12/2016,22/12/2016,23/12/2016,24/12/2016,25/12/2016,26/12/2016,27/12/2016,28/12/2016,29/12/
2016,30/12/2016,31/12/2016,01/01/2017,02/01/2017,03/01/2017,04/01/2017,05/01/2017,06/01/2017,07/01/2017,08/01/2017,09/01/2017,10/01/2017,11/01/2017,12/01/2017,13/01/2017,14/01/2017,15/01/2017,16/01/2017,17/01/2017,18/01/2017,19/01/2017,20/01/2017,21/01/2017,22/01/2017,23/01/2017,24/01/2017,25/01/201
7,26/01/2017,27/01/2017,28/01/2017,29/01/2017,30/01/2017,31/01/2017,01/02/2017,02/02/2017,03/02/2017,04/02/2017,05/02/2017,06/02/2017,07/02/2017,08/02/2017,09/02/2017,10/02/2017,11/02/2017,12/02/2017,13/02/2017,14/02/2017,15/02/2017,16/02/2017,17/02/2017,18/02/2017,19/02/2017,20/02/2017,21/02/2017,2
2/02/2017,23/02/2017,24/02/2017,25/02/2017,26/02/2017,27/02/2017,28/02/2017,01/03/2017,02/03/2017,03/03/2017,04/03/2017,05/03/2017,06/03/2017,07/03/2017,08/03/2017,09/03/2017,10/03/2017,11/03/2017,12/03/2017,13/03/2017,14/03/2017,15/03/2017,16/03/2017,17/03/2017,18/03/2017,19/03/2017,20/03/2017,21/0
3/2017,22/03/2017,23/03/2017,24/03/2017,25/03/2017,26/03/2017,27/03/2017,28/03/2017,29/03/2017,30/03/2017,31/03/2017,01/04/2017
If I change the script to the following :
#!/usr/local/bin/perl
use strict;
use warnings 'all';
my @rows = ();
my @pivot = ();
open (FILE, "EDCNDC-Daily-Volume-Forecast_Ops Forecast by Shift.csv") or die $!; #Opens File or returns error thrown ($!)
while (<FILE>) {
chomp;
push @rows, [split /,/];
}
for my $row (@rows) {
for my $column (0 .. $#{$row}) {
push(@{$pivot[$column]}, $row->[$column]);
}
}
for my $newrow (@pivot) {
for my $newcol (@{$newrow}) {
print $newcol,",";
}
print "\n";
}
close FILE or die $!;
It returns the desired output except for the entire input CSV
Any help/hints would be greatly appreciated.
EDIT
The desired output for this script should be something like the following:
Site Activity, Promotional Activity, Fiscal week, Week, Date
MI Report Outage 14:30 - 15:30, 0, 18, 31, 31/07/2016
03:00 - 09:00 - Level 0 Diverter Installation - SCS Loop, 0, 18, 31, 01/08/2016
03:00 - 09:00 - Level 1 Diverter Installation - SCS Loop, 0, 18, 31, 02/08/2016
0, 0, 18, 31, 03/08/2016
0, 0, 18, 31, 04/08/2016
EDIT 2
Input file without stripping it down:
EDCNDC -Daily Volume Forecast,,,,,Issued,28th July,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
w/c 31st August,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,Site Activity,MI Report Outage 14:30 - 15:30,03:00 - 09:00 - Level 0 Diverter Installation - SCS Loop,03:00 - 09:00 - Level 1 Diverter Installation - SCS Loop,0,0,0,0,MI Report Outage 14:30 - 15:30,0,WCS/WMS Release 16.5 - Outage 03:00 - 11:00,0,0,0,0,MI Report Outage 14:30 - 15:30,0,0,0,0,0,0,MI Report Outage 14:30 - 15:30,0,0,0,0,0,0,MI Report Outage 14:30 - 15:30,Bank Hol,0,0,0,0,0,0,0,0,0,0,0,0,0,0,WCS/WMS Release 16.6 - Outage 03:00 - 11:00,0,0,0,0,0,0,0,0,0,0,0,0,0,WCS/WMS Release 16.7 - Outage 03:00 - 11:00,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,,,,Promotional Activity,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,Sparks Preview to Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale & Cyber,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Sale,Sale,Sale,Sale,Sale,England vs Slovakia,Sale,Sale,Sale,Sale,Sale,Sale,0,0,0,0,F&F,F&F,F&F,F&F,F&F,0,0,0,0,0,0,Sparks Preview to Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,England vs Malta,0,0,0,0,0,0,England vs Malta,0,0,England vs Scotland,0,0,0,0,0,0,0,0,F&F,F&F,F&F,F&F,F&F,0,Home Event,Home Event,Home Event,Home Event,Home Event,Home Event,Home Event,0,Cyber,0,0,0,0,0,0,0,0,0,0,0,0,0,F&F & Beauty Advent Calendar Launch (TBC),F&F,F&F,F&F,F&F,0,Black Friday,Black Friday,Black Friday,Black Friday,Black Friday,Black Friday,0,0,Gifting Weekend,Gifting Weekend,Gifting Weekend,Gifting Weekend,Gifting Weekend,0,0,Xmas Countdown,Xmas Countdown,Xmas Countdown,Xmas Countdown,Xmas Countdown,0,0,Wrap Up,Wrap Up,Wrap Up,Wrap Up,Wrap Up,0,0,0,0,0,Sale,Sale,Sale,Sale,Sale,Sale,Sale,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,Cyber,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,F&F,F&F,F&F,F&F,F&F,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,Mothers Day England vs Lithuania,0,0,0,0,0,0
,,,,Fiscal week,18,18,18,18,18,18,18,19,19,19,19,19,19,19,20,20,20,20,20,20,20,21,21,21,21,21,21,21,22,22,22,22,22,22,22,23,23,23,23,23,23,23,24,24,24,24,24,24,24,25,25,25,25,25,25,25,26,26,26,26,26,26,26,27,27,27,27,27,27,27,27,27,27,27,27,27,27,28,28,28,28,28,28,28,29,29,29,29,29,29,29,30,30,30,30,30,30,30,31,31,31,31,31,31,31,32,32,32,32,32,32,32,33,33,33,33,33,33,33,34,34,34,34,34,34,34,35,35,35,35,35,35,35,36,36,36,36,36,36,36,37,37,37,37,37,37,37,38,38,38,38,38,38,38,39,39,39,39,39,39,39,40,40,40,40,40,40,40,41,41,41,41,41,41,41,42,42,42,42,42,42,42,43,43,43,43,43,43,43,44,44,44,44,44,44,44,45,45,45,45,45,45,45,46,46,46,46,46,46,46,47,47,47,47,47,47,47,48,48,48,48,48,48,48,49,49,49,49,49,49,49,50,50,50,50,50,50,50,51,51,51,51,51,51,51,52,52,52,52,52,52,52
,,,,Week,31,31,31,31,31,31,31,32,32,32,32,32,32,32,33,33,33,33,33,33,33,34,34,34,34,34,34,34,35,35,35,35,35,35,35,36,36,36,36,36,36,36,37,37,37,37,37,37,37,38,38,38,38,38,38,38,39,39,39,39,39,39,39,40,40,40,40,40,40,40,40,40,40,40,40,40,40,41,41,41,41,41,41,41,42,42,42,42,42,42,42,43,43,43,43,43,43,43,44,44,44,44,44,44,44,45,45,45,45,45,45,45,46,46,46,46,46,46,46,47,47,47,47,47,47,47,48,48,48,48,48,48,48,49,49,49,49,49,49,49,50,50,50,50,50,50,50,51,51,51,51,51,51,51,52,52,52,52,52,52,52,1,1,1,1,1,1,1,2,2,2,2,2,2,2,3,3,3,3,3,3,3,4,4,4,4,4,4,4,5,5,5,5,5,5,5,6,6,6,6,6,6,6,7,7,7,7,7,7,7,8,8,8,8,8,8,8,9,9,9,9,9,9,9,10,10,10,10,10,10,10,11,11,11,11,11,11,11,12,12,12,12,12,12,12,13,13,13,13,13,13,13
,,,,Date,31/7/16,1/8/16,2/8/16,3/8/16,4/8/16,5/8/16,6/8/16,7/8/16,8/8/16,9/8/16,10/8/16,11/8/16,12/8/16,13/8/16,14/8/16,15/8/16,16/8/16,17/8/16,18/8/16,19/8/16,20/8/16,21/8/16,22/8/16,23/8/16,24/8/16,25/8/16,26/8/16,27/8/16,28/8/16,29/8/16,30/8/16,31/8/16,1/9/16,2/9/16,3/9/16,4/9/16,5/9/16,6/9/16,7/9/16,8/9/16,9/9/16,10/9/16,11/9/16,12/9/16,13/9/16,14/9/16,15/9/16,16/9/16,17/9/16,18/9/16,19/9/16,20/9/16,21/9/16,22/9/16,23/9/16,24/9/16,25/9/16,26/9/16,27/9/16,28/9/16,29/9/16,30/9/16,1/10/16,2/10/16,3/10/16,4/10/16,5/10/16,6/10/16,7/10/16,8/10/16,2/10/16,3/10/16,4/10/16,5/10/16,6/10/16,7/10/16,8/10/16,09/10/2016,10/10/2016,11/10/2016,12/10/2016,13/10/2016,14/10/2016,15/10/2016,16/10/2016,17/10/2016,18/10/2016,19/10/2016,20/10/2016,21/10/2016,22/10/2016,23/10/2016,24/10/2016,25/10/2016,26/10/2016,27/10/2016,28/10/2016,29/10/2016,30/10/2016,31/10/2016,01/11/2016,02/11/2016,03/11/2016,04/11/2016,05/11/2016,06/11/2016,07/11/2016,08/11/2016,09/11/2016,10/11/2016,11/11/2016,12/11/2016,13/11/2016,14/11/2016,15/11/2016,16/11/2016,17/11/2016,18/11/2016,19/11/2016,20/11/2016,21/11/2016,22/11/2016,23/11/2016,24/11/2016,25/11/2016,26/11/2016,27/11/2016,28/11/2016,29/11/2016,30/11/2016,01/12/2016,02/12/2016,03/12/2016,04/12/2016,05/12/2016,06/12/2016,07/12/2016,08/12/2016,09/12/2016,10/12/2016,11/12/2016,12/12/2016,13/12/2016,14/12/2016,15/12/2016,16/12/2016,17/12/2016,18/12/2016,19/12/2016,20/12/2016,21/12/2016,22/12/2016,23/12/2016,24/12/2016,25/12/2016,26/12/2016,27/12/2016,28/12/2016,29/12/2016,30/12/2016,31/12/2016,01/01/2017,02/01/2017,03/01/2017,04/01/2017,05/01/2017,06/01/2017,07/01/2017,08/01/2017,09/01/2017,10/01/2017,11/01/2017,12/01/2017,13/01/2017,14/01/2017,15/01/2017,16/01/2017,17/01/2017,18/01/2017,19/01/2017,20/01/2017,21/01/2017,22/01/2017,23/01/2017,24/01/2017,25/01/2017,26/01/2017,27/01/2017,28/01/2017,29/01/2017,30/01/2017,31/01/2017,01/02/2017,02/02/2017,03/02/2017,04/02/2017,05/02/2017,06/02/2017,07/02/2017,08/02/2017,09/02/2017,10/02/2017,11/02/2017,12/02/2017,13/02/2017,14/02/2017,15/02/2017,16/02/2017,17/02/2017,18/02/2017,19/02/2017,20/02/2017,21/02/2017,22/02/2017,23/02/2017,24/02/2017,25/02/2017,26/02/2017,27/02/2017,28/02/2017,01/03/2017,02/03/2017,03/03/2017,04/03/2017,05/03/2017,06/03/2017,07/03/2017,08/03/2017,09/03/2017,10/03/2017,11/03/2017,12/03/2017,13/03/2017,14/03/2017,15/03/2017,16/03/2017,17/03/2017,18/03/2017,19/03/2017,20/03/2017,21/03/2017,22/03/2017,23/03/2017,24/03/2017,25/03/2017,26/03/2017,27/03/2017,28/03/2017,29/03/2017,30/03/2017,31/03/2017,01/04/2017
Function,Stock Type,Process,Unit,Shift,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Inbound,Boxed,Total Boxed Inbound,Singles,Nights,28753,40776,40776,40776,40776,40776,28753,30776,54628,27699,54628,54628,54628,30776,33886,48057,48057,48057,48057,48057,33886,34232,48547,48547,48547,48547,48547,34232,31036,44014,44014,44014,44014,44014,31036,27777,39394,39394,39394,39394,39394,27777,28758,51046,25883,51046,51046,51046,28758,29189,41396,41396,41396,41396,41396,29189,26718,46756,46756,26718,46756,46756,26718,44613,63270,63270,63270,63270,63270,44613,39952,56660,56660,56660,56660,56660,39952,42442.17878,60190.72627,60190.72627,60190.72627,60190.72627,60190.72627,42442.17878,36979.88005,52444.19353,52444.19353,52444.19353,52444.19353,52444.19353,36979.88005,40308.06908,57164.17069,57164.17069,57164.17069,57164.17069,57164.17069,40308.06908,46676.58717,66195.88726,66195.88726,66195.88726,66195.88726,66195.88726,46676.58717,39409.75945,55890.20431,55890.20431,55890.20431,55890.20431,55890.20431,39409.75945,43296.5707,61402.40936,61402.40936,61402.40936,61402.40936,61402.40936,43296.5707,42459.3679,60215.10356,60215.10356,60215.10356,60215.10356,60215.10356,42459.3679,46535.68254,65996.05887,65996.05887,65996.05887,65996.05887,65996.05887,46535.68254,39267.83205,55688.92545,55688.92545,55688.92545,55688.92545,55688.92545,39267.83205,35169.72953,49877.07097,49877.07097,49877.07097,49877.07097,49877.07097,35169.72953,28787.26072,40825.56974,40825.56974,40825.56974,40825.56974,40825.56974,28787.26072,0,34683.57367,32858.12242,27381.76869,23730.86619,36509.02491,27381.76869,7889.088977,29978.53811,28400.72032,23667.26693,20511.63134,31556.35591,15778.17795,34548.03526,48995.39545,48995.39545,48995.39545,48995.39545,48995.39545,34548.03526,29586.82654,41959.49946,41959.49946,41959.49946,41959.49946,41959.49946,29586.82654,29142.11368,41328.81577,41328.81577,41328.81577,41328.81577,41328.81577,29142.11368,24446.63191,34669.7689,34669.7689,34669.7689,34669.7689,34669.7689,24446.63191,21871.48748,31017.74588,17894.85339,31813.07269,35789.70678,31017.74588,21871.48748,25878.56489,36700.51021,36700.51021,36700.51021,36700.51021,36700.51021,25878.56489,34473.60269,48889.83655,48889.83655,48889.83655,48889.83655,48889.83655,34473.60269,25470.73167,36122.12855,36122.12855,36122.12855,36122.12855,36122.12855,25470.73167,18392.09341,26083.33247,26083.33247,26083.33247,26083.33247,26083.33247,18392.09341,17921.55403,25416.02208,25416.02208,25416.02208,25416.02208,25416.02208,17921.55403,28883.3673,40961.86635,40961.86635,40961.86635,40961.86635,40961.86635,28883.3673,24868.17244,35267.59001,35267.59001,35267.59001,35267.59001,35267.59001,24868.17244
,,Total Boxed Inbound,Singles,AM,28753,40776,40776,40776,40776,40776,28753,30776,54628,27699,54628,54628,54628,30776,33886,48057,48057,48057,48057,48057,33886,34232,48547,48547,48547,48547,48547,34232,31036,44014,44014,44014,44014,44014,31036,27777,39394,39394,39394,39394,39394,27777,28758,51046,25883,51046,51046,51046,28758,29189,41396,41396,41396,41396,41396,29189,26718,46756,46756,26718,46756,46756,26718,44613,63270,63270,63270,63270,63270,44613,39952,56660,56660,56660,56660,56660,39952,42442.17878,60190.72627,60190.72627,60190.72627,60190.72627,60190.72627,42442.17878,36979.88005,52444.19353,52444.19353,52444.19353,52444.19353,52444.19353,36979.88005,40308.06908,57164.17069,57164.17069,57164.17069,57164.17069,57164.17069,40308.06908,46676.58717,66195.88726,66195.88726,66195.88726,66195.88726,66195.88726,46676.58717,39409.75945,55890.20431,55890.20431,55890.20431,55890.20431,55890.20431,39409.75945,43296.5707,61402.40936,61402.40936,61402.40936,61402.40936,61402.40936,43296.5707,42459.3679,60215.10356,60215.10356,60215.10356,60215.10356,60215.10356,42459.3679,46535.68254,65996.05887,65996.05887,65996.05887,65996.05887,65996.05887,46535.68254,39267.83205,55688.92545,55688.92545,55688.92545,55688.92545,55688.92545,39267.83205,35169.72953,49877.07097,49877.07097,49877.07097,49877.07097,49877.07097,35169.72953,28787.26072,40825.56974,40825.56974,40825.56974,40825.56974,40825.56974,28787.26072,0,34683.57367,32858.12242,27381.76869,23730.86619,36509.02491,27381.76869,7889.088977,29978.53811,28400.72032,23667.26693,20511.63134,31556.35591,15778.17795,34548.03526,48995.39545,48995.39545,48995.39545,48995.39545,48995.39545,34548.03526,29586.82654,41959.49946,41959.49946,41959.49946,41959.49946,41959.49946,29586.82654,29142.11368,41328.81577,41328.81577,41328.81577,41328.81577,41328.81577,29142.11368,24446.63191,34669.7689,34669.7689,34669.7689,34669.7689,34669.7689,24446.63191,21871.48748,31017.74588,17894.85339,31813.07269,35789.70678,31017.74588,21871.48748,25878.56489,36700.51021,36700.51021,36700.51021,36700.51021,36700.51021,25878.56489,34473.60269,48889.83655,48889.83655,48889.83655,48889.83655,48889.83655,34473.60269,25470.73167,36122.12855,36122.12855,36122.12855,36122.12855,36122.12855,25470.73167,18392.09341,26083.33247,26083.33247,26083.33247,26083.33247,26083.33247,18392.09341,17921.55403,25416.02208,25416.02208,25416.02208,25416.02208,25416.02208,17921.55403,28883.3673,40961.86635,40961.86635,40961.86635,40961.86635,40961.86635,28883.3673,24868.17244,35267.59001,35267.59001,35267.59001,35267.59001,35267.59001,24868.17244
Input after the following section of code:
...
if ($. < 4 ) {print ""}
elsif ($. > 8) {print ""}
elsif ($_ =~ /^\,\,\,\,/) {print substr($_,4)} #Tidy up excess white space
...
This outputs the following:
Site Activity,MI Report Outage 14:30 - 15:30,03:00 - 09:00 - Level 0 Diverter Installation - SCS Loop,03:00 - 09:00 - Level 1 Diverter Installation - SCS Loop,0,0,0,0,MI Report Outage 14:30 - 15:30,0,WCS/WMS Release 16.5 - Outage 03:00 - 11:00,0,0,0,0,MI Report Outage 14:30 - 15:30,0,0,0,0,0,0,MI Report Outage 14:30 - 15:30,0,0,0,0,0,0,MI Report Outage 14:30 - 15:30,Bank Hol,0,0,0,0,0,0,0,0,0,0,0,0,0,0,WCS/WMS Release 16.6 - Outage 03:00 - 11:00,0,0,0,0,0,0,0,0,0,0,0,0,0,WCS/WMS Release 16.7 - Outage 03:00 - 11:00,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
Promotional Activity,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,Sparks Preview to Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale & Cyber,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Home Event & Sale,Sale,Sale,Sale,Sale,Sale,England vs Slovakia,Sale,Sale,Sale,Sale,Sale,Sale,0,0,0,0,F&F,F&F,F&F,F&F,F&F,0,0,0,0,0,0,Sparks Preview to Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,Sale,England vs Malta,0,0,0,0,0,0,England vs Malta,0,0,England vs Scotland,0,0,0,0,0,0,0,0,F&F,F&F,F&F,F&F,F&F,0,Home Event,Home Event,Home Event,Home Event,Home Event,Home Event,Home Event,0,Cyber,0,0,0,0,0,0,0,0,0,0,0,0,0,F&F & Beauty Advent Calendar Launch (TBC),F&F,F&F,F&F,F&F,0,Black Friday,Black Friday,Black Friday,Black Friday,Black Friday,Black Friday,0,0,Gifting Weekend,Gifting Weekend,Gifting Weekend,Gifting Weekend,Gifting Weekend,0,0,Xmas Countdown,Xmas Countdown,Xmas Countdown,Xmas Countdown,Xmas Countdown,0,0,Wrap Up,Wrap Up,Wrap Up,Wrap Up,Wrap Up,0,0,0,0,0,Sale,Sale,Sale,Sale,Sale,Sale,Sale,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,Cyber,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,F&F,F&F,F&F,F&F,F&F,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,Mothers Day England vs Lithuania,0,0,0,0,0,0
Fiscal week,18,18,18,18,18,18,18,19,19,19,19,19,19,19,20,20,20,20,20,20,20,21,21,21,21,21,21,21,22,22,22,22,22,22,22,23,23,23,23,23,23,23,24,24,24,24,24,24,24,25,25,25,25,25,25,25,26,26,26,26,26,26,26,27,27,27,27,27,27,27,27,27,27,27,27,27,27,28,28,28,28,28,28,28,29,29,29,29,29,29,29,30,30,30,30,30,30,30,31,31,31,31,31,31,31,32,32,32,32,32,32,32,33,33,33,33,33,33,33,34,34,34,34,34,34,34,35,35,35,35,35,35,35,36,36,36,36,36,36,36,37,37,37,37,37,37,37,38,38,38,38,38,38,38,39,39,39,39,39,39,39,40,40,40,40,40,40,40,41,41,41,41,41,41,41,42,42,42,42,42,42,42,43,43,43,43,43,43,43,44,44,44,44,44,44,44,45,45,45,45,45,45,45,46,46,46,46,46,46,46,47,47,47,47,47,47,47,48,48,48,48,48,48,48,49,49,49,49,49,49,49,50,50,50,50,50,50,50,51,51,51,51,51,51,51,52,52,52,52,52,52,52
Week,31,31,31,31,31,31,31,32,32,32,32,32,32,32,33,33,33,33,33,33,33,34,34,34,34,34,34,34,35,35,35,35,35,35,35,36,36,36,36,36,36,36,37,37,37,37,37,37,37,38,38,38,38,38,38,38,39,39,39,39,39,39,39,40,40,40,40,40,40,40,40,40,40,40,40,40,40,41,41,41,41,41,41,41,42,42,42,42,42,42,42,43,43,43,43,43,43,43,44,44,44,44,44,44,44,45,45,45,45,45,45,45,46,46,46,46,46,46,46,47,47,47,47,47,47,47,48,48,48,48,48,48,48,49,49,49,49,49,49,49,50,50,50,50,50,50,50,51,51,51,51,51,51,51,52,52,52,52,52,52,52,1,1,1,1,1,1,1,2,2,2,2,2,2,2,3,3,3,3,3,3,3,4,4,4,4,4,4,4,5,5,5,5,5,5,5,6,6,6,6,6,6,6,7,7,7,7,7,7,7,8,8,8,8,8,8,8,9,9,9,9,9,9,9,10,10,10,10,10,10,10,11,11,11,11,11,11,11,12,12,12,12,12,12,12,13,13,13,13,13,13,13
Date,31/7/16,1/8/16,2/8/16,3/8/16,4/8/16,5/8/16,6/8/16,7/8/16,8/8/16,9/8/16,10/8/16,11/8/16,12/8/16,13/8/16,14/8/16,15/8/16,16/8/16,17/8/16,18/8/16,19/8/16,20/8/16,21/8/16,22/8/16,23/8/16,24/8/16,25/8/16,26/8/16,27/8/16,28/8/16,29/8/16,30/8/16,31/8/16,1/9/16,2/9/16,3/9/16,4/9/16,5/9/16,6/9/16,7/9/16,8/9/16,9/9/16,10/9/16,11/9/16,12/9/16,13/9/16,14/9/16,15/9/16,16/9/16,17/9/16,18/9/16,19/9/16,20/9/16,21/9/16,22/9/16,23/9/16,24/9/16,25/9/16,26/9/16,27/9/16,28/9/16,29/9/16,30/9/16,1/10/16,2/10/16,3/10/16,4/10/16,5/10/16,6/10/16,7/10/16,8/10/16,2/10/16,3/10/16,4/10/16,5/10/16,6/10/16,7/10/16,8/10/16,09/10/2016,10/10/2016,11/10/2016,12/10/2016,13/10/2016,14/10/2016,15/10/2016,16/10/2016,17/10/2016,18/10/2016,19/10/2016,20/10/2016,21/10/2016,22/10/2016,23/10/2016,24/10/2016,25/10/2016,26/10/2016,27/10/2016,28/10/2016,29/10/2016,30/10/2016,31/10/2016,01/11/2016,02/11/2016,03/11/2016,04/11/2016,05/11/2016,06/11/2016,07/11/2016,08/11/2016,09/11/2016,10/11/2016,11/11/2016,12/11/2016,13/11/2016,14/11/2016,15/11/2016,16/11/2016,17/11/2016,18/11/2016,19/11/2016,20/11/2016,21/11/2016,22/11/2016,23/11/2016,24/11/2016,25/11/2016,26/11/2016,27/11/2016,28/11/2016,29/11/2016,30/11/2016,01/12/2016,02/12/2016,03/12/2016,04/12/2016,05/12/2016,06/12/2016,07/12/2016,08/12/2016,09/12/2016,10/12/2016,11/12/2016,12/12/2016,13/12/2016,14/12/2016,15/12/2016,16/12/2016,17/12/2016,18/12/2016,19/12/2016,20/12/2016,21/12/2016,22/12/2016,23/12/2016,24/12/2016,25/12/2016,26/12/2016,27/12/2016,28/12/2016,29/12/2016,30/12/2016,31/12/2016,01/01/2017,02/01/2017,03/01/2017,04/01/2017,05/01/2017,06/01/2017,07/01/2017,08/01/2017,09/01/2017,10/01/2017,11/01/2017,12/01/2017,13/01/2017,14/01/2017,15/01/2017,16/01/2017,17/01/2017,18/01/2017,19/01/2017,20/01/2017,21/01/2017,22/01/2017,23/01/2017,24/01/2017,25/01/2017,26/01/2017,27/01/2017,28/01/2017,29/01/2017,30/01/2017,31/01/2017,01/02/2017,02/02/2017,03/02/2017,04/02/2017,05/02/2017,06/02/2017,07/02/2017,08/02/2017,09/02/2017,10/02/2017,11/02/2017,12/02/2017,13/02/2017,14/02/2017,15/02/2017,16/02/2017,17/02/2017,18/02/2017,19/02/2017,20/02/2017,21/02/2017,22/02/2017,23/02/2017,24/02/2017,25/02/2017,26/02/2017,27/02/2017,28/02/2017,01/03/2017,02/03/2017,03/03/2017,04/03/2017,05/03/2017,06/03/2017,07/03/2017,08/03/2017,09/03/2017,10/03/2017,11/03/2017,12/03/2017,13/03/2017,14/03/2017,15/03/2017,16/03/2017,17/03/2017,18/03/2017,19/03/2017,20/03/2017,21/03/2017,22/03/2017,23/03/2017,24/03/2017,25/03/2017,26/03/2017,27/03/2017,28/03/2017,29/03/2017,30/03/2017,31/03/2017,01/04/2017
Upvotes: 0
Views: 61
Answers (1)
Dan Walmsley
Reputation: 2821
I think this is what you are after
#!/usr/local/bin/perl
use strict;
use warnings 'all';
my @rows = ();
my @pivot = ();
open (FILE, "test.csv") or die $!; #Opens File or returns error thrown ($!)
while (<FILE>) {
if ($. < 4 ) {
#next will just go to the next line of the csv
next;
}
elsif ($. > 8) {
#last will mean you jump out of the while loop and stop reading the csv
last;
}
#So the regex is a little more complex the brackets are capture groups basically what is in side them will get put in $1, $2 and so on.
elsif ($_ =~ /^\,\,\,\,(.*?)\,(.*)/) {
#in the first group is the header you want
# the .* matches every thing, the ? Makes it non greedy (so you will stop with you see the next ,
# then there is the funny $. == 8 ? I'm checking if your on line 8 , if you are you get the code in the first () else you get the second()
# it's called the turnary operator. I want to avoid adding the last comma if we're on the last line
$. == 8 ? (print "$1") : (print "$1, ");
# now instead of in an else clause you want to grab the rest of the line here.
# what was happening before is you were ignoring the rest of the line and only getting into the else when nothing else matched
# the $2 contains the rest of the line so basically doing what you were doing before but with $2
chomp $2;
push @rows, [split /,/, $2];
}
}
#This is just to separate the header line
print "\n";
for my $row (@rows) {
for my $column (0 .. $#{$row}) {
push(@{$pivot[$column]}, $row->[$column]);
}
}
for my $newrow (@pivot) {
for my $newcol (@{$newrow}) {
print $newcol,",";
}
print "\n";
}
close FILE or die $!;
You were close, the main issue was that you were thinking you were dealing with rest of the line in your else block. But you were actually on the lines that were the ones you wanted to throw away
EDIT
I'm getting the following output
Site Activity, Promotional Activity, Fiscal week, Week, Date
MI Report Outage 14:30 - 15:30,0,18,31,31/7/16,
03:00 - 09:00 - Level 0 Diverter Installation - SCS Loop,0,18,31,1/8/16,
03:00 - 09:00 - Level 1 Diverter Installation - SCS Loop,0,18,31,2/8/16,
0,0,18,31,3/8/16,
0,0,18,31,4/8/16,
0,0,18,31,5/8/16,
0,0,18,31,6/8/16,
MI Report Outage 14:30 - 15:30,0,19,32,7/8/16,
0,0,19,32,8/8/16,
WCS/WMS Release 16.5 - Outage 03:00 - 11:00,0,19,32,9/8/16,
0,0,19,32,10/8/16,
0,0,19,32,11/8/16,
0,0,19,32,12/8/16,
0,0,19,32,13/8/16,
MI Report Outage 14:30 - 15:30,0,20,33,14/8/16,
0,Sparks Preview to Sale,20,33,15/8/16,
0,Sale,20,33,16/8/16,
0,Sale,20,33,17/8/16,
0,Sale,20,33,18/8/16,
0,Sale,20,33,19/8/16,
0,Sale,20,33,20/8/16,
MI Report Outage 14:30 - 15:30,Sale,21,34,21/8/16,
Upvotes: 1
Related Questions
- How to extract row data from a csv file using perl?
- Reading CSV with Perl produces distorted lines
- Transposing CSV data in Perl
- Perl - transpose CSV data on a per x dimensions basis
- Perl-reordering rows in a tsv file
- Partially transposing a CSV
- How to define columns and lines in CSV files?
- How to merge multiple lines in a csv file using Perl
- How do I read a CSV file column by column in order to transpose it?
- Transpose rows into columns using Perl