Splitting columns into rows from a text file using unix shell script - Dynamically changing source file structure

Question

I've a tab seperated source file with this structure: Only the first 9 columns from ID to Line Item/Property is fixed, rest are all dynamic varying in count and structure.

ID  Date/Time (UTC) User    Description Security Change Previous Value  New Value   Module/List Line Item/Property  Scenarios   Region EM2  Plan Item PB6   Market EM4  Plants - Master Plan Brand PB4  T/DI    GRS 6   GRS 7   Target User Import  Object  Target Role Export  Dashboard   Action  Time

Here's one sample record from that file

2572561 3/24/2020 14:01 [email protected]            FALSE   TRUE    FILTER:  Brand P&L Report - Market  Plan Brands                     Polly Pocket                [email protected]

I need to change it to the below struture as a CSV file with the following headers and data format using Unix shell script. I want to keep the permanent columns (ID till Line Item/Property) as such and park all other dynamically variable columns into Attribute Name & Attribute Value column:

ID,Date/Time (UTC),User,Description,Security Change,Previous Value,New Value,Module/List,Line Item/Property,Attribute Name,Attribute Value
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Scenarios,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Region EM2,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Plan Item PB6,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Market EM4,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Plants - Master,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Plan Brand PB4,Polly Pocket
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,T/DI,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,GRS 6,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,GRS 7,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Target User,[email protected]
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Import,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Object,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Target Role,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Export,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Dashboard,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Action,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Time,

Kindly wrap your samples in code tags in your question and let us know then. — RavinderSingh13
– RavinderSingh13, Commented Apr 3, 2020 at 5:04
Please remove the email address and replace it by some dummy vallue like foo@bar. — kvantour
– kvantour, Commented Apr 3, 2020 at 5:06
Please clarify how to handle commas (,) in original data records. — rtx13
– rtx13, Commented Apr 3, 2020 at 6:45

rtx13 · Accepted Answer · 2020-04-03 06:09:16Z

1

Note: the following will not work correctly if any field contains a comma character (,).

Try this bash script (named process for the terminal session that follows):

#!/bin/bash

tr '\t' ',' | {
    IFS=',' # separator for all array reads and printfs

    # read and output heading
    read -r -a heading
    printf "%s\n" "${heading[*]:0:9},Attribute Name,Attribute Value"    

    # process one line of data
    while read -r -a data ; do
        for (( i=9; i<${#heading[*]}; ++i )) ; do
            printf "%s\n" "${data[*]:0:9},${heading[i]},${data[i]}"
        done
    done
}

Terminal session:

$ cat data.in | tr '\t' ','
ID,Date/Time (UTC),User,Description,Security Change,Previous Value,New Value,Module/List,Line Item/Property,Scenarios,Region EM2,Plan Item PB6,Market EM4,Plants - Master,Plan Brand PB4,T/DI,GRS 6,GRS 7,Target User,Import,Object,Target Role,Export,Dashboard,Action,Time
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,,,,,,Polly Pocket,,,,[email protected]
$ ./process < data.in 
ID,Date/Time (UTC),User,Description,Security Change,Previous Value,New Value,Module/List,Line Item/Property,Attribute Name,Attribute Value
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Scenarios,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Region EM2,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Plan Item PB6,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Market EM4,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Plants - Master,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Plan Brand PB4,Polly Pocket
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,T/DI,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,GRS 6,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,GRS 7,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Target User,[email protected]
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Import,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Object,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Target Role,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Export,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Dashboard,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Action,
2572561,3/24/2020 14:01,[email protected],,,FALSE,TRUE,FILTER:  Brand P&L Report - Market,Plan Brands,Time,
$

edited Apr 3, 2020 at 6:09

answered Apr 3, 2020 at 5:08

rtx13

2,6201 gold badge8 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Arjun Vasudevan Over a year ago

I have to convert columns to rows. That's a major challenge here. That too from a dynamically changing source structure

rtx13 Over a year ago

You input and sample output do not show any row-column transpose operation... Nevermind I understand it now...

Arjun Vasudevan Over a year ago

Yeah, sorry, changed the question to add that

rtx13 Over a year ago

Do you have a choice of language? I don't recommend bash. I could show you a solution in tcl.

Arjun Vasudevan Over a year ago

No, Unix shell script.

Collectives™ on Stack Overflow

Splitting columns into rows from a text file using unix shell script - Dynamically changing source file structure

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related