Need help with SED/AWK script

[heroicwisdom]

Hi guys,

Great forum here, this is my first post, after looking though here I could not find the answer i needed, so i figured i would join up and see if anybody can help me.

Basically what i have is a csv file that looks similar to the following -
ID Name Date+Time
1 Joe Doe 1/1/2011 5:12:00 PM
2 Joe Doe 1/1/2011 5:12:00 PM
3 Joe Doe 1/2/2011 9:20:00 AM
4 Joe Doe 1/3/2011 11:12:00 AM


What i need to do is pull the first instance, and last instance of the date. So lets say there are 50 records with 1/2/2011, I need the first and last record (based of the time)

I figure i need to compare the record below, to the record above, but i can not figure how to do that with sed/awk.

Can somebody help me out?

Thanks!

[atreyu]

That does not look like a CSV file (where are the commas?).

But basically, you need to:

1. cat the file

2. pipe the output to awk and match on the date in question and print the columns/fields representing the time

3. use sed to strip out anything from the output you don't need

once you have the times saved in a variable, you should be able to determine which is the lowest number (earliest time) and which is the highest number (latest time), then subtract them.

Read up on the commands mentioned (man sed, man awk, man bash, etc.) and try it out.

See the scripts in /etc/init.d/ for examples of the above commands in action.

[heroicwisdom]

Thanks for the response. i forgot to include the commas in the sample. see below.

1, Joe Doe, 1/1/2011 5:12:00 PM
2, Joe Doe, 1/1/2011 5:12:00 PM
3, Joe Doe, 1/2/2011 9:20:00 AM
4, Joe Doe, 1/3/2011 11:12:00 AM

i figure i need to start by adding a comma between the date and time?

1, Joe Doe, 1/1/2011, 5:12:00 PM

About the date part of it, its not the date in question, its every day. I need the first and last record from each day to be put out into a file.

I imagine i need to do a loop that assigns the date/time to a var and compares the value to the current record? Can you help me with that?

I am checking out the samples now. Thanks again for the assistance.

[heroicwisdom]

I have gotten much farther, but i am having an issue with the following

cat a.txt| awk 'BEGIN{FS = "|"}
{i=1
do
{print NR $5; i++}
while(i<=NR)
}
'

when i run that code above, i get output like the following
83 5/20/2009 9:25:08 AM
83 5/20/2009 9:25:08 AM
83 5/20/2009 9:25:08 AM
83 5/20/2009 9:25:08 AM
84 5/20/2009 11:17:23 AM
84 5/20/2009 11:17:23 AM

The record prints an obscene amount of times before incrementing i and reading the next var. Any ideas guys?

Thanks,

[atreyu]

try this to get you started:

cat file.txt|while read line; do
#  echo LINE: $line
  index=$(echo $line| awk -F, '{print $1}')
  name=$(echo $line| awk -F, '{print $2}')
  date=$(echo $line| awk -F, '{print $3}'|awk '{print $1}')
  time=$(echo $line| awk -F, '{print $3}'|awk '{print $2}')
 echo " $index - $name - $date - $time"
done

the file "file.txt" contains the csv data you listed in your last post.

now you have the name, date, and time all in separate variables, for each line in the file.

[heroicwisdom]

Thank you very much! With your code i was able to modify it to fit my needs. I now have what i need, however i would like to add one more piece to the puzzle.

Using your code i formatted the text to look like the following

name | date | 1st | last
joe | 2/2/1022 | 8:18:90 AM | 4:26 PM


what i would like to do now is calculate the difference in hours between the two times. adding one more column - total time

I attempted to convert the PM times to military time for calculations, but I am failing there.

here is a code snippet.

if [ $ampm == "PM" ];then
time12=$(($time + 12))
echo "match found!"
echo "$index $time12 $ampm"
else 
echo "elsed!"
echo "$index $time $ampm"
fi
done

When run i get a syntax error that i can not figure out. Do i have to convert the time values to numeric before i can calculate?

[atreyu]

Thank you very much! With your code i was able to modify it to fit my needs. I now have what i need, however i would like to add one more piece to the puzzle.

Using your code i formatted the text to look like the following

name | date | 1st | last
joe | 2/2/1022 | 8:18:90 AM | 4:26 PM


what i would like to do now is calculate the difference in hours between the two times. adding one more column - total time

I attempted to convert the PM times to military time for calculations, but I am failing there.

here is a code snippet.

if [ $ampm == "PM" ];then
time12=$(($time + 12))
echo "match found!"
echo "$index $time12 $ampm"
else 
echo "elsed!"
echo "$index $time $ampm"
fi
done

When run i get a syntax error that i can not figure out. Do i have to convert the time values to numeric before i can calculate?

you could convert the time using the date command, e.g.:

 date -d '4:26 PM' +'%H:%M'

That should convert 4:26 PM to 16:26. Then you could do the same with your start time (to strip of the seconds). It'd probably be best to convert them both to minutes, then subtract, then convert back to Hours Mins.

To do the subtration itself, try something like:

diff=$(echo $stop - $start|bc -l)