Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Date conversion DD-MMM-YY to YYYYMMDD for many columns in a file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Date conversion DD-MMM-YY to YYYYMMDD for many columns in a file
# 1  
Old 01-11-2016
Date conversion DD-MMM-YY to YYYYMMDD for many columns in a file
Hi,

Input

Code:
Col1|col2|col3|col4|col5|col6-------col26
1|2|3|10-Nov-67|10-Nov-97|4|5-------100
1|2|3|11-Feb-01|01-Dec-15|4|5-------2000


Output
Code:
Col1|col2|col3|col4|col5|col6-------col26
1|2|3|19671110|19971010|4|5-------100
1|2|3|20010211|20151201|4|5-------2000

I want to convert the date format for col2,col3,col21.Tried with below command for one column 4 but i am unable to disply the value same position.

Code:
awk -F '|' '{printf("%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|",$1,$2,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26);system("date -d "$4" +%Y%m%d")}'

Thanks
Suri
Last edited by jim mcnamara; 01-14-2016 at 09:05 PM..
# 2  
Old 01-11-2016
How about
Code:
awk -F"|" '

BEGIN   {for (n=split ("Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec", T); n>0; n--) M[T[n]] = sprintf ("%02d", n)
        }

function CV(DT) {split (DT, T, "-")
                 return (T[3]>25?"19":"20")T[3]M[T[2]]T[1]
                }

NR > 1  {$4  = CV($4)
         $5  = CV($5)
         $21 = CV($21)
        }
1
' OFS="|" file4
Col1|col2|col3|col4|col5|col6-------col26
1|2|3|19671110|19971110|4|5-------100||||||||||||||20
1|2|3|20010211|20151201|4|5-------2000||||||||||||||20

# 3  
Old 01-14-2016
Hi,

Date utility collection dateutils, can handle situations like this. Here's a demonstration, but note that this version of dconv will fail on some 2-digit year data. This is an issue with Debian being behind, the author has added an option to handle many 2-digit years correctly.
Code:
#!/usr/bin/env bash

# @(#) s1	Demonstrate date conversion, dateutils, dconv.
# Warning:
# dateutils earlier than 0.3.3 will not convert correctly some
# 2-digit years by default, as is noted here for year "67".

program=dconv
program=dateutils.dconv
# Utility functions: print-as-echo, print-line-with-visual-space, debug.
# export PATH="/usr/local/bin:/usr/bin:/bin"
LC_ALL=C ; LANG=C ; export LC_ALL LANG
pe() { for _i;do printf "%s" "$_i";done; printf "\n"; }
pl() { pe;pe "-----" ;pe "$*"; }
db() { ( printf " db, ";for _i;do printf "%s" "$_i";done;printf "\n" ) >&2 ; }
db() { : ; }
C=$HOME/bin/context && [ -f $C ] && $C $program

FILE=${1-data1}

pl " Input data file $FILE:"
cat $FILE

pl " Expected output:"
cat expected-output.txt

pl " Results:"
input='%d-%b-%y'
pe " Input format is \"$input\""
$program -i "$input" -f '%Y%m%d' -S < $FILE

exit 0

producing:
Code:
$ ./s1

Environment: LC_ALL = C, LANG = C
(Versions displayed with local utility "version")
OS, ker|rel, machine: Linux, 3.16.0-4-amd64, x86_64
Distribution        : Debian 8.2 (jessie) 
bash GNU bash 4.3.30
dateutils.dconv dconv 0.3.1

-----
 Input data file data1:
Col1|col2|col3|col4|col5|col6-------col26
1|2|3|10-Nov-67|10-Nov-97|4|5-------100
1|2|3|11-Feb-01|01-Dec-15|4|5-------2000

-----
 Expected output:
Col1|col2|col3|col4|col5|col6-------col26
1|2|3|19671110|19971010|4|5-------100
1|2|3|20010211|20151201|4|5-------2000


-----
 Results:
 Input format is "%d-%b-%y"
Col1|col2|col3|col4|col5|col6-------col26
1|2|3|20671110|19971110|4|5-------100
1|2|3|20010211|20151201|4|5-------2000

I hesitated to post code that doesn't work correctly with the user data, but the usefulness of the dateutils collection is appropriate, especially because the issue can be resolved with a more recent version than I have at my disposal.

The collection can be found in many repositories.

Best wishes ... cheers, drl
# 4  
Old 01-15-2016
Code:
perl -nlpe '
BEGIN{ @m{qw/Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec/}=(1..12) }
s/(\d{2})-(\w{3})-(\d{2})/sprintf "%d%02d%02d", ($3>38?$3+1900:$3+2000),$m{$2},$1/ge' onesuri.file

Code:
Col1|col2|col3|col4|col5|col6-------col26
1|2|3|19671110|19971110|4|5-------100
1|2|3|20010211|20151201|4|5-------2000

Last edited by Aia; 01-15-2016 at 12:23 AM..
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 to be changed from DDMMYYYY to YYYYMMDD

My requirement is:- there will be files at a location each day with the date format DDMMYYYY. Novawise_Activity_Call_Notes_04022013.txt Novawise_Activity_Inbound_04022013.txt Novawise_Activity_Inbound_05022013.txt Novawise_Activity_Call_Notes_05022013.txt... (8 Replies)
Discussion started by: djrulz123
8 Replies

2. Shell Programming and Scripting

perl one-liner to get yesterday's date in format dd-MMM-yy (i.e. 01-JAN-12)

I have the following perl one-liner to get yesterday's date, but I would like it in the form of dd-MMM-yy (for example: 01-JAN-12). Can someone alter the below code so I get the format I want? Also, could someone also give me a line for dd-Mmm-yy (for example 01-Jan-12)? Code: YEST=`perl -w... (3 Replies)
Discussion started by: thibodc
3 Replies

3. Shell Programming and Scripting

Find first created file date in YYYYMMDD format

Hi All, We are copying all the files into ARCHIVE directory after we process them. We are doing this process from last 2 years, now we have a lot of files in ARCHIVE directory. Now I need to find when the first file is copied into this directory? If I Issue, ls -l /ARCHIVE/*.* | tail -1... (3 Replies)
Discussion started by: Raamc
3 Replies

4. Shell Programming and Scripting

Date after 5 days from current date in YYYYMMDD format

Hello Experts, How do i get date after 5 days from current date in YYYYMMDD format? How do you compare date in YYYYMMDD format? Thanks (8 Replies)
Discussion started by: needyourhelp10
8 Replies

5. Solaris

Date after 5 dates in YYYYMMDD format

Hi Experts, How to get date 5 days after current date in YYYYMMDD format? How do we compare date in YYYYMMDD format? Thanks (1 Reply)
Discussion started by: needyourhelp10
1 Replies

6. Shell Programming and Scripting

Date string conversion within a file

Hi, I have a log file that contains information along the lines of the following: ========= jobnumber 322761 start_time Tue May 19 19:42:37 2009 end_time Tue May 19 20:11:28 2009 failed 0 ========= jobnumber 322762 start_time Tue May 19 19:39:51 2009 end_time ... (4 Replies)
Discussion started by: chrissycc
4 Replies

7. UNIX for Dummies Questions & Answers

Format date from MM/DD/YYYY to YYYYMMDD

I have a file with some date columns in MM/DD/YYYY format: SMPBR|DUP-DO NOT USE|NEW YORK||16105|BA5270715|6/6/2007 |MWERNER|109||||JOHN||SMITH|MD|72211118||||||74559|21 WILMINGTON RD||D|11/6/2003|SL# MD CONTACT-LIZ RICHARDS|||0|Y|N||1411458| And I want to convert the date format to: ... (5 Replies)
Discussion started by: ChicagoBlues
5 Replies

8. Shell Programming and Scripting

Using awk to convert DD-MMM-YY to YYYYMMDD

Hi i need to convert a date in the format DD-Mon-YY to YYYYDDMM Ex : 01-JUL-00 to 20000701 Can anybdy help me with this?? Thanks in advance Shenaz (5 Replies)
Discussion started by: shanu_85
5 Replies

9. HP-UX

How can i get the yesterday's date in YYYYMMDD format

How can i get the yesterday's date in YYYYMMDD format??? (4 Replies)
Discussion started by: prasadsr
4 Replies

10. UNIX for Dummies Questions & Answers

get yesterday date in yyyymmdd format

I would like to know how I could get a yesterday date in yyyymmdd e.g. today is 20011109, and I would like to get 20011108. Thank you!:confused: (2 Replies)
Discussion started by: hk_newbie
2 Replies
Login or Register to Ask a Question