Changing the date format in the columns of a CSV file

[shaswat]

Hi Team,

I have a requirement where I need to change the date format of the columns. Here I have provided the input file and the desired output file. Please suggest me a script that will do this work.

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

Required 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

Thanks for your help and time..!!

Regards,
Shaswat

[Squashman]

Antonio gave you the code to change the date format in your previous thread!
viewtopic.php?p=42984#p42984
You have been given several examples of all the commands you need to use in all your previous threads. Please make an attempt to solve it yourself.

Give a man a fish, and you feed him for a day; show him how to catch fish, and you feed him for a lifetime.

[shaswat]

Antonio gave you the code to change the date format in your previous thread!
viewtopic.php?p=42984#p42984
You have been given several examples of all the commands you need to use in all your previous threads. Please make an attempt to solve it yourself.

Hi Squashman,
I did try to do it myself as per the examples and help from you people, but in this case there is not a single date value. Every row is having multiple dates and the dates varies from each row. The previous example was for a single date format conversion and putting that converted date value in all the rows of a given column. I tried and failed so I have posted my query here. The output I was getting from my script is no where similar to by desired output. So I have not posted my script here as I though I am going in a completely wrong approach.

So I am here asking the help from you people.

[Squashman]

Post your code and we will guide you on what you are doing wrong.

[shaswat]

Post your code and we will guide you on what you are doing wrong.

Hi, Please find the attached code, input file and the output file that i'm getting while executing the script.
Script:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

set m=100
for %%a in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do (
   set /A m+=1
   set "month[%%a]=!m:~1!"
)
< in_date.csv ( set /P "line=" )
for /F "tokens=1-8 delims=- " %%a in ("%line%") do set "myDate=!month[%%b]!/%%a/%%c"

rem Process the file
for /F " tokens=1-8 delims=," %%a in (in_date.csv) do >>out_date.csv echo "%myDate%,%%b,%%c,"%myDate%,%%e,%%f,%%g,"%myDate%
pause

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 that I am getting:

Code: Select all

"08/17/15,516121,PSAM,14,516121,PSAM,"08/17/15,516121,PSAM,14,O,Option,42655,"08/17/15,516121,PSAM,14
"08/17/15,516121,PSAM,14,516121,PSAM,"08/17/15,516121,PSAM,14,CU,Currency,42642,"08/17/15,516121,PSAM,14
"08/17/15,516121,PSAM,14,516121,PSAM,"08/17/15,516121,PSAM,14,CU,Currency,42643,"08/17/15,516121,PSAM,14

As you can see all the dates are changed with the single date. Please suggest the change required in my script. Thanks..!!

[Squashman]

This line you do not need. This line of code was specific to using your HEADER files. You need to edit the tokens in the file as they are being parsed.

Code: Select all

< in_date.csv ( set /P "line=" )

This line of code needs to be used twice. Instead of using %line%, you are going to use your token values from your FOR /F command that reads your input file. Which should be %%a and %%d. You will use this code twice and have it create a variable called myDate1 and myDate2. The Tokens and delims should be adjusted according to your existing date format.

Code: Select all

for /F "tokens=1-8 delims=- " %%a in ("%line%") do set "myDate=!month[%%b]!/%%a/%%c"

The above line of code will be used twice inside this FOR /F command.

Code: Select all

for /F " tokens=1-8 delims=," %%a in (in_date.csv) do (
     First FOR /F command to get mydate1
     Second FOR /F command to get mydate2
     >>out_date.csv echo !myDate1!,%%b,%%c,!myDate2!,%%e,%%f,%%g,%%h
)

[shaswat]

The above line of code will be used twice inside this FOR /F command.

As per your suggestion I have done few changes but I think I'm wrong as the output I'm getting after executing the script is shown below:

Code: Select all

",516121,PSAM,",O,Option,42655,"8/21/15
",516121,PSAM,",CU,Currency,42642,"3/15/14
",516121,PSAM,",CU,Currency,42643,"6/28/15

My Script:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

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

for /F " tokens=1-8 delims=," %%a in (in_date.csv) do (
for /F "tokens=1-8 delims=- " %%a in (in_date.csv) do set "myDate1=!month[%%b]!/%%a/%%c"
for /F "tokens=1-8 delims=- " %%d in (in_date.csv) do set "myDate2=!month[%%b]!/%%a/%%c"

>>out_date.csv echo "%myDate1%,%%b,%%c,"%myDate2%,%%e,%%f,%%g,"%%h
)
pause

Please advice the change required to get the desired output. Thanks..!!

[Squashman]

I specifically said to replace "%line%" in Antonio's code with "%%a" and "%%d".
%%a is your first date in your data.
%%d is the second date in your data.

You also did not adjust the TOKENS and DELIMS options per my advice for those two FOR /F commands.

You are also not using the mydate1 and mydate2 variables correctly. Look at my example.

[shaswat]

Look at my example.

I have replaced the %line% with %%a and %%d for date1 and date2 respectively, but still the script is somehow not correct. Could you please let me know where I'm going wrong?

Code: Select all

@echo off
setlocal EnableDelayedExpansion

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

for /F " tokens=1-8 delims=," %%a in (in_date.csv) do (
for /F "tokens=1-8 delims=- " %%a in ("%%a") do set "myDate1=!month[%%b]!/%%a/%%c"
for /F "tokens=1-8 delims=- " %%d in ("%%d") do set "myDate2=!month[%%b]!/%%a/%%c"

>>out_date.csv echo "%myDate1%,%%b,%%c,"%myDate2%,%%e,%%f,%%g,"%%h
)
pause

[Squashman]

Code: Select all

for /F "tokens=1-8 delims=- " %%a in ("%%a") do set "myDate1=!month[%%b]!/%%a/%%c"

If you split up the date field with a hyphen as the delimiter how many TOKENS are there?
You also are using a space as one of the delimiters. Is there any spaces in your dates?

Code: Select all

for /F "tokens=1-8 delims=- " %%d in ("%%d") do set "myDate2=!month[%%b]!/%%a/%%c"

If you split up the date field with a hyphen as the delimiter how many TOKENS are there?
You also are using a space as one of the delimiters. Is there any spaces in your dates?
You changed the FOR variable from %%a to %%d in Antonio's example. Change it back to %%a. I only told you to change %line%.
If you use %%d as your FOR variable then you would need to use %%e and %%f when you set your myDate2 variable.
So just change it back to %%a like the original code showed.

Code: Select all

>>out_date.csv echo "%myDate1%,%%b,%%c,"%myDate2%,%%e,%%f,%%g,"%%h

Look at my example above. What is different with the myDate1 and myDate2 variables

[Squashman]

Probably late for you over there in India.
So here is your code. Hope you learned something.

Code: Select all

@echo off
setlocal EnableDelayedExpansion

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

for /F " tokens=1-8 delims=," %%a in (in_date.csv) do (
   for /F "tokens=1-3 delims=-" %%a in ("%%a") do set "myDate1=!month[%%b]!/%%a/%%c"
   for /F "tokens=1-3 delims=-" %%a in ("%%d") do set "myDate2=!month[%%b]!/%%a/%%c"
   >>out_date.csv echo !myDate1!,%%b,%%c,!myDate2!,%%e,%%f,%%g,%%h
)

[Aacini]

@Squashman, I admire the patience you have in your explanations!

I think it would be clearer if different replaceable parameters are used in the FOR's that convert the dates (I used %%i, %%j and %%k in the code below); I also suggest to use the output to file method of this answer:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

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

(for /F "tokens=1-8 delims=," %%a in (in_date.csv) do (
   for /F "tokens=1-3 delims=-" %%i in ("%%a") do set "myDate1=!month[%%j]!/%%i/%%k"
   for /F "tokens=1-3 delims=-" %%i in ("%%d") do set "myDate2=!month[%%j]!/%%i/%%k"
   echo !myDate1!,%%b,%%c,!myDate2!,%%e,%%f,%%g,%%h
)) > out_date.csv

After that, perhaps the next code segment will cause more OP questions!

Code: Select all

(for /F "tokens=1-8 delims=," %%a in (in_date.csv) do (
   for /F "tokens=1-6 delims=-" %%i in ("%%a-%%d") do (
      echo !month[%%j]!/%%i/%%k,%%b,%%c,!month[%%m]!/%%l/%%n,%%e,%%f,%%g,%%h
   )
)) > out_date.csv

Antonio

[Squashman]

Code: Select all

(for /F "tokens=1-8 delims=," %%a in (in_date.csv) do (
   for /F "tokens=1-6 delims=-" %%i in ("%%a-%%d") do (
      echo !month[%%j]!/%%i/%%k,%%b,%%c,!month[%%m]!/%%l/%%n,%%e,%%f,%%g,%%h
   )
)) > out_date.csv

Antonio

Love that! One less FOR /F should speed things up considerably on large files.

[penpen]

If there are no '-' chars except in the calendar data that should be changed, then you could reduce to one for loop:
Just use "delims=,-".


penpen

[Aacini]

If there are no '-' chars except in the calendar data that should be changed, then you could reduce to one for loop:
Just use "delims=,-".


penpen

I like it!

Code: Select all

(for /F "tokens=1-12 delims=-," %%a in (in_date.csv) do (
   echo !month[%%b]!/%%a/%%c,%%d,%%e,!month[%%g]!/%%f/%%h,%%i,%%j,%%k,%%l
)) > out_date.csv

Antonio