1

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
  • 4
    Better to show the samples source lines and what do you want to receive. – Costas Oct 27 '14 at 14:36
  • What have you tried? What tools are your willing to use? Did you get any errors while trying? Or maybe some unexpected results? – John WH Smith Oct 27 '14 at 14:38
  • I am new to UNIX so i dont have an idea how to do it. I am researching and I saw awk commands. but i saw a lot doing arrays. – user89332 Oct 27 '14 at 14:52
  • Commands to get values from a comma-separated and putting the value to a file. – user89332 Oct 27 '14 at 14:55
  • 1
    What values? You haven't shown even a single sample line of your three files, just said that the first file contains commas, and two 24-hour timestamps. Is there anything else in there? What order are the columns? Is file 2 a single integer value, or something else? What should file 3 look like? – Useless Oct 27 '14 at 15:43
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.