Format CSV file

Shell Programming and Scripting
1

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 :slight_smile:

2

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?

3

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.

4

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.

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?

5

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.

6

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

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

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.

8

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

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

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

10

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

11

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

12

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?

13

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.

14

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:

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?