Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have 1 column like below:

AAA
999888
BBB
1111
BBB
2222
AAA
999777
BBB
3333
AAA
999666
BBB
4444
CCC
Doit
...

Consider above up to a few thousand rows.
I want to convert it to below 3 column table with rows from an AAA to the next AAA line:

AAA     BBB     CCC
999888  1111    
999888  2222    
999777  3333    
999666  4444    Doit

According to below pattern/rules:

  1. There are 3 types of headers: AAA, BBB and CCC. Next line after them are their corresponding values.

  2. Values of AAA and BBB (that is to say, next lines after them) are not null. ex:

    AAA
    BBB

    or

    BBB
    CCC

    cannot be seen. They will always have values after them.

  3. CCC column header may not be seen between 2 AAA entries as in the example above. From one AAA to the next AAA if there is no CCC, the new column of CCC will be empty for that row.

What is the way to perform this in excel (any version).
Perl, shell, awk etc. are all fine as well.

share|improve this question

1 Answer 1

I have used

#!/bin/bash
input_file=${1}
declare -A columns
SEP='\t'
curr_level=0
headers=""
while read line; do
if [ -n "${column}" ]; then
    for (( i=${curr_level}; i<${columns[$column]}; i++ )); do
        echo -ne ${SEP} >> tmpout.txt
    done
    curr_level=${columns[$column]}
    echo -n ${line} >> tmpout.txt
    column=""      
else
    column="${line}"
    if [ -z "${columns[$line]}" ]; then
        columns[$line]="${#columns[@]}"
        headers+="${line}${SEP}"
    elif [ ${columns[$line]} -le $curr_level ]; then
        echo "" >> tmpout.txt
        curr_level=0
    fi
fi             
done < $input_file

echo -e $headers | cat - tmpout.txt >> out.txt
unset columns column headers
rm tmpout.txt

Put that into a file, say script.sh, make it executable and call script.sh file_to_format.sh. It will create a file called out.txt, containing the result, and a file called tmpout.txt, that will be deleted, it is used for temporary working out.

Note: This will leave vertical gaps, such as

AAA     BBB     CCC     
999888  1111
        2222
999777  3333
999666  4444    Doit
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.