awk and printf to format a csv file

Although all the information is there it looks like a bit of a jumble.

To make it more legible use awk and printf to clean it up and make at easier to read.

Here’s what we start with.

Date,In,Out,h:m,Time,Rate(per hour),CA,Status,Billable,Customer,Tracking Number
13.01.,10:02,17:32,7:30,7.5,30,225,open,100%,My-Company,My-Company.01.03.17
12.01.,09:49,17:13,7:24,7.4,30,222,open,100%,My-Company,My-Company.01.03.17
11.01.,09:53,17:36,7:43,7.72,30,231.5,open,100%,My-Company,My-Company.01.03.17
10.01.,09:50,17:15,7:25,7.42,30,222.5,open,100%,My-Company,My-Company.01.03.17
09.01.,10:05,17:16,7:11,7.18,30,215.5,open,100%,My-Company,My-Company.01.03.17
06.01.,09:30,17:16,7:46,7.77,30,233,open,100%,My-Company,My-Company.01.03.17
05.01.,09:49,17:37,7:48,7.8,30,234,open,100%,My-Company,My-Company.01.03.17
04.01.,09:40,17:18,7:38,7.63,30,229,open,100%,My-Company,My-Company.01.03.17
03.01.,09:40,17:30,7:50,7.83,30,235,open,100%,My-Company,My-Company.01.03.17
02.01.,10:13,10:14,0:01,0.02,30,0.72,open,100%,My-Company,My-Company.01.03.17

This will format it into something a lot cleaner.

echo ' ';awk -F',' '{ printf "%-8s %-8s %-8s %-8s %-8s $%-16s %5s\n",$1,$2,$3,$4,$5,$6,$7; SUM += $7 } END {printf "%68s\n", "Total $"SUM}' timecard.csv; echo ' '

Now you have something like this

Date     In       Out      h:m      Time     $Rate(per hour)      CA
13.01.   10:02    17:32    7:30     7.5      $30                 225
12.01.   09:49    17:13    7:24     7.4      $30                 222
11.01.   09:53    17:36    7:43     7.72     $30               231.5
10.01.   09:50    17:15    7:25     7.42     $30               222.5
09.01.   10:05    17:16    7:11     7.18     $30               215.5
06.01.   09:30    17:16    7:46     7.77     $30                 233
05.01.   09:49    17:37    7:48     7.8      $30                 234
04.01.   09:40    17:18    7:38     7.63     $30                 229
03.01.   09:40    17:30    7:50     7.83     $30                 235
02.01.   10:13    10:14    0:01     0.02     $30                0.72
                                                      Total $2048.22

The echo’s are there just to break it away from the prompt.

Then you have the -F’,’ delimiter

Next the printf statement with the column width’s, one for each column.

Don’t forget the comma between the printf statement and the awk column selections.

I’ve also summarized the last column to give a total amount.

The last few column’s were left out intentionally, but you could add them easily by including them in both the awk and printf command. I also added the $ sign to the rate column by placing it in front of the % sign in the 6th printf column.

Hmmm! Looking at this I also think she needs a raise!

Here is a great intro to printf

https://www.math.utah.edu/docs/info/gawk_7.html#SEC60

 

Leave a Reply

Your email address will not be published. Required fields are marked *