Datestamp format 2nd change in csv file (awk or sed)

Shell Programming and Scripting
1

I have a csv file formatted like this:

2014-08-21 18:06:26,A,B,12345,123,C,1232,26/08/14 18:07

and I'm trying to change it to MM/DD/YYYY HH:MM for both occurances.
I have got this:

awk -F, 'NR <=1 {print;next}{"date +%d/%m/%Y\" \"%H:%m -d\""$1 "\""| getline dte;$1=dte}1' OFS="," test.csv

This changes and formats the first occurance, but how do I convert the second one at the same time?

I'm also hitting this error

awk: cmd. line:1: (FILENAME=test.csv FNR=258) fatal: cannot create child process for `date +%d/%m/%Y" "%H:%m -d"2014-04-15 14:14:11"' (fork: Resource temporarily unavailable)

Is awk exhausting all my memory?

In fact, whilst that command runs on one flavour of unix, it doesn't run on redhat :-(. The first field is null.

Thanks in advance

2

What Operating System your machine is running on?

uname -a
3

Linux (Redhat)

reports as

Linux hostname 2.6.9-104.ELsmp #1 SMP Wed May 9 19:42:36 EDT 2012 i686 i686 i386 GNU/Linux

I can use bash, ksh, or sh ...

the fork error was on cygwin, so you can ignore that one...

4

Ok, the ksh93 builtin printf includes a %T formatting option.

For the sample input you posted, try something like:-

#!/bin/ksh93

while IFS="," read d1 v1 v2 v3 v4 v5 v6 d2
do
        printf "%(%m-%d-%Y %H:%M)T,%s,%(%m-%d-%Y %H:%M)T\n" "$d1" "$v1,$v2,$v3,$v4,$v5,$v6" "${d2:6:2}-${d2:3:2}-${d2:0:2} ${d2:9}"
done < file.csv
5 
akshay@nio:/tmp$ cat file.csv
2014-08-21 18:06:26,A,B,12345,123,C,1232,26/08/14 18:07

akshay@nio:/tmp$ cat date_format.awk
function fmt(v,reverse){

	split(v,dt,"[-/ :]")	
	str=dt[1]" "dt[2]" "dt[3]" "dt[4]" "dt[5]" "dt[6]
	
	if(reverse)
	{
		dt[3] += (dt[3] >=0 && dt[3] <=69) ? 2000 : (dt[3]>=70 && dt[3] <= 99) ? 1900 : 0

		str = dt[3]" "dt[2]" "dt[1]" "dt[4]" "dt[5]" " ( length(dt[6])?dt[6]:0 )
	}

	return strftime("%d/%m/%Y %H:%m",mktime(str))
}
{
	$1 = fmt($1) 
	$8 = fmt($8,1)
}1

Resulting

akshay@nio:/tmp$ awk -vFS="," -f date_format.awk file.csv
21/08/2014 18:08 A B 12345 123 C 1232 26/08/2014 18:08
1 Like
6

This one certainly won't win the beauty contest, but it might work for you.

sed -r "s/^([0-9]{4})-([0-9]{2})-([0-9]{2}) (.....)...(,.*,)([0-9]{2})\/([0-9]{2})/\2-\3-\1 \4\5\7\/\6/"

:smiley: :rolleyes:

7

I don't have ksh93, only straight ksh which returns

./csvsplit2.ksh[6]: : bad substitution

---------- Post updated at 11:20 AM ---------- Previous update was at 10:28 AM ----------

I think something is not quite right as the times are different in the result. Ah.. %m not %M. Thanks... all working now.