[SOLVED] Add string in CSV file via windows batchscript

Add string in CSV file via windows batchscript

I have a pipe delimited CSV file like below

METADATA|WorkTerms|PeriodOfServiceId
MERGER|WorkTerms|WT10127
METADATA|Assignment|WorkTermsAssignmentId|PeriodOfServiceId
MERGER|Assignment|WT10127|WR10127

I want a output like below

METADATA|WorkTerms|PeriodOfServiceId(SourceSystemId)
MERGER|WorkTerms|WT10127
METADATA|Assignment|WorkTermsAssignmentId(SourceSystemId)|PeriodOfServiceId(SourceSystemId)
MERGER|Assignment|WT10127|WR10127

I want to add (SourceSystemId) at specific places, can this be done via windows Batch script ?

Solution

Firstly, replace the name of myfile.csv in the fourth line only with the name of your CSV file and the path to the file in the third line. If the strings you want to replace are the only strings, do not touch anything else in the script.

@echo off
setlocal EnableExtensions EnableDelayedExpansion
set "mypath=C:\PATH\TO\CSV"
set "mycsv=myfile.csv"
move "%mypath%\%mycsv%" "%mypath%\oldmyfile.txt"

for /f "delims=" %%i in ('type "%mypath%\oldfile.txt"') do (
    set "str=%%i"
    set "edit=!str:PeriodOfServiceId=PeriodOfServiceId(SourceSystemId)!"
    set "edit=!edit:WorkTermsAssignmentId=WorkTermsAssignmentId(SourceSystemId)!"
    echo !edit!>>"%mypath%\%mycsv%"
)
del "%mypath%\oldfile.txt"

Here is what the script does: It renames the original file to oldfile.txt. Next it types each line captured by cmd.exe. Each non-empty line not beginning with a semicolon is assigned to an environment variable named str. Then a replacement of certain fields is made with resulting string assigned to the environment variable edit, but the fields that do not match the search string will remain unmodified. Finally it outputs the lines to the original file with the replacements.