I have a CSV file and need to change the date format in each record within a specific field. The file has headers and is comma delimited.
I need to change this:
Despatch_date [date],Order_Number,Name,Tags,Tags Command,Command
23/07/2025,186999,17000004531,Backorder Scheduled,UPDATE,UPDATE
17/08/2025,187607,17000011058,Backorder Scheduled,UPDATE,UPDATE
To this:
Despatch_date [date],Order_Number,Name,Tags,Tags Command,Command
07/23/2025,186999,17000004531,Backorder Scheduled,UPDATE,UPDATE
08/17/2025,187607,17000011058,Backorder Scheduled,UPDATE,UPDATE
Can anybody help me with a script I can use in a .bat file to run through the CSV file and change the format of the date fields?
@ECHO Off
SETLOCAL ENABLEDELAYEDEXPANSION
rem The following settings for the directories and filenames are names
rem that I use for testing and deliberately includes spaces to make sure
rem that the process works using such names. These will need to be changed to suit your situation.
SET "sourcedir=u:\your files"
SET "destdir=u:\your results"
SET "filename1=%sourcedir%\q79704618.txt"
SET "outfile=%destdir%\outfile.txt"
SET "line="
(
FOR /f "usebackqdelims=" %%e IN ("%filename1%") DO (
IF DEFINED line (
SET "line=%%e"
SET "line=!line:~3,3!!line:~0,2!!line:~5!"
ECHO !line!
) ELSE (
ECHO %%e
)
SET "line=Y"
)
)>"%outfile%"
GOTO :EOF
Always verify against a test directory before applying to real data.
Note that if the filename does not contain separators like spaces, then both usebackq
and the quotes around %filename1%
can be omitted.
You would need to change the values assigned to sourcedir
and destdir
to suit your circumstances. The listing uses a setting that suits my system.
I deliberately include spaces in names to ensure that the spaces are processed correctly.
I used a file named q79704618.txt
containing your data for my testing. I assume a simple comma between fields and that no "Poison characters" (characters that have a special meaning to the batch language) are present in the data
Produces the file defined as %outfile%
Simply read each line, having set the variable line
initially to nothing
On the header line, line
is set to nothing so the ECHO %%e
is executed. line
is then set to Y
(or anything other than nothing).
On subsequent lines, since line
will be not-nothing the line will be mangled, rebuilt such that its 3rd to 6th character (starts at character "0") is concatenated with the first two and then the 5th to end, then echo
ed.