windowscsvdatebatch-file

Changing the date format in a CSV file using a Windows Batch Script


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?


Solution

  • @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 echoed.