Windows batch & Vbscript: replace values of specific columns with YYYYMMDD value

Hello experts,
I have a bunch of csv files located in C:\csv
I need to make sure that column I & M have the YYYYMMDD value. The rest of values will remain the same.
The idea is to have script  which reads the various files  in C:\csv, generates revised version of files as specified in previous point (YYYYMMDD value for column I & M)  in C:\out .
Columns involved by the change I & M should be related to a variable.
I attached dummy file.
If you have questions, please contact me.
Thank you in advance for your help.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CDRetiredCommented:
Apparently there is a bug in EE whee sometimes attachments don't get saved. Try it again as there is no attachment. Where is the date coming from? Is it a DATETIME or is it a string?
LD16Author Commented:
Hello,
Here is csv file attached.
Date is current date so it is a datetime.
If we are planning to use powershell we can define as datestring as following:
$DateStamp = (Get-Date -Format "yyyyMMdd")

Open in new window

Else with windows batch like this:
REM Get current date/time
set LocalDateTime=
for /f "tokens=* skip=1" %%A in ('wmic os get LocalDateTime') do (
    if not defined LocalDateTime (
        set LocalDateTime=%%A
    )
)
REM Format current date/time as YYYYMMDD_hhmmdd
set VAR_MYDATE=%LocalDateTime:~0,8%_%LocalDateTime:~8,6%

Open in new window

Thank you in advance for your help.
Dummy-file.csv
Bill PrewIT / Software Engineering ConsultantCommented:
Here is a BAT approach that seems to do what you wanted.

@echo off
setlocal EnableDelayedExpansion

REM Define file locations
set BaseDir=B:\EE\EE29149053\In
set DestDir=B:\EE\EE29149053\Out

REM Get current date/time
set LocalDateTime=
for /f "tokens=* skip=1" %%A in ('wmic os get LocalDateTime') do (
    if not defined LocalDateTime (
        set LocalDateTime=%%A
    )
)
REM Format current date/time as YYYYMMDD
set LocalDateTime=%LocalDateTime:~0,8%

REM Process each CSV file
for %%A in ("%BaseDir%\*.csv") do (
    (
        REM Process each line of file, parse at semi-colons
        set Header=Y
        for /f "tokens=1-13* delims=;" %%a in ('type "%%~A"') do (
            if "!Header!" EQU "Y" (
                REM Don't change header row
                set Header=N
                echo %%a;%%b;%%c;%%d;%%e;%%f;%%g;%%h;%%i;%%j;%%k;%%l;%%m;%%n
            ) else (
                REM Place current date (YYYYMMDD) in columns I and M
                echo %%a;%%b;%%c;%%d;%%e;%%f;%%g;%%h;%LocalDateTime%;%%j;%%k;%%l;%LocalDateTime%;%%n
            )
        )
    )>"%DestDir%\%%~nxA"
)

Open in new window


»bp
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

LD16Author Commented:
Thank you very much Bill, possible to add a log for success processing, in folder doest exist, out folder doesn't exist?
If out folder doesn't exist script should create it.
Thank you.
Bill PrewIT / Software Engineering ConsultantCommented:
Try this.

@echo off
setlocal EnableDelayedExpansion

REM Define file locations
set BaseDir=B:\EE\EE29149053\In
set DestDir=B:\EE\EE29149053\Out
set LogFile=%~dp0logfile.txt

REM Get current date/time
set LocalDateTime=
for /f "tokens=* skip=1" %%A in ('wmic os get LocalDateTime') do (
    if not defined LocalDateTime (
        set LocalDateTime=%%A
    )
)
REM Format current date/time as YYYYMMDD
set LocalDateTime=%LocalDateTime:~0,8%

REM Make sure base folder exists
if not exist "%BaseDir%" (
    echo *ERROR* Base folder does not exist "%BaseDir%"
    exit /b
)

REM Make sure dest folder exists
if not exist "%DestDir%" (
    md "%DestDir%"
    if not exist "%DestDir%" (
        echo *ERROR* Count not create destination folder "%BaseDir%"
        exit /b
    )
)

if exist "%LogFile%" del "%LogFile%"

REM Process each CSV file
for %%A in ("%BaseDir%\*.csv") do (
    echo Processing file "%%~A">>"%LogFile%"
    (
        REM Process each line of file, parse at semi-colons
        set Header=Y
        for /f "tokens=1-13* delims=;" %%a in ('type "%%~A"') do (
            if "!Header!" EQU "Y" (
                REM Don't change header row
                set Header=N
                echo %%a;%%b;%%c;%%d;%%e;%%f;%%g;%%h;%%i;%%j;%%k;%%l;%%m;%%n
            ) else (
                REM Place current date (YYYYMMDD) in columns I and M
                echo %%a;%%b;%%c;%%d;%%e;%%f;%%g;%%h;%LocalDateTime%;%%j;%%k;%%l;%LocalDateTime%;%%n
            )
        )
    )>"%DestDir%\%%~nxA"
)

Open in new window


»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Hello Bill,
Your proposal works.
Thank you very much for your help.
For my knowledge. How the script is able to re-write the various columns from A to T though in the script your report till column N:
echo %%a;%%b;%%c;%%d;%%e;%%f;%%g;%%h;%LocalDateTime%;%%j;%%k;%%l;%LocalDateTime%;%%n

Open in new window

I am asking this if I want to use the same replace column mechanism for other case.
Normally I should report the various columns or just the last one before the column which is going to be replaced, in that case column N?
Thank you very much for your help.
Bill PrewIT / Software Engineering ConsultantCommented:
To answer your question we have to look at the FOR /F that is reading in the columns from the CSV file:

for /f "tokens=1-13* delims=;" %%a in ('type "%%~A"') do (

Notice that it specifies tokens=1-13*.  What this means in the FOR /F statement is to parse the input lines by semi-colon (specified in the delims=;), and assign values 1 through 13 to the loop variables, in this case starting at %%a.  So that would assign the first 13 values to %%a through %%m.

The * at the end of the tokens=1-13* now is important to note.  It specifies that after the first 13 delimited values, take the entire remaining content of the line and assign to the next loop variable, in this case %%n.  When this is done the delimiter is not used, rather everything after the 13th value is placed in one last loop variable.  This will include all remaining values, and any remaining semi-colons as well.

Let's look at an example from your actual data:

The input line was:

AU_Australia;ENERGY_BRISBANE;AU0101;AU018017;none;AU0101_AU018017;92.52;20190601;20190603;1465;1.02;11295.15;20190601;50498;FR01AR;Project managers;265.03;Connected Products;EUR;Modification

Open in new window

And after parsing in the FOR /F loop the variable assignments would have been:

%%a = AU_Australia
%%b = ENERGY_BRISBANE
%%c = AU0101
%%d = AU018017
%%e = none
%%f = AU0101_AU018017
%%g = 92.52
%%h = 20190601
%%i = 20190603
%%j = 1465
%%k = 1.02
%%l = 11295.15
%%m = 20190601
%%n = 50498;FR01AR;Project managers;265.03;Connected Products;EUR;Modification

Open in new window

Let me know if this helps and clears things up.


»bp
LD16Author Commented:
Ok, it is clear now. Thank you very much for your help!
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome.


»bp
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Batch

From novice to tech pro — start learning today.