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.

What field(s) in the CSV file contains the date(s) to be changed?

Are you just trying to change a particular date, or are you trying to change all dates in the file?

What operating system and shell are you using?

What have you tried?

I'm going to assume this is some sort of *nix environment due to the requirement for it to be in shell.
The date command can do this for you:

date -d "$yourolddateformat" +"%d/%b/%Y %H:%M:%S"

If you need to do this via shell (I'll use bourne as it's the only shell you have on all *nix's), you'll just need to do a little lookup:

month=`echo "$yourolddateformat" | cut -d '/' -f 1`
day=`echo "$yourolddateformat" | cut -d '/' -f 2`
therest=`echo "$yourolddateformat" | cut -d '/' -f 3`
case $month in
  01)
    monthname="Jan"
    ;;
  02)
    monthname="Feb"
    ;;
  03)
    monthname="Mar"
    ;;
  04)
    monthname="Apr"
    ;;
  05)
    monthname="May"
    ;;
  06)
    monthname="Jun"
    ;;
  07)
    monthname="Jul"
    ;;
  08)
    monthname="Aug"
    ;;
  09)
    monthname="Sep"
    ;;
  10)
    monthname="Oct"
    ;;
  11)
    monthname="Nov"
    ;;
  12)
    monthname="Dec"
    ;;
  *)
    exit 1
    ;;
esac
echo "${day}/${monthname}/$therest"

The date command is tested, the script is not - some debug may be required.
Some editing will be required to make it fit you larger needs of course too but the core should be enough to get you 95% there.

That will only work with the GNU date command; -d is not standard.

I doubt that OP is using a system without a POSIX shell, so there's no need for cut :

IFS=/ read month day year whatever <<.
$yourolddateformat
.

Or, in bash or ksh93 :

IFS=/ read month day year whatever <<< "$yourolddateformat"

---------- Post updated at 10:46 PM ---------- Previous update was at 10:36 PM ----------

I'd strongly recommend changing it to the ISO standard format:

YYYY-MM-DD_HH:MM:SS

(Strictly speaking, the standard uses T where I used an underscore, but that's a minor variation.)

1 Like

I think you meant to write:

IFS=/ read month day therest ...

no?

Indeed. Fixed.

Since the `/' is used as separator, the string can be only divided into three parts, making whatever useless, or in other words, there's no whatever; the year will receive the time as well.
Perhaps a minor modification.

IFS='/ ' read month day year time <<.
> $yourolddateformat
> .

Hi Smiling Dragon, cfajohnson, Scrutinizer, & Aia,
If you look closely at the 1st post in this thread, you'll notice that the request is to find timestamps in unspecified fields in a CSV formatted file where the input timestamps are in the format MM/DD/YYYY HH:MM:SS and the output timestamps are supposed to be one month later in the format DD/Mon/YYYY HH:MM:SS .

So, there is no reason to believe that using / as a field separator on an input line will yield only three fields, that the first field will just contain a month number, nor that the third field will only contain a year and a timestamp. None of the code suggested so far makes any attempt to adjust the output month, to adjust the day field if the output month has fewer days than the input month, nor to adjust the year if the input month is 12 .

Three days ago (less than an hour after the original post), I asked the submitter to provide us with more details and to show us what had been tried to solve this problem. I figured that the code that would be shown to us would clarify if the conversion from month number 06 to abbreviated month name Jul was intentional or a typo, would clarify if the field separator in the CSV file format was a comma or some other character, and would clarify whether every input field needed to be processed or if only certain fields need to be processed. Unfortunately, I am still waiting for a response.

1 Like

I'm choosing to assume the month mismatch is an error on OPs part (dangerous I know but they've only talked about format in the text).
I've deliberately written the snippet to expect a date string and nothing else. As we've not seen any examples of the input stream I figure OP can sort that out :wink:

I hang my head in shame for using a GNU date feature, I actually hadn't realised it didn't exist on the bog-standard date <wrist slap>

Why? The GNU utilities are so ubiquity that chances are that it would work for the OP as well as for anyone searching how to do something similar. In fact, you have used the GNU date all this time, without knowing you could have been using another crippled version.

s/crippled/standard/
1 Like

Exactly, it can be substituted, since in the context I wrote it, it is interchangeable.

I am waiting for Don to appear. :wink:

It seems that you don't care about portable code. It seems that you don't care about making suggestions that will work on any system likely to be used by the readers of this forum. It seems that you frequently make suggestions without testing them and believe that readers should be smart enough to ignore the suggestions you make that might not work on their system (on any system).

But, there are many users on these forums (including me) who are using UNIX systems that do not include GNU date .

Don,

I do accept you have the right to interpret the rules as it suits you and when it suits you, as moderator. But I have not denigrated any user. Smiling Dragon made a comment I thought it was too hard on himself/herself, and I responded with my sincere opinion that I thought it was valuable his/her contribution. Of course, a smart-ass innuendo remark had it to be made about my chosen word "crippled".

I think this is a fair question, although I risk taking us a long way off topic here :wink:

The thing to remember is that most Unix is still not Linux and that many Unixes that are not Linux do not implement GNU versions of the commands by default. One reason that I could suggest (I don't know for sure) is that not all Unix is free and therefor would clash with the GPL.

If you can solve something for every platform, then wonderful!
If you can solve something for most platforms, then that's still pretty good
If you have to solve something for a small number of platforms, then OK, but you should get to this conclusion as a last resort.

1 Like

Hi.

A utility, dadd, in package dateutils can do the arithmetic and reformatting. It seems to be in many repositories ( e.g. arch, FreeBSD, Slackware, Mac OS X "brew" etc.), and source is available (github). I compiled it for an earlier version of Debian.

A single command suffices to process all conforming dates:

#!/usr/bin/env bash

# @(#) s1	Demonstrate date reformat, arithmetic, dateutils, dadd.
# "dadd" is part of dateutils, repository Debian/Jessie, MiNT, # Ubuntu, etc.
# Also: http://www.fresse.org/dateutils.

# 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 dadd

FILE=${1-data1}

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

# In : MM/DD/YYYY HH:MM:SS
# Out: DD/Mon/YYYY HH:MM:SS
pl " Results:"
dadd -S -i '%m/%d/%Y' +1mo -f '%d/%b/%Y' < $FILE

exit 0

producing:

$ ./s1

Environment: LC_ALL = C, LANG = C
(Versions displayed with local utility "version")
OS, ker|rel, machine: Linux, 2.6.26-2-amd64, x86_64
Distribution        : Debian 5.0.8 (lenny, workstation) 
bash GNU bash 3.2.39
dadd 0.2.6

-----
 Input data file data1:
First line, no dates, some blank lines appear.
Today was: Mon Jul  6 08:18:06 CDT 2015

06/23/2015 20:59:12
Now is the time 06/23/2015 20:59:12 for all good men

No dates here.
When was 06/23/2015 20:59:12, another 06/23/2015 20:59:12, not 2015.06.23 20:59:12

Last line, no dates.

-----
 Results:
First line, no dates, some blank lines appear.
Today was: Mon Jul  6 08:18:06 CDT 2015

23/Jul/2015 20:59:12
Now is the time 23/Jul/2015 20:59:12 for all good men

No dates here.
When was 23/Jul/2015 20:59:12, another 23/Jul/2015 20:59:12, not 2015.06.23 20:59:12

Last line, no dates.

Sample input and output are best to supply with the original question, otherwise responders need to create them, which may or may not match what is really needed.

Best wishes ... cheers, drl

1 Like

Using ksh93 builtin printf is powerful date / time handler. Same date format values as in date command. Input format supported us standard month/day/year or iso YYYY-MM-DD or epoc or ...

LC_ALL=C
export LC_ALL
fromdate="04/25/2015 12:01:02"
printf "%(%d/%b/%Y %H:%M:%S)T\n" "$fromdate"
year=$(printf "%(%Y)T\n" "$fromdate"
# ...

# current time, builtin value "now"
printf "%(%d/%b/%Y %H:%M:%S)T now" 
# epoc
epoc=$(printf "%(%s)T" now)
# date counting using epoc
((day=60*60*24))
#...