perl - Transpose CSV data

Question

I have been looking around and I can see how to turn columns into rows but not vice versa.

Example A

What I am trying to achieve is the following:

Input data

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,...
Promotional Activity,0,0,0,0,0,...
Fiscal week,18,18,18,18,18,...
Week,31,31,31,31,31,...
Date,31/07/2016,01/08/2016,02/08/2016,03/08/2016,04/08/2016,.....

This is a snippet of the input data the actual data has over 200 fields (x axis)

The output I am trying to achieve is as follows:

Site Activity, Promotional Activity, Fisclal 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
etc etc

My current code to return the input format is as follows:

#!/usr/local/bin/perl

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 {
        print $_;
    }
}

close file

I am having to transform CSV into a usable format hence why I am limiting the lines that are returned. (lines 8 and 11)

Answer 1

use strict;
use warnings 'all';
use feature 'say';

my @data = map {
    chomp;
    [ split /,/ ];
} <DATA>;

for my $i ( 0 .. $#{ $data[0] } ) {
    say join ', ', map { $_->[$i] // "" } @data;
}

__DATA__
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,...
Promotional Activity,0,0,0,0,0,...
Fiscal week,18,18,18,18,18,...
Week,31,31,31,31,31,...
Date,31/07/2016,01/08/2016,02/08/2016,03/08/2016,04/08/2016,.....

output

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
..., ..., ..., ..., .....