Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Most vexing: Sed or Awk scripting for date conversion needed

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Most vexing: Sed or Awk scripting for date conversion needed
# 1  
Old 09-11-2011
Tools Most vexing: Sed or Awk scripting for date conversion needed
Hi,

I have some files being sent to me that have dates in them in this format:
from
1/8/2011 15:14:20


and I need the dates in this format (mysql date format)
To
2011-01-08 15:14:20
all I have so far is the regexp that detects the format:
Code:
sed -r -e 's@[0-9]\1/[0-9]\2/[0-9]\3[0-9]\4[0-9]\5[0-9]\6[[:space:]][0-9][0-9]:[0-9][0-9]:[0-9][0-9]$@do something@'

How does one change places of the data in Sed so that it swaps the locations of the year and replaces the slashes with hyphens?
Last edited by pludi; 09-11-2011 at 07:45 PM..
# 2  
Old 09-11-2011
Hello, Astrocloud:

I think the easiest solution is some AWK pixie dust. The following one-liner not only shuffles the date components, but it makes certain to zero-pad them (1 to 01, 8 to 08, etc):
Code:
awk -F'/| ' '{printf("%.4d-%.2d-%.2d %s\n", $3, $1, $2, $4)}'

Regards and welcome to the forum,
Alister
# 3  
Old 09-12-2011
Thanks!

I am guilty however of "dumbing down" the problem.

I have a csv file like the following:
Code:
"104787","MILLER,BOB M.","","Automatic refund. Expected over-payment","67.47","A","225","","128110048","1/8/2011 15:14:20"
"104196","REBECCA M MANNERS","","Automatic refund. Expected over-payment","53.21","A","225","","128110047","12/28/2011 15:14:20"
"104415","LEMMING,LETTUCE","","Automatic refund. Expected over-payment","257.03","A","225","","128110046","7/22/2011 15:14:20"
"104535","BUYTRON, LATOYA","","Automatic refund. Expected over-payment","281.51","A","225","","128110045","10/13/2011 15:14:20"
"104698","Jesus Rodriguez","Chase Bank","Prorate for december. 512-784-2711","200.00","N","225","","128110049","12/25/2011 15:14:20"

I am in the process of modifying your script... Perhaps I need better documentation on Awk... but basically I'm getting stuck.

How do you separate by [quote][comma][quote] "," rather than merely [comma] ,
# 4  
Old 09-12-2011
To separate on a longer string, make the separator the longer string...
awk -v FS="\",\"" ...
# 5  
Old 09-12-2011
Code:
root@bt:/tmp# echo '"104787","MILLER,BOB M.","","Automatic refund. Expected over-payment","67.47","A","225","","128110048","1/8/2011 15:14:20"' 
| awk -F[\",] '{print $(NF-1)}'

1/8/2011 15:14:20

# 6  
Old 09-12-2011
Using Perl -

Code:
$
$ cat csv.txt
"104787","MILLER,BOB M.","","Automatic refund. Expected over-payment","67.47","A","225","","128110048","1/8/2011 15:14:20"
"104196","REBECCA M MANNERS","","Automatic refund. Expected over-payment","53.21","A","225","","128110047","12/28/2011 15:14:20"
"104415","LEMMING,LETTUCE","","Automatic refund. Expected over-payment","257.03","A","225","","128110046","7/22/2011 15:14:20"
"104535","BUYTRON, LATOYA","","Automatic refund. Expected over-payment","281.51","A","225","","128110045","10/13/2011 15:14:20"
"104698","Jesus Rodriguez","Chase Bank","Prorate for december. 512-784-2711","200.00","N","225","","128110049","12/25/2011 15:14:20"
$
$
$ perl -ne 'chomp;
            m|^(.*)\b(\d+)/(\d+)/(\d+)\b(.*)$|;
            printf ("%s%04d-%02d-%02d%s\n", $1,$4,$3,$2,$5)
           ' csv.txt
"104787","MILLER,BOB M.","","Automatic refund. Expected over-payment","67.47","A","225","","128110048","2011-08-01 15:14:20"
"104196","REBECCA M MANNERS","","Automatic refund. Expected over-payment","53.21","A","225","","128110047","2011-28-12 15:14:20"
"104415","LEMMING,LETTUCE","","Automatic refund. Expected over-payment","257.03","A","225","","128110046","2011-22-07 15:14:20"
"104535","BUYTRON, LATOYA","","Automatic refund. Expected over-payment","281.51","A","225","","128110045","2011-13-10 15:14:20"
"104698","Jesus Rodriguez","Chase Bank","Prorate for december. 512-784-2711","200.00","N","225","","128110049","2011-25-12 15:14:20"
$
$

tyler_durden
# 7  
Old 09-12-2011
How about plain old sed...
Code:
sed 's;\([0-9][0-9]*\)/\([0-9][0-9]*\)/\([0-9][0-9]*\);\3-\2-\1;' file
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Date format conversion how to change this from using nawk to awk

Hi, I have a file where I need to change the date format on the nth field from DD-MM-YYYY to YYYY-MM-DD so I can accurately sort the record by dates From regex - Use sed or awk to fix date format - Stack Overflow, I found an example using nawk. Test run as below: $: cat xyz.txt A ... (2 Replies)
Discussion started by: newbie_01
2 Replies

2. Shell Programming and Scripting

Bash Scripting with date format conversion

I have a script below and wanted to change the output into three different file format (3 separate script) #!bin/bash #input file format postwrf_d01_20131206_0600_f08400.grb2 #postwrf_d01_YYYYMMDD_ZZZZ_f0HHHH.grb2 #zzzz= 0000,0600,1200,1800 (in UTC) #HHHH=00000,00600,01200,01800 ..ect (in... (1 Reply)
Discussion started by: cumulus_255
1 Replies

3. Shell Programming and Scripting

another vexing awk problem

Hello; I have the following commnad: ls -lrt /ulogs/dblocks_logs/*log |awk '{print "grep Total "$9" |tail -20"}' | sh 05/10/11 21:54:01 Total number of locks:0 05/10/11 21:55:01 Total number of locks:0 05/10/11 21:56:01 Total number of locks:2 05/10/11 21:57:00 Total number of... (8 Replies)
Discussion started by: delphys
8 Replies

4. UNIX for Advanced & Expert Users

Date Conversion on output string from awk

Hi, I would like to convert the output from awk function to date and print on the screen. Example : echo "Start Date: May 24 2010" | gawk -F": " '{print $2}' Output : May 04 2010 I want this to be converted to 2010/05/24 Can i use date function here and how? Thanks, Deepika (2 Replies)
Discussion started by: deepikad
2 Replies

5. Shell Programming and Scripting

help needed using awk scripting

Hi, I've few files in the following format file 1 ------------------------- A=a1 B=0 C=1 D=3 A=a2 B=0 C=1 D=3 A=a3 B=0 C=6 D=7 A=a4 B=0 C=1 D=3 A=a5 B=0 C=1 D=3 file 2 ------------------------- A=a1 B=2 C=2 D=4 A=a2 ... (2 Replies)
Discussion started by: skpvalvekar
2 Replies

6. Shell Programming and Scripting

Wiki conversion with Awk or Sed

I have the words in twiki format that I want to convert to mediawiki format like below : %BLUE%some words1%ENDCOLOR% bla bla blab labdad sdadasd adsasdads oerdkfj kdfjs %PINK%some wordks2 123.4.5.6/26%ENDCOLOR%, ksdjak dkasjd kjfrjkfgjdkfgjdfkgjdgdfgdgf %PURPLE%1.2.3.4/28%ENDCOLOR%, dskd... (3 Replies)
Discussion started by: rk4k
3 Replies

7. Shell Programming and Scripting

sed/shell scripting - add line if needed and not allready there

I am writing a shell script that checks all .c files to see if they use fprintf or printf. If a file does, then the line #include <stdio.h> is added to the top of the file, unless it's already there. This is what I've got: #!/bin/sh egrep -l f?printf *.c | while read file; do sed -i '1i\... (2 Replies)
Discussion started by: computethis
2 Replies

8. Shell Programming and Scripting

sed or awk scripting help needed

hi all, for an example : df -k output shows: $ df -k Filesystem 1K-blocks Used Available Use% Mounted on /dev/cciss/c0d0p6 3099260 1117760 1824068 8% / /dev/cciss/c0d0p1 256666 18065 225349 8% /boot none 8219180 0 8219180 0% /dev/shm /dev/mapper/vglocal-home 1032088 245172 734488 26%... (7 Replies)
Discussion started by: raghur77
7 Replies

9. Shell Programming and Scripting

awk script for date conversion

hi awk script for dd/mm/yyyy to yyyymmdd awk script for dd-mon-yyyy to yyyymmdd awk script for dd-mm-yyyy to yyyymmdd formate ..............urgent............. Thanks in advanced (2 Replies)
Discussion started by: charandevu
2 Replies

10. Shell Programming and Scripting

Help needed - Replacing all date & time occurrences in a file with a string using Sed

Hi, I am new to using Sed. I have a file containg lines like the following: INFORM----Test.pc:168:10/11/05 12:34:26 > some text goes here.. TRACE-----Test.pc:197:10/11/05 12:34:26 > some text goes here.. My requirement is to replace 10/11/05 12:34:26 with a string <RUNDATE> (including <... (4 Replies)
Discussion started by: Hema_M
4 Replies
Login or Register to Ask a Question