Process Files to create a new file

Question

I have a file1 which contains comma separated values (timein/timeout in military time format eg 0800, 0900, 1300). For each line of File 1 contains timein/timeout for each day.

Sample File 1:

Name, Position Level 3
0800, 1800
0900, 1200, 1230, 2000
0901, 2100

File 2 contains (hourly rate):

Position Level 1, 100
Position Level 2, 200
Position Level 3, 300
Position Level 4, 400
Position Level 5, 500

I need to create a File 3 with lines with the 1st time in and last timeout and number of hours rendered for each day displayed in each line of File3. And last line will display the monthly salary which will calculate the number of hours rendered for the month (sum of hours from each day) * the hourly rate.

File 3:

Name, Position Level 3
0800, 1800, 10
0900, 2000, 10.5
0901, 2100, 10.983
9444.9

Answer 1

Please note that the files you have given are not comma separated; they are comma-space separated. You will need to take out these spaces after the commas otherwise below method may not work correctly.

The following is pseudo-code which should help you get started.

Take care of using the right characters.

My approach would be:

• read and store the first line of file1

FIRSTLINE="`head -n 1 file1`"

• write this to file3

echo "$FIRSTLINE" >> file3

• read the first line of file1 and get the second argument with a comma as the separator

POSLVL="`head -n 1 file1 | awk -F , '{print $2}'`"

You now have the position level in a variable, this allows you you to work out the hourly rate for this code by looking it up in file2:

RATE=`grep "$POSLVL" file2 | awk -F , '{print $2}'`

We can then continue reading through everly line of file1 using something like this:

MAXLINES=`wc -l file1 | awk '{print $1}'`
LINE=2

while test $LINE -le $MAXLINES
do
  READLINE="`head -n $LINE | tail -n 1`"

  LINE_HOURS=0  
  TOTAL_COST=0

  # count nr of commas to determine the number of in/out pairs
  COMMAS=`echo $READLINE | grep -o , | wc -l | awk '{print $1}'`

  # if comma-count = 1 (1 in/out pair)
  # extract first and second numbers
  # write these numbers to file3 without a newline (echo -n "$NR1,$NR2")
  # work out difference in hours
  # add to LINE_HOURS

  # if comma-count > 2 (2 in/out pairs)
  # extract third and fourth numbers
  # write these numbers to file3 without a newline 
  # work out difference in hours
  # add to LINE_HOURS

  # maybe add more if's for up to 3, 4 or 5 pairs..

  # -- reached end of the line -- 

  # write LINE_HOURS to file3

  # calculate total cost by multiplying LINE_HOURS * RATE and store in TOTAL_COST 

  LINE=$((LINE + 1))  

done

# -- reached end of the file --

# write TOTAL_COST to file3

See how you go.