Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to extract start/end times from log file to CSV file?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to extract start/end times from log file to CSV file?
# 1  
Old 11-23-2014
How to extract start/end times from log file to CSV file?
Hi,

I have a log file (log.txt) that which contains lines of date/time.
I need to create a script to extract a CSV file (out.csv) that gets all the sequential times (with only 1 minute difference) together by stating the start time and end time of this period.


Sample log file (log.txt)
Code:
14-11-2014 05:23
14-11-2014 05:24
14-11-2014 05:25
14-11-2014 05:26
16-11-2014 13:01
16-11-2014 13:02
16-11-2014 13:03
23-11-2014 03:00
23-11-2014 05:24
23-11-2014 05:25

The CSV out file should look something like the below:
Code:
14-11-2014 05:23,14-11-2014 05:26,4
16-11-2014 13:01,16-11-2014 13:03,3
23-11-2014 03:00,23-11-2014 03:00,1
23-11-2014 05:24,23-11-2014 05:25,2

The first column of each line is the start time, the second column is the end time, and the third column is the number of minutes between the start and end times.

Would you please help me in this?

Thanks in advance.

Zizo
# 2  
Old 11-23-2014
That would do the job
Enjoy...

Code:
#!/bin/bash

numOfRecords=0
prevDay=`head -1 log.txt| awk '{print $1}'`
startTime=`head -1 log.txt| awk '{print $2}'`
endTime=$startTime
while read data
do
        curDay=`echo $data | awk '{print $1}'`
        if [[ $curDay != $prevDay ]]
        then
        echo $prevDay,$startTime,$endTime,$numOfRecords
                startTime=`echo $data | awk '{print $2}'`
                numOfRecords=1
        else
                numOfRecords=$((numOfRecords+1))
                endTime=`echo $data | awk '{print $2}'`
        fi
        prevDay=$curDay
done<log.txt
echo $prevDay,$startTime,$endTime,$numOfRecords

Last edited by Franklin52; 11-23-2014 at 09:17 AM.. Reason: Please use code tags, thanks
# 3  
Old 11-23-2014
This depends on a recent shell and GNU date:
Code:
awk '{print $3"-"$2"-"$1" "$4":"$5}' FS="[-: ]" file4 |
        date -f- +%s |
        { read OLDP
          printf "%(%d-%m-%Y %H:%M)T, " $OLDP; CNT=1   
          while read EP
                do [ $((EP - OLDP)) -gt 60 ] && { printf "%(%d-%m-%Y %H:%M)T, %s\n%(%d-%m-%Y %H:%M)T, " $OLDP $CNT $EP; CNT=0; }  
                OLDP=$EP
                ((CNT++))
                done
          printf "%(%d-%m-%Y %H:%M)T, %s\n" $OLDP $CNT
        } 
14-11-2014 05:23, 14-11-2014 05:26, 4
16-11-2014 13:01, 16-11-2014 13:03, 3
23-11-2014 03:00, 23-11-2014 03:00, 1
23-11-2014 05:24, 23-11-2014 05:25, 2

This User Gave Thanks to RudiC For This Post:
derekludwig
# 4  
Old 11-23-2014
Thanks a lot for your replies. I really appreciate your help.

However, unfortunately, I still have problems trying both ways...

@Igal Malka, I tried the script you provided, but it only checks for days (not times)... meaning that it doesn't capture the case when there is non-sequential times in the same day.
Code:
14-11-2014 05:23 
14-11-2014 05:24 
14-11-2014 05:25 
14-11-2014 05:26 
16-11-2014 13:01 
16-11-2014 13:02 
16-11-2014 13:03 
23-11-2014 03:00 
23-11-2014 05:24 
23-11-2014 05:25

When I tired the script, the result was the below:
Code:
14-11-2014,05:23,05:26,4
16-11-2014,13:01,13:03,3
23-11-2014,03:00,05:25,3

It checked only for the date and assumed that those are only 3 minutes (on 23-11-2014), where it is actually 1 minute at 03:00, and 2 minutes from 05:24 to 05:25.


@RudiC, I tried the script you provided, but unfortunately it gave me some error related to printf:
Code:
-bash: printf: `(': invalid format character
-bash: printf: `(': invalid format character
-bash: printf: `(': invalid format character
-bash: printf: `(': invalid format character
-bash: printf: `(': invalid format character

Note:
OS:
Code:
Linux  #1 SMP Tue Feb 18 11:42:11 EST 2014 x86_64 x86_64 x86_64 GNU/Linux

Thanks,
Zizo
Last edited by Mr.Zizo; 11-23-2014 at 12:07 PM..
# 5  
Old 11-23-2014
As I said, it needs a recent shell that has the %(datefmt)T format for printf. You still can try to use the date command to convert epoch seconds to your preferred date format.
# 6  
Old 11-23-2014
I'm stuck unfortunately.
# 7  
Old 11-23-2014
Modification of RudiC script (I unrolled the pipeline just to make it clearer to myself) that doesn't use the %(datefmt)T format of printf
Code:
awk '{print $3"-"$2"-"$1" "$4":"$5}' FS="[-: ]" "${1}" \
| date -f- +%s \
| {
  read oldT
  echo -n "${oldT} "
  N=1
  while read curT; do
    if [[ $(( curT - oldT )) -gt 60 ]]; then
      echo ${oldT} ${N}
      echo -n "${curT} "
      N=0
    fi
    oldT=${curT}
    (( N++ ))
  done
  echo ${oldT} ${N}
} \
| while read oldT newT N; do
  echo $(date +%d-%m-%Y\ %H:%M -d @${oldT}),$(date +%d-%m-%Y\ %H:%M -d @${newT}),${N}
done

See the manpage if your version of date doesn't support -d @seconds.
Testing with a larger dataset:
Code:
14-11-2014 05:23
14-11-2014 05:24
14-11-2014 05:25
14-11-2014 05:26
16-11-2014 13:01
16-11-2014 13:02
16-11-2014 13:03
23-11-2014 03:00
23-11-2014 05:24
23-11-2014 05:25
24-11-2014 08:24
24-11-2014 08:25
25-11-2014 08:58
25-11-2014 08:59
25-11-2014 09:00
25-11-2014 09:01
31-12-2014 23:47
31-12-2014 23:48
31-12-2014 23:49
    snip
01-01-2015 00:05
01-01-2015 00:06
01-01-2015 00:07

yielded:
Code:
14-11-2014 05:23,14-11-2014 05:26,4
16-11-2014 13:01,16-11-2014 13:03,3
23-11-2014 03:00,23-11-2014 03:00,1
23-11-2014 05:24,23-11-2014 05:25,2
24-11-2014 08:24,24-11-2014 08:25,2
25-11-2014 08:58,25-11-2014 09:01,4
31-12-2014 23:47,01-01-2015 00:07,21

(thanks to RudiC for giving me a starting point)
These 2 Users Gave Thanks to derekludwig For This Post:
Mr.Zizo RudiC
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Split a file by start and end row.

I have a file which looks something as following, I would like to split to several files, The start and end of each file is 'FILE' and end with 'ASCII... ' . At the same time for each file in the first column add 100 and also second column add 100 the rest of the column as it is , see example of... (2 Replies)
Discussion started by: tk2000
2 Replies

2. Shell Programming and Scripting

Extract data from XML file and write in CSV file

Hi friend i have input as following XML file <?xml version="1.0"?> <Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.054.001.02"> <BkToCstmrDbtCdtNtfctn> <GrpHdr><MsgId>LBP-RDJ-TE000000-130042430010001001</MsgId><CreDtTm>2013-01-04T03:21:30</CreDtTm></GrpHdr>... (3 Replies)
Discussion started by: mohan sharma
3 Replies

3. UNIX for Dummies Questions & Answers

extract regions of file based on start and end position

Hi, I have a file1 of many long sequences, each preceded by a unique header line. file2 is 3-columns list: headers name, start position, end position. I'd like to extract the sequence region of file1 specified in file2. Based on a post elsewhere, I found the code: awk... (2 Replies)
Discussion started by: pathunkathunk
2 Replies

4. Shell Programming and Scripting

Use grep sed or awk to extract string from log file and put into CSV

I'd like to copy strings from a log file and put them into a CSV. The strings could be on different line numbers, depending on size of log. Example Log File: File = foo.bat Date = 11/11/11 User = Foo Bar Size = 1024 ... CSV should look like: "foo.bat","11/11/11","Foo Bar","1024" (7 Replies)
Discussion started by: chipperuga
7 Replies

5. Shell Programming and Scripting

Extract data from an XML file & write into a CSV file

Hi All, I am having an XML tag like: <detail sim_ser_no_1="898407109001000090" imsi_1="452070001000090"> <security>ADM1=????</security> <security>PIN1=????</security> <security>PIN2=????</security> ... (2 Replies)
Discussion started by: ss_ss
2 Replies

6. Shell Programming and Scripting

can I specifiy the start and end times manually

Hi I have a ksh script which fetches data from a db using a number of .arc files and creates CSV files for them and puts them on the server. Question is, how can I specifiy the start and stop times specifically so that data is fetched for a certain period? # Get the current time as the... (1 Reply)
Discussion started by: shajju
1 Replies

7. Shell Programming and Scripting

can I specifiy the start and end times manually

Hi I have a ksh script which fetches data from a db using a number of .arc files and creates CSV files for them and puts them on the server. Question is, can I specifiy the start and stop times manually and run the script manually to fetch data for a certain period? # Get the current... (0 Replies)
Discussion started by: shajju
0 Replies

8. UNIX for Dummies Questions & Answers

Extract a specific number from an XML file based on the start and end tags

Hello People, I have the following contents in an XML file ........... ........... .......... ........... <Details = "Sample Details"> <Name>Bob</Name> <Age>34</Age> <Address>CA</Address> <ContactNumber>1234</ContactNumber> </Details> ........... ............. .............. (4 Replies)
Discussion started by: sushant172
4 Replies

9. Shell Programming and Scripting

extract a particular start and end pattern from a line

hi In the foll example the whole text in a single line.... i want to extract text from IPTel to RTCPBase.h. want to use this acrooss the whole file Updated: IPTel\platform\core\include\RTCPBase.h \main\MWS2051_Sablime_Int\1... (7 Replies)
Discussion started by: manish205
7 Replies

10. Shell Programming and Scripting

Start and End times of background processes

Hi I'm running 4 jobs in the background and I need to write the start and end times to a log file. I know there's probably a simple way to do this but, I can't think of it. I've used nohup <script name> & but, that doesn't record the times. Is there a way to get the start and end times of a... (2 Replies)
Discussion started by: stonemonolith
2 Replies
Login or Register to Ask a Question