Changing date from MM/DD/YY to MM/DD/YYYY format

[shaswat]

Hi Team,

I have a requirement where I need to change the date format of the columns. Here I have provided my script, input file, output file and the desired output file. Please suggest the changes required in my script to get the desired output. Currently I am getting the output in the MM/DD/YY format but my system only supports the MM/DD/YYYY format. So please help me in achieving the required result. Thanks...!!

Code:

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 "dateformat.csv"
for /f "tokens=1-8 delims=|" %%a in ('parseCSV.bat "/o:|" ^<dateformat.csv') do (
   echo "%%~a","%%~b","%%~c","%%~d","%%~e","%%~f","%%~g","%%~h">>output.csv
echo "Wait......Processing Files...!!!"
)

Input File:

Code: Select all

17-Aug-15,516121,PSAM,14-Aug-15,O,Option,42655,8/21/15
21-Jan-11,516121,PSAM,17-Aug-15,CU,Currency,42642,3/15/14
15-Dec-13,516121,PSAM,19-Aug-15,CU,Currency,42643,6/28/15

Output File:

Code: Select all

08/17/15,516121,PSAM,08/14/15,O,Option,42655,08/21/15
01/21/11,516121,PSAM,08/17/15,CU,Currency,42642,03/15/14
12/15/13,516121,PSAM,08/19/15,CU,Currency,42643,06/28/15

Desired Output File:

Code: Select all

08/17/2015,516121,PSAM,08/14/2015,O,Option,42655,08/21/2015
01/21/2011,516121,PSAM,08/17/2015,CU,Currency,42642,03/15/2014
12/15/2013,516121,PSAM,08/19/2015,CU,Currency,42643,06/28/2015

Thanks for your help and time..!!

Regards,
Shaswat

[penpen]

You could use "for /F" on the actual output string, and use the slash charracter ('/') as a delimiter.
Then write the topkens with the (now) missing slash characters and add "20" (without doublequotes) at the needed positions.

penpen

[Compo]

Although it will not give you the exact output you require, (since each field will be double quoted), this powershell script should output the correct data for you.
ReDateIt.ps1

Code: Select all

$cols = Import-CSV .\input.csv -Header A,B,C,D,E,F,G,H
foreach ($col in $cols)
{
   $col.A = Get-Date ([datetime]::ParseExact($col.A,"dd-MMM-yy",$null)) -Format "MM/dd/yyyy"
   $col.D = Get-Date ([datetime]::ParseExact($col.D,"dd-MMM-yy",$null)) -Format "MM/dd/yyyy"
   $col.H = Get-Date ([datetime]::ParseExact($col.H,"M/dd/yy",$null)) -Format "MM/dd/yyyy"
}
$cols | ConvertTo-CSV -notype | Select -Skip 1 | Set-Content .\output.csv

same rules as per my response in your last topic

[trebor68]

Here are two solutions.

Solution 1 can under certain circumstances produce erroneous information.
Errors come about when the sign "/", "-" or the comma "," within expressions are with double quotes.

The solution 2 can under certain circumstances produce erroneous information.
Errors come about when the comma "," is within expressions with double quotes.


Solution 1

Code: Select all

@echo off
setlocal EnableExtensions 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!"
)

rem 17-Aug-15,516121,PSAM,14-Aug-15,O,Option,42655,8/21/15
rem 17 Aug 15 516121 PSAM 14 Aug 15 O Option 42655 8 21 15
rem a  b   c  d      e    f  g   h  i j      k     l m  n

for /f "tokens=1-14 delims=-/," %%a in (Input62.txt) do (
  rem echo %%a %%b %%c  ##  !%%b!
  rem echo %%f %%g %%h  ##  !%%g!
  rem echo %%l %%m %%n
  rem echo.

  rem day %%a, %%f, %%m, and month %%l; when value = 1 ... 9 then 10x and the value is 0x; when value = 10 ... 31 then 10xx and the value is xx
  set vara=10%%a
  set varf=10%%f
  set varl=10%%l
  set varm=10%%m
  rem year %%c, %%h, %%n; when value = 00 ... 99 then the value is 19xx
  set varc=19%%c
  set varh=19%%h
  set varn=19%%n

  rem Correction of the year; 70 ... 99 = 1970 ... 1999 and 00 ... 69 = 2000 ... 2069
  set /a "varc=varc-((varc-1970)>>31)*100,varh=varh-((varh-1970)>>31)*100,varn=varn-((varn-1970)>>31)*100"

  rem echo !%%b!/!vara:~-2!/!varc!
  rem echo !%%g!/!varf:~-2!/!varh!
  rem echo !varl:~-2!/!varm:~-2!/!varn!
  rem echo.
  echo !%%b!/!vara:~-2!/!varc!,%%d,%%e;!%%g!/!varf:~-2!/!varh!,%%i,%%j,!varl:~-2!/!varm:~-2!/!varn!
  echo.
)

Solution 2

Code: Select all

@echo off
setlocal EnableExtensions EnableDelayedExpansion

rem 17-Aug-15,516121,PSAM,14-Aug-15,O,Option,42655,8/21/15
rem 17-Aug-15 516121 PSAM 14-Aug-15 O Option 42655 8/21/15
rem a         b      c    d         e f      g     h

for /f "tokens=1-8 delims=," %%a in (Input62.txt) do (
  set vara=%%a
  set vard=%%d
  set varh=%%h

  rem variable vara
  set varaDAY=0!vara:~0,-7!
  set varaHELP=!vara:~-6,3!
  set m=100
  for %%M in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do (
     set /a m+=1
     if !varaHELP!==%%M set "varaMONTH=!m:~1!"
  )
  set varaYEAR=!vara:~-2!
  rem echo !varaMONTH! !varaDAY:~-2! !varaYEAR!

  rem variable vard
  set vardDAY=0!vard:~0,-7!
  set vardHELP=!vara:~-6,3!
  set m=100
  for %%M in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do (
     set /a m+=1
     if !vardHELP!==%%M set "vardMONTH=!m:~1!"
  )
  set vardYEAR=!vard:~-2!
  rem echo !vardMONTH! !vardDAY:~-2! !vardYEAR!

  rem variable varh #  m/d/yy  or  mm/d/yy  or  m/dd/yy  or  mm/dd/yy
  set hvar5=!varh:~5,1!
  set hvar4=!varh:~4,1!
  set hvar3=!varh:~3,1!
  set hvar1=!varh:~1,1!
  if "!hvar5!"=="/" (set varhMONTH=!varh:~0,2!) & (set varhDAY=!varh:~3,2!)
  if "!hvar4!"=="/" if "!hvar1!"=="/" ((set varhMONTH=0!varh:~0,1!) & (set varhDAY=!varh:~2,2!)) else ((set varhMONTH=!varh:~0,2!) & (set varhDAY=0!varh:~3,1!))
  if "!hvar3!"=="/" (set varhMONTH=0!varh:~0,1!) & (set varhDAY=0!varh:~2,1!)
  set varhYEAR=!varh:~-2!
  rem echo !varhMONTH! !varhDAY! !varhYEAR!

  set /a varaYEAR+=1900, vardYEAR+=1900, varhYEAR+=1900
  rem Correction of the year; 70 ... 99 = 1970 ... 1999 and 00 ... 69 = 2000 ... 2069
  set /a "varaYEAR=varaYEAR-((varaYEAR-1970)>>31)*100,vardYEAR=vardYEAR-((vardYEAR-1970)>>31)*100,varhYEAR=varhYEAR-((varhYEAR-1970)>>31)*100"

  echo !varaMONTH!/!varaDAY:~-2!/!varaYEAR!,%%b,%%c,!vardMONTH!/!vardDAY:~-2!/!vardYEAR!,%%e,%%f,%%g,!varhMONTH!/!varhDAY!/!varhYEAR!

)

[Samir]

I have a batch that has a similar requirement. The easiest thing I found was to just use the replace.bat available here and run it on the output file to change the years that way.

The only thing you have to watch for is what you specify in the replace as it will also replace dates if you don't have a trailing , or space (for your output file).