Shell Programming and Scripting

I have a csv file which I need to process and export back to xlsx file.

For instance, the csv contains:

John Smith, job-title, hours
John Smith, job-title, hours
Mary Smith job-title, hours

etc.

I need to import that to a script, get id of redundant data i.e:

John smith, job-title, hours(hours for the whole month..)
Mary Smith job-title, hours

and export that to an xlsx file.

Does anybody know how I would go about this?

I have heard that OpenOffice's spreadsheet program has modules that could perhaps do this?

I could also probably do this in php... I mean, if I imported the data to a db and then exported everything as I needed? But would this be overkill?

Thanks in advance for your comments

If there are commas between each of the extra hours then to remove the extraneous data by running:
$ cat file.csv | awk -F"," '{ print $1","$2","$3 }' > outputfile.csv

Excel can import "*.csv" files and then save as "*.xlsx" file but I presume you want this automated?

Thanks for the reply. Yes, I need this to be automated. Plus, the extraneous data, the hours, I need to add these together to get a total for the month (each line is the total for the week).

The php and db route just seems a little much but I'm not sure of another way to do it.

As I understand, you would like to sum the third field over the lines having the same first field, and for each distinct first field output a single line with the sum replacing the third field.

Code:

awk -F, -v OFS=, 'FNR==NR{a[$1]+=$3;next}$3=a[$1]{print$0;a[$1]=0}' file.csv file.csv > result.csv

Yes, you have to give to awk the name of the input file two times in a row.

Sorry, I don't know what a .xlsx file is. Perhaps a xml file formatted by/for Excel?

Thanks for the reply.

A .xlsx file is a combination of XML architecture and ZIP compression for size reduction. Basically though, I have to output to a spreadsheet.

The other problem is that I have to format the output for each month. I.e:

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
...

The first two (the total for john Smith for January) have to be added up and displayed separately from John Smith for Feb and separately from Mary Smith.

this does not work if the total results in '0'.

Code:

awk -F, -v OFS=, '{sum[$1]+=$3;rec[$1]=$1 OFS $2} END {for(i in sum) print rec[i], sum[i]}' file.csv > result.csv

Good point. I was aware of this problem, but assumed that an input record with zero hours would be entirely omitted and that negative hours do not make sense as input.