Changing the date format in the columns of a CSV file

[dbenham]

Here is an efficient solution using JREPL.BAT that should work with any CSV, regardless where the date fields appear

It performs the following conversions:
D-Mon-YY ==> MM/DD/YY
M/D/YY ==> MM/DD/YY

The only restriction is the dates must not be enclosed by quotes, though that could be handled with small modifications.

Code: Select all

@echo off
setlocal enableDelayedExpansion

set m=100
for %%M in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do (
   set /a m+=1
   set "%%M=!m:~1!"
)

::   1      2    3    4    5          6     
set "find1=(^|,)(\d)?(\d)-([a-z]{3})-(\d\d)(?=,|$)"
set "repl1=env($5)?($2+env($5)+'/'+($3?$3:'0')+$4+'/'+$6):$0"

::   7      8    9    10   11   12
set "find2=(^|,)(\d)?(\d/)(\d)?(\d/\d\d)(?=,|$)"
set "repl2=$8+($9?$9:'0')+$10+($11?$11:'0')+$12"

call jrepl "%find1%@%find2%" "%repl1%@%repl2%" /i /j /t @ /f "input.csv" /o "output.csv"

input.csv

Code: Select all

7-Aug-15,516121,PSAM,8/21/15,O,Option,42655,14-Aug-15
10-Jan-11,516121,PSAM,17-Aug-15,CU,Currency,42642,3/5/14
15-Dec-13,516121,PSAM,19-Aug-15,CU,Currency,42643,10/28/15
1-May-15
5/1/15
5-Bad-15

output.csv

Code: Select all

08/07/15,516121,PSAM,08/21/15,O,Option,42655,08/14/15
01/10/11,516121,PSAM,08/17/15,CU,Currency,42642,03/05/14
12/15/13,516121,PSAM,08/19/15,CU,Currency,42643,10/28/15
05/01/15
05/01/15
5-Bad-15

Dave Benham

[shaswat]

Dave Benham

Thanks a lot for your help. With your help I could convert the D-Mon-YY ==> MM/DD/YY format. But currently in some of my requirement I need this as MM/DD/YYYY format. Is there any way that I can convert my MM/DD/YY to MM/DD/YYYY format. Or it will be nice if I can directly convert D-Mon-YY to MM/DD/YYYY format. Please help me out in this. Thanks a lot...!!

Regards,
Shaswat