Format Time with Awked Output

Hi,

I would like the 2nd column in this log file to display as %k%S(00:00) in a new file. See my script and desired output below.

05-25-2010 21:45:49 1616 1615 0 0 0 0 0 0 0
05-23-2010 21:55:49 1495 1493 0 0 0 3 0 0 0
05-23-2010 22:05:49 1750 1750 0 0 0 0 0 0 0
05-24-2010 22:15:49 1839 1837 0 0 0 0 1 0 0
05-24-2010 22:25:49 1693 1687 0 0 0 3 1 0 1
05-25-2010 22:35:49 1614 1609 0 0 0 3 4 0 0
05-25-2010 22:45:49 1599 1594 0 0 0 0 3 0 0

This is my current script.

#!/bin/bash
echo "You must type the date in this format ---> MM-DD-YYYY"
read vdate
grep $vdate /log/log.csv |awk -F, '{print $2, $3, $12, $13, $14, $15, $16, $17,$18}' |tee /tmp/bk_$vdate.csv

Desired Output

21:45 1616 1615 0 0 0 0 0 0 0
21:55 1495 1493 0 0 0 3 0 0 0 

Try:

#!/bin/bash
echo "You must type the date in this format ---> MM-DD-YYYY"
read vdate

awk -v dat=$vdate '$0 ~ dat{$1="";sub("...$","",$2);sub("^ ","")}1' file

you are using field separator as comma (-F,) but your file doesn't seems to be csv file!!

don't you want the date field?

try:

awk -v dd="$vdate" '/dd/ {$2=substr($2,1,5);for (i=2;i<=NF;i++) printf "%s ",$i}' /log/log.csv |tee /tmp/bk_$vdate.csv

Yeah, I also wondered about that...

@ravzter
However, no matter if it's comma separated or whitespace separated, there are no fields $12-$18

If it's comma separated, try to replace your existing awk command in the script with this:

awk -F, '{print $2,$3,$4,$5,$6,$7,$8,$9,$10,$11}' | sed 's/:[0-9][0-9] / /g'

or if it's whitespace separated, with this:

awk '{print $2,$3,$4,$5,$6,$7,$8,$9,$10,$11}' | sed 's/:[0-9][0-9] / /g'

read -p "You must type the date in this format ---> MM-DD-YYY" vdate
grep $vdate log.csv | sed -e "s/^$vdate //" -e 's/\:[0-9][0-9] / /' | tee /tmp/bk_$vdate.csv

Thanks everyone that replied, they all produce output of the logfile, however the Works script below gives me the desired TIME format.

Below is a snippet of the actual log file saved as csv.
I need to extract the underline.

02-13-2007  14:38:11,2296,2281,2289,2335,2391,2392,2307,2307,0,288,0,0,0,8,8,46,0
02-13-2007 14:48:11,2058,2052,2055,2055,2043,2044,2043,2043,0,0,0,0,0,3,0,0,0
02-13-2007  14:58:11,2255,2242,2247,2247,2082,2081,2082,2082,0,0,0,0,0,5,9,0,0

The code below indicates the fields I need output for a specific excel report.
Works

grep $vdate log.csv |awk -F, '{print $1, $2, $3, $12, $13, $14, $15, $16, $17,$18}' | sed 's/:[0-9][0-9] / /g' |tee /tmp/bk_$vdate.csv

Output of Works
05-27-2010 23:45 1351 1351 0 0 0 0 0 0 0

However how do I eliminate the DATE portion highlighted in Red?

s/.*-[0-9]\{4\} //'

It works as following: deletes everything before the last minus sign followed by 4 digits.

Try to improve your already existing sed part like this:

sed 's/:[0-9][0-9] / /g;s/.*-[0-9]\{4\} //'

Awesome! Works like a charm Pseudocoder

Just one annoyance, how do I fix the "READ" portion of the script to jump to the next line for user entry? It'll probably take me some time to figure it out since I'm a noob.

You must type the date in this format ---> MM-DD-YYY05-27-2010

#!/bin/bash
read -p "You must type the date in this format ---> MM-DD-YYY" vdate
grep $vdate log.csv |awk -F, '{print $1, $2, $3, $12, $13, $14, $15, $16, $17,$18}' |sed 's/:[0-9][0-9] / /g;s/
.*-[0-9]\{4\} //' |tee /tmp/nm_$vdate.csv

Here's my output:

23:25 1412 1408 0 0 0 0 1 0 0
23:35 1472 1475 0 0 0 1 0 0 0
23:45 1351 1351 0 0 0 0 0 0 0
23:55 1345 1344 0 0 0 0 0 0 0

Seems to me to be the best run. No need to feed awk with grep nor to filter output with sed. Alternative to the use of awk sub() function:

awk -F"[ :]" -v dat=$vdate '$1=="05-25-2010"{print $2":"$3,$5,$6,$7,$8,$9,$10,$11,$12,$13}'

Try this

echo "You must type the date in this format ---> MM-DD-YYYY" && read -p vdate

Also there is a small typo in your previous postings, it should be 4 Y's I guess

I'll give this a shot also! Just need to understand why the date is specific?
I need the script to run daily.

Thanks!

---------- Post updated at 10:02 AM ---------- Previous update was at 09:47 AM ----------

Oops! Thanks, and a period to complete the sentence.

BTW It's getting hung with the new READ portion:

Hung Screen

You must type the date in this format ---> MM-DD-YYYY
vdate05-27-2010

Try without -p switch, like following:

echo "You must type the date in this format ---> MM-DD-YYYY" && read vdate

If this won't work, please post your current script, maybe there's a typo or something.

Perfect!

Thank you very much! Just got two more ideas, which I'll try on my own and post results.

Thanks again!

You must type the date in this format ---> MM-DD-YYYY
05-27-2010
00:05 1282 1278 0 0 0 0 1 4 0
00:15 1094 1091 0 0 0 0 1 0 0
00:25 1030 1031 0 0 0 0 1 0 0
00:35 989 989 0 0 0 0 0 0 0