Find the answer to your Linux question:
Results 1 to 7 of 7
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 ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Aug 2011
    Posts
    4

    Question Need help with SED/AWK script


    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!

  2. #2
    Trusted Penguin
    Join Date
    May 2011
    Posts
    4,353
    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.

  3. #3
    Just Joined!
    Join Date
    Aug 2011
    Posts
    4
    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.

  4. #4
    Just Joined!
    Join Date
    Aug 2011
    Posts
    4
    I have gotten much farther, but i am having an issue with the following

    Code:
    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,

  5. #5
    Trusted Penguin
    Join Date
    May 2011
    Posts
    4,353
    try this to get you started:
    Code:
    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.

  6. #6
    Just Joined!
    Join Date
    Aug 2011
    Posts
    4
    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.

    Code:
    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?

  7. #7
    Trusted Penguin
    Join Date
    May 2011
    Posts
    4,353
    Quote Originally Posted by heroicwisdom View Post
    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.

    Code:
    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.:
    Code:
     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:
    Code:
    diff=$(echo $stop - $start|bc -l)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •