Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Get first and last time entries for a given date in CSV file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Get first and last time entries for a given date in CSV file
# 1  
Old 05-06-2015
Get first and last time entries for a given date in CSV file
CSV date sorted file has multiple entries for most dates. Need to eliminate all but the first and last entries of each date, being the start and end times for that day.

Code:
2009-11-20,23:57:46
2009-11-20,23:58:46
2009-11-20,23:59:46
2009-11-21,00:00:45
2009-11-21,00:01:46
2009-11-21,00:02:45
2009-11-21,00:22:45
2009-11-21,00:23:45
2009-11-21,00:24:45
2009-11-21,00:25:46

Ideally, output would be:
date,begintime,endtime
HTML Code:
2009-11-20,23:57:46,23:59:46
2009-11-21,00:00:45,00:25:46
I've used bash and gawk to get this far, but haven't grasped how to loop with gawk comparing two lines.

If there's a better solution with perl or php, that would work too.

Thanks
# 2  
Old 05-06-2015
Hiya

Try:
Code:
date=$(date +%F)
grep $date file.txt | head -n1
grep $date file.txt | tail -n1

hth
# 3  
Old 05-06-2015
although you don't state it the assumption is the file is sorted to start with.

using (g)awk:

Code:
awk -F, '
$1==date{last=$2; next}
{
   if(date) print date,first,last
   date=$1
   last=first=$2
}
END{ if(date) print date,first,last}' OFS=, infile

This User Gave Thanks to Chubler_XL For This Post:
mdennis6
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Date format change in a csv file

Hi, We have csv file where date is coming in MM/DD/YYYY HH:MM:SS (06/23/2015 20:59:12) in multiple places But we need to change the date format to DD/Mon/YYYY HH:MM:SS (23/Jul/2015 20:59:12) using shell script. Please let us know how can we achieve the same. (16 Replies)
Discussion started by: dholea
16 Replies

2. Shell Programming and Scripting

Need to change date format in a csv file using awk

Example: Input csv file 00245DLS,Sitel Ocala,12/31/2014,18:45,1.00,7.00,0.00,0.00 00245DLS,Sitel Ocala,12/31/2014,19:00,-1.00,-1.00,-1.00,-1.00 00245HB,Charlotte,01/01/2015,00:00,-1.00,-1.00,-1.00,0.00 Output csv file 00245DLS,Sitel Ocala,2014/12/31,18:45,1.00,7.00,0.00,0.00 00245DLS,Sitel... (8 Replies)
Discussion started by: adit
8 Replies

3. Shell Programming and Scripting

Changing date format in CSV file

I have a CSV file with a date format like this; 11/19/2012 17:37:00,1.372,121.6 11/19/2012 17:38:00,0.743,121.6 Want to change the time stamp to seconds after 1970 so I can get the data in rrdtool. For anyone interested, this is data from a TED5000 unit and is Kwatts and volts. Needs to... (3 Replies)
Discussion started by: ottsm
3 Replies

4. Shell Programming and Scripting

awk work with time change in csv file

Hi, i have csv input file looks like below 3rd field is date and time field i want to change it with user supplied date and time says year=2011 month=09 day=05 hour=11 count=2 when count is say 10 then first ten records should pick and it should increment the... (2 Replies)
Discussion started by: raghavendra.nsn
2 Replies

5. Shell Programming and Scripting

shell script to sort entries in a file by date and time

Hello All, Need a shell script to sort entries in a file by date and time. Below are the entries in the file, i need to sort it first by the date and then time Note :- Date is in MM/DD/YY format and date comes as the 6th & time comes on 7th coloumns respectively. 150 pbnawldb001-b... (10 Replies)
Discussion started by: ajiwww
10 Replies

6. Shell Programming and Scripting

Strip time from CSV File?

Hi, I've been trying (and failing miserably) all morning to strip from a CSV file the time from it. Can somebody point me in the right direction on how to do this using sed or awk? The file looks like: "James","07/20/2009-14:40:11" "Steve","08/06/2006-02:34:37"... (5 Replies)
Discussion started by: nmuntz
5 Replies

7. UNIX for Advanced & Expert Users

Time validation in a csv file

Hi, Im having a hard time in creating a script with the following conditions below. I have a csv file generated which is updated every 5 mins and it contains a timestamp in it. for example: time data 00:00 1 00:05 0 00:10 6 00:15 3 however, there is a time that... (4 Replies)
Discussion started by: mdap
4 Replies

8. Shell Programming and Scripting

Format a date in a csv file

So I have a csv file where the 3rd field is a date string in the format yyyy-mm-dd. I need to change it to mm/dd/yyyy. So each line in the csv file looks like: StringData,StringData,2009-02-17,12.345,StringData StringData,StringData,2009-02-16,65.789,StringData Any idea how I can keep... (5 Replies)
Discussion started by: rpiller
5 Replies

9. Shell Programming and Scripting

Compare date in a csv file

Hi all, how can I compare two defferent lines in a csv file, with the date values, and print the latest date! example: cat test.csv test1;whatever;Wed Nov 26 14:17:48 CET 2008 test1;whatever;Wed Nov 26 17:14:06 CET 2008 Any suggession? (4 Replies)
Discussion started by: research3
4 Replies

10. Shell Programming and Scripting

Processing a log file based on date/time input and the date/time on the log file

Hi, I'm trying to accomplish the following and would like some suggestions or possible bash script examples that may work I have a directory that has a list of log files that's periodically dumped from a script that is crontab that are rotated 4 generations. There will be a time stamp that is... (4 Replies)
Discussion started by: primp
4 Replies
Login or Register to Ask a Question