Shell Programming and Scripting

well, I guess John Smith is entitled to at least 2 weeks of vacation
the problem is this:

Code:

$3=a[$1]{print$0;a[$1]=0}

if 'a[$1]' results in '0', '$3=0' results in '0' => no 'action' taken, i.e. 'print' omitted.

Code:

awk -F, -v OFS=, 'FNR==NR{split($4,b,"-");a[$1"-"b[1]+0"-"b[3]+0]+=$3;next} \
{split($4,b,"-")}($3=a[k=$1"-"b[1]+0"-"b[3]+0])!=""{print$1,$2,$3,b[1]"-"b[3];a[k]=""}' \
file.csv file.csv > result.csv

CSV works as a raw spreadsheet format. You can "import" a CSV file in Excel, or even rename it to a .xls extension and "open" it or "execute" (double clic) it.

Yes, but I need to parse the data and remove redundant information and display it formatted per month.

so show a proper set of input data, and show your desired output as well...

I am puzzled.

result.csv above already has no "redundant data" (i.e. the hours are added up over each month-year, it has exactly one line per name-month-year, and the month-year is in the fourth field).
Why can't you import result.csv in Excel?

Because this is a file with thousands of records containing bi-weekly hours, which need to be added up to show the amount of hours per month worked on the same project by the same employee. If I just import into excel as is, then I would have to sift through all those records, add titles... and the month name for each record as well as add up the total hours per month per project, per user.



Okay, thanks for the reply.


This is the raw csv data:
john,project,1/1/09,1/15/2009,misc notes,hours
john,project,1/16/09,1/31/2009,misc notes,hours
john,project,2/1/09,2/15/2009,misc notes,hours
...

I need to output as follows:

Period: January

Username: Project: Start Date: End Date: Notes: Total Hours
John Project 01/01/09 01/31/09 Notes... 55.0


Period: February

Username: Project: Start Date: End Date: Notes: Total Hours
John Project 02/01/09 02/28/09 Notes... 65.0

Period: January

Username: Project: Start Date: End Date: Notes: Total Hours
Mary Project 01/01/09 01/31/09 Notes... 55.0


Period: February

Username: Project: Start Date: End Date: Notes: Total Hours
Mary Project 02/01/09 02/28/09 Notes... 65.0


Sorry about the formatting above, I can't line up the desired outcome text. But basically, the column name with the data underneath.

Total hours per staff member, per month.

Thanks again.

Please, pay attention.
When I referred to result.csv I was talking about a csv file that is the result of the calculations you would like to automatically perform.
The file is obtained as explained in the above articles, using a unix tool whose name is awk. After all we are in the unix forum, aren't we?
I showed you exactly how to write the command line to invoke awk to obtain result.csv, given your input file.csv.
I even tested the command line with a file.csv formatted as per your sample.

This file result.csv, I believe, can be easily imported in Excel.

Now you are changing the format (swapping fields around) for file.csv, but this would be easily taken into account. You just have to decide which format you like and settle onto it, otherwise we are going to be hopelessly confused about what code works for what format.

This is exactly what the command line outputs in result.csv, if file.csv is formatted like:

Code:

John Smith, job-title, hours, 01-01-09, 01-15-09
John Smith, job-title, hours, 01-16-09, 01-31-09
Mary Smith job-title, hours, , 01-01-09, 01-31-09
...
John Smith, job-title, hours, 02-01-09, 02-15-09
...

You didn't attempt to run that command line and open the result file, did you?