Excel column headers export to csv

Question

Just wandering how I can export excel files to csv while retaining the multicolumn headers?

I realize that csv does not support this however, I'm hoping that I can have the header name duplicated for every column that it goes over.

To illustrate, this is what I am currently getting

a,,,b,,,c,,
1,2,3,4,5,6,7,8,9
d1,d1,d1,d1,d1,d1,d1,d1,d1
d2,d2,d2,d2,d2,d2,d2,d2,d2

This is what I would like

a,a,a,b,b,b,c,c,c
1,2,3,4,5,6,7,8,9
d1,d1,d1,d1,d1,d1,d1,d1,d1
d2,d2,d2,d2,d2,d2,d2,d2,d2

I have searched to find an answer, however unless I'm searching the wrong term .... I can't seem to find much on it.

Solution doesn't have to be Microsoft .... can also be a linux solution.

Thanks, Brian

Answer 1

If you have exported your spreadsheet to file1.csv, as shown, do

#!/bin/sh
awk -F, -v OFS=, '
NR==1 {
        for (i = 1; i <= NF; i++) {
                if ($i == "") $i = prev
                prev=$i
        }
    }
{ print }' < file1.csv > file2.csv

• -F, -v OFS=, sets awk’s input and output file separators to ,.
• NR==1 means “do the following on the first record (line/row) only”.
• The for loop looks at each field (cell) in the first row, and, if it’s blank, sets it equal to the preceding value.
• { print } prints all records from file1.csv to file2.csv, with the first one being modified (as described above) and all others being passed through from input to output (so empty cells below the first row won’t be tampered with).