Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk help reformatting badly formatted time field

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk help reformatting badly formatted time field
# 1  
Old 01-31-2013
awk help reformatting badly formatted time field
I need help reformatting an input file with spaces in the time field (4th field). I want the field to look like “hh:mm” with appropriate embedded zeros, but instead it has “h :m “ if the hour and/or minute are single character.

I'm pretty new to scripting and this is beyond me. Any help would be appreciated.

Sample input file (tab delimited):
Code:
 
12345    553353  20130131             4 :59      1234
12346    553354  20130131             5 :9        1255
12347    553355  20130131             5 :10      1254
12533    553366  20130131             10:1       1667
12537    553389  20130131             10:15     1688


Desired output file (tab delimited):
Code:
 
12345    553353  20130131             04:59     1234
12346    553354  20130131             05:09     1255
12347    553355  20130131             05:10     1254
12533    553366  20130131             10:01     1667
12537    553389  20130131             10:15     1688

Any suggestions would be greatly appreciated.
Thanks,
Lise
# 2  
Old 01-31-2013
Code:
awk '{ gsub(/ ?:/, "\t"); printf("%s\t%s\t%s\t%02d:%02d\t%s\n", $1, $2, $3, $4, $5, $6) }' inputfile

These 2 Users Gave Thanks to Corona688 For This Post:
lisep manalisharmabe
# 3  
Old 01-31-2013
Code:
awk '{ sub(/ :/,":");d=$4;sub(/[0-9]+:/,"",d); $4=sprintf("%02d:%02d",$4,d); print $1,$2,$3,$4,$5; }' OFS='\t' filename

Last edited by Yoda; 01-31-2013 at 05:28 PM.. Reason: Fixed zero padding
These 2 Users Gave Thanks to Yoda For This Post:
lisep manalisharmabe
# 4  
Old 01-31-2013
Code:
sed '
  s/\([0-9]\) :/0\1:/
  s/:\([0-9]\) /:0\1/
  s/[ \t]\{1,99\}/\t/g
 ' in_file >out_file

The '\t' is a real tab.
This User Gave Thanks to DGPickett For This Post:
lisep
# 5  
Old 01-31-2013
Thanks, ALMOST working
Thanks very much to all - I tried your suggestions and both of the awk solutions work for the initial file I needed to process. The sed suggestion fixes the hour but not the minutes.

Another twist - I have multiple files that have incorrectly formatted times (all coming from the same source system) and the other files I need to correct have some fields that contain spaces.

The awk code splits the fields with spaces into separate fields (converts spaces to tabs).

How would I change this so it only updates the 4th column and doesn't affect the other fields?

Thanks again!
Lise

---------- Post updated at 02:22 PM ---------- Previous update was at 02:12 PM ----------

Disregard my last question - I was able to get that to work by adding
-F"\t" ... so here's what I ended up with that worked for all files, including those that have text fields with spaces:

Code:
awk -F"\t" '{ sub(/ :/,":");d=$4;sub(/[0-9]+:/,"",d); $4=sprintf("%02d:%02d",$4,d); print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12; }' OFS='\t' infile > outfile

This User Gave Thanks to lisep For This Post:
Corona688
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Access log field - using awk to pull date/time

hey guys. the following line is a line taken from apache's access_log 10.10.10.10 - jdoe "GET /images/down.gif HTTP/1.1" 304 I'm concerned about the field that has the date and time in it. if assuming the delimiter in the file is a space, then the fourth field will always have the date... (5 Replies)
Discussion started by: SkySmart
5 Replies

2. Shell Programming and Scripting

reformatting xml file, sed or awk I think (possibly perl)

I have some xml files that cannot be read using a standard parser, or I am using the wrong parser. The issues seems to be spaces in some of the tags. Here is a sample,<UgUn 2 > <Un> -0.426753 </Un> </UgUn>The parser isn't able to find the number 2, so that information is lost, etc. It seems... (16 Replies)
Discussion started by: LMHmedchem
16 Replies

3. Shell Programming and Scripting

awk multiple file reformatting

I hopefully have a simple request - I need to process multiple files reformatting the output based on tags at the beginning of each line. So the data for the new 3 lines of the output file are in the HDR line and then the details are in the DTL tagged lines. for ifile in $indir do echo... (1 Reply)
Discussion started by: jason_v_brown
1 Replies

4. Shell Programming and Scripting

unixtime to formatted date time

Hi, I need to take the unix time and format it to a date/time string like this yyyymmdd,hhmmss I'm wrting in shell but have tried calling perl, but all the perl options I found on here puts output to Thu Jan 1 00:00:00 1970 format. Any help? Cheers Neil (4 Replies)
Discussion started by: nhatch
4 Replies

5. Shell Programming and Scripting

Sorting on two fields time field and number field

Hi, I have a file that has data in it that says 00:01:48.233 1212 00:01:56.233 345 00:09:01.221 5678 00:12:23.321 93444 The file has more line than this but i just wanted to put in a snippet to ask how I would get the highest number with time stamp into another file. So from the above... (2 Replies)
Discussion started by: pat4519
2 Replies

6. Shell Programming and Scripting

output - tab formatted - awk

Dear All, Good Day. I would like to hear your suggestions for the following problem: I have a file with 5 columns with some numbers in 16 lines as shown below. Input file: Col 1 Col 2 Col 3 Col 4 Col 5 12 220 2 121 20 234 30 22 9... (3 Replies)
Discussion started by: Fredrick
3 Replies

7. UNIX for Dummies Questions & Answers

Problem with formatted printing in AWK

Hi, I want to print 130 fileds using formatted printing in AWK. It looks like awk '{printf ("%7.2f%7.2f...%7.2\n",$1,$2,...,$130)}' inflie>oufile But it gives me an error: Word too long! Can you please help me with this? Is there another way to do this? (1 Reply)
Discussion started by: PHL
1 Replies

8. Shell Programming and Scripting

Reformatting Data in AWK

Dear AWK Users, I have a data set that is so large (Gigabytes) that it cannot be opened in the vi editor in its entirety. But I can manipulate the entire thing in AWK. It is formatted in a regular manner such that it has the variable descriptions or listings preceeding the variables. The latter... (13 Replies)
Discussion started by: sda_rr
13 Replies

9. Shell Programming and Scripting

Formatted output - awk

Hi I have the following records in a file SABN YOURTUBE 000514 7256 SACN XYOUDSDF 000514 7356 SADN KEHLHRSER 000514 7656 SAEN YOURTUBE 000514 7156 SAFN YOURTUBE 000514 7056 I need to put this in the format like this printf '%s %-50s %6s %-6s\n' I am not going to read individual... (3 Replies)
Discussion started by: dhanamurthy
3 Replies

10. UNIX for Advanced & Expert Users

find formatted filename with date time

Hi, I operate and use HF radars along the California coast for ocean surface currents. The devices use Mac OS as the control and logging software. The software generates thousands of files a week and while I've used PERL in the past to solve the problems of finding files I come to realize some... (6 Replies)
Discussion started by: dpath2o
6 Replies
Login or Register to Ask a Question