How to change the format of the date column in a flat file?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to change the format of the date column in a flat file?
# 1  
Old 11-28-2013
How to change the format of the date column in a flat file?

Hi,

i have a flat file namely temp.txt with this data below
Code:
ID|name|contact_date
101|Kay|2013-12-26
102|let|2013-12-26

I need to modify the date data in the flat file into MM/DD/YYYY HH24:MI:SS format

let me know the code for this.

Thank you!

Last edited by Franklin52; 11-28-2013 at 03:31 AM.. Reason: Please use code tags
# 2  
Old 11-28-2013
With some assumptions,

Try this,

We have to do error handling in below code Smilie

Code:
while IFS="|" read a c b
do
[[ p=$(date -d "$b" +"%m/%d/%Y %r") ]] && print $a"|"$c"|"$p || print $a"|"$c"|"$b
done<file


Last edited by pamu; 11-28-2013 at 04:59 AM..
# 3  
Old 11-28-2013
Hi
In gnu sed and date:
Code:
sed -r 's/(([^\|]*\|){2})([0-9]{4}(-[0-9]{2}){2})/date -d "\3" +"\1%m\/%d\/%Y %r"/e' file

Regards.
# 4  
Old 11-28-2013
One more might be bad approach calling date inside awk and even lengthy...

Code:
$ cat file
ID|name|contact_date
101|Kay|2013-12-26
102|let|2013-12-26

Code:
awk '

function cmd(cmde,result){ 
                              while ((cmde | getline line) > 0)
                                  {
                                      result = result (result=="" ? "" : "\n")line
                                  }
                              close(cmde)
                              return result
                         }

                     NR>1{
                          $3 = cmd("date" " " "-d""\""$3"\"" " " "+%m/%d/%Y\\ %r")
                         }1
    ' FS="|" OFS="|"  file

Code:
ID|name|contact_date
101|Kay|12/26/2013 12:00:00 AM IST
102|let|12/26/2013 12:00:00 AM IST

# 5  
Old 11-28-2013
Not sure where to take the time from, so -
Code:
awk 'NR==1 {print;next} {split ($3, T, "-"); $3=sprintf("%s/%s/%s 00:00:00:00", T[2], T[3], T[1])}1' FS="|" OFS="|" file
ID|name|contact_date
101|Kay|12/26/2013 00:00:00:00
102|let|12/26/2013 00:00:00:00

# 6  
Old 11-28-2013
Yes no need to use date to reformat this one it's just a simple string manipulation, this is simplar to RudiCs:

Code:
awk -F\| 'NR>1{split($3,T,"-");$3=T[2]"/"T[3]"/"T[1]" 00:00:00"}1' OFS=\| infile

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Change date format in a file.

Hi all, I have a file as below, i would like the change the format of the time from "11/7/2019 20:12" to "2019-07-11 20:12:00" in the last coloumn. any awk solution on this. Input: 2,0,695016,1961612,497212,5800804,0,0,161,33,7605,12226,23,10,66,0,0,34,11/7/2019 20:10... (4 Replies)
Discussion started by: Raghuram717
4 Replies

2. 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

3. 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

4. Shell Programming and Scripting

Date format change in UNIX .dat file

Hi, I need help to convert the date format in .DAT file in unix. I want to convert 10@@|SWIFT MT568 Extract@@|Apr 14 2014 5:47:52:563PM@@|Apr 14 2014 4:33:47:663PM@@||##| into 10@@|SWIFT MT568 Extract@@|04/14/2014/ 5:47:52:563PM@@|04/14/2014 4:33:47:663PM@@||##| Appreciate... (18 Replies)
Discussion started by: karthikengox
18 Replies

5. Shell Programming and Scripting

File date format how to change

Hi All, Below are the unix files taken by the help of ls -lrt -rw-r--r-- 1 kbehera Domain Users 293 Jul 27 13:33 sand.txt -rw-r--r-- 1 kbehera Domain Users 4 Jul 27 13:37 sand1.txt -rw-r--r-- 1 kbehera Domain Users 293 Jul 27 15:30 new_sand.txt -rw-r--r-- 1 kbehera Domain Users 0 Jul 27... (2 Replies)
Discussion started by: krupasindhu18
2 Replies

6. Shell Programming and Scripting

Convert the date format from mdy to ymd in column of file

The date format in the delimited file for one column '6/27/2011 12:00:00 AM' Is it possible o change it to '2011-06-27 12:00:00 AM' for all the records.. Thanks in advance..... (8 Replies)
Discussion started by: infernalhell
8 Replies

7. UNIX for Advanced & Expert Users

Converting the date format in a flat file

Hi All, I am new to this forum, could any one help me out in resolving the below issue. Input of the flat file contains several lines of text for example find below: 5022090,2,4,7154,88,,,,,4/1/2011 0:00,Z,L,2 5022090,3,1,6648,88,,,,,4/1/2011 0:00,Z,,1... (0 Replies)
Discussion started by: av_sagar
0 Replies

8. Shell Programming and Scripting

How to change date format in file

Hello! I have a textfile that look like this: "83d1:46:2b";"20091008190000";"Rögle BK - Skellefteå";"Swedish" "d4c:46:21";"20091008190000";"Södertälje - Brynäs";"Swedish" "d4b:46:2";"20091008190000";"HV 71 - Färjestad";"Swedish" "838:46:b";"20091010160000";"Skellefteå - HV 71";"Swedish"... (2 Replies)
Discussion started by: condmaster
2 Replies

9. Shell Programming and Scripting

convert date format to mysql date format in log file

I have a comma delimited log file which has the date as MM/DD/YY in the 2nd column, and HH:MM:SS in the 3rd column. I need to change the date format to YYYY-MM-DD and merge it with the the time HH:MM:SS. How will I got about this? Sample input 02/27/09,23:52:31 02/27/09,23:52:52... (3 Replies)
Discussion started by: hazno
3 Replies

10. Shell Programming and Scripting

script to change the date format in a file

i have many files with date format of 6-9-2008 and i want a script that can change the format to 2008-06-09 Thanks (15 Replies)
Discussion started by: shehzad_m
15 Replies
Login or Register to Ask a Question