Align columns in comma-separated file

Question

I am writing the script in linux and the result of the script is a csv file. I want to format this file. My output file is:

"T_Hours","T_Count","T_Hours","P_Avg_5","Diffrence_Between_P_Avg_5_and_T_Count"
"00","446","00","533","87"
"01","159","01","224","65"
"02","95","02","140","45"
"03","84","03","117","33"
"04","92","04","113","21"

I want to make the output like this:

"T_Hours","T_Count","T_Hours","P_Avg_5","Diffrence_Between_P_Avg_5_and_T_Count"
"00"     ,"446"    ,"00"     ,"533"    ,"87"
"01"     ,"159"    ,"01"     ,"224"    ,"65"
"02"     ,"95"     ,"02"     ,"140"    ,"45"
"03"     ,"84"     ,"03"     ,"117"    ,"33"
"04"     ,"92"     ,"04"     ,"113"    ,"21"

What I try: I try to add spaces after the , and before it, but that doesn't work because the numbers do not always have the same width; for example, in the second column may be the number "55556" so adding a specific number of spaces will not work. So I think the solution is to shift the character , in all lines to put it under the , in the first line. Any help?

Answer 1

Here's a portable way:

sed 's/,/:,/g' output.csv |
column -t -s: |
sed 's/ ,/,/g'

Explanation:

column -t aligns columns. The input separator can be specified with -s but the output separator is always a space (unless you have version like GNU or util-linux supporting the -o flag, see this answer.).

With column -s, -t output.csv the output would be just

"T_Hours" "T_Count" "T_Hours" ...
"00"      "446"     "00"      ...
"01"      "159"     "01"      ...
...

To keep the , in the output we have to pre- and post-process:

• Use sed to insert an additional delimiter : in front of each comma-delimiter. The character you choose has to be a character that isn't otherwise in your input.
• Use column -t specifying that : character as the delimiter. column will align all columns and replace all : by a space.
• Use sed to delete the spaces (originally the : we inserted in the first command).

"T_Hours","T_Count","T_Hours",...
"00"     ,"446"    ,"00"     ,...
"01"     ,"159"    ,"01"     ,...
...

More info, see man column.

Answer 2

$ column -t -s, -o, file
"T_Hours","T_Count","T_Hours","P_Avg_5","Diffrence_Between_P_Avg_5_and_T_Count"
"00"     ,"446"    ,"00"     ,"533"    ,"87"
"01"     ,"159"    ,"01"     ,"224"    ,"65"
"02"     ,"95"     ,"02"     ,"140"    ,"45"
"03"     ,"84"     ,"03"     ,"117"    ,"33"
"04"     ,"92"     ,"04"     ,"113"    ,"21"