DosTips.com

A Forum all about DOS Batch
https://www.dostips.com/forum/

Changing the date format in the columns of a CSV file

https://www.dostips.com/forum/viewtopic.php?f=3&t=6678

Page 1 of 2

Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 09:12
by 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

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 09:45
by 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.

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 10:22
by shaswat
Squashman wrote: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.

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 10:32
by Squashman
Post your code and we will guide you on what you are doing wrong.

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 11:06
by shaswat
Squashman wrote: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..!!

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 11:28
by 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
)

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 13:01
by shaswat
Squashman wrote: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..!!

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 13:07
by 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.

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 13:24
by shaswat
Squashman wrote: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

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 13:34
by 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

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 14:13
by 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
)

Re: Changing the date format in the columns of a CSV file

Posted: 22 Sep 2015 20:28
by Aacini
@Squashman, I admire the patience you have in your explanations! :D

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! :mrgreen:

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

Re: Changing the date format in the columns of a CSV file

Posted: 23 Sep 2015 06:40
by Squashman
Aacini wrote:

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.

Re: Changing the date format in the columns of a CSV file

Posted: 23 Sep 2015 16:23
by 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

Re: Changing the date format in the columns of a CSV file

Posted: 23 Sep 2015 17:15
by Aacini
penpen wrote: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! :D

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
All times are UTC-06:00
Page 1 of 2
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/