Replace string in files located in specific folder

Hello experts,
I have various csv files in a specific folder I would like to:
-Replace the value 05 by 06 in column C
-Script should have SourceString TargetString.
I attached dummy file.
Windows batch, Powershell, VBscript and MS VBA approaches are more than welcome.
If you have questions, please contact me.
Thank you.
Dummy-file.csv
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.

Bill PrewIT / Software Engineering ConsultantCommented:
Here is a simple BAT script approach...

@echo off
setlocal

set FileIn=in.txt
set FileOut=out.txt
set SourceString=5
set TargetString=6

(
    for /f "tokens=1-3* delims=;" %%A in ('type "%FileIn%"') do (
        if "%%C" EQU "%SourceString%" (
            echo %%A;%%B;%TargetString%;%%D
        ) else (
            echo %%A;%%B;%%C;%%D
        )
    )
)>"%FileOut%

Open in new window


»bp
FlysterCommented:
Here's an Excel Macro that will open all workbooks in a specified folder. I modified it to replace the column C value as requested. Be sure to change "Filepath" to the correct path for your files.

Sub ReplaceValues()
Dim MyFile As String
Dim Filepath As String

Filepath = "C:\Define\Path\Here\Your_Folder\"

MyFile = Dir(Filepath)

Do While Len(MyFile) > 0

Workbooks.Open (Filepath & MyFile)
   Columns("C:C").Select
   Selection.Replace What:="5", Replacement:="6", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
MyFile = Dir

Loop

End Sub

Open in new window


Paul
leflonCommented:
LD16,

I try to get into Powershell.So here is my try using that.
param(
[string]$FileIn,
[int]$Index,
[string]$From,
[string]$To)

Get-Content $FileIn | ForEach-Object {
	$elements = $_.Split(';')
	if ($elements[$Index] -eq $From) { $elements[$Index] = $To }
	$elements -join ';'
}

Open in new window


I call it like .\ReplaceX.ps1 .\Sample.csv 2 5 6.

hth
leflon
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

LD16Author Commented:
Thank you all.
@Bill: I don't see Folder variable in your proposal. The aim is not to replace a single file but the various files located in a specific folder.
@Flyster: If I have more than 50 files to loop, are you sure this is the right way to do it through excel? Open and close the file. Is there another way to it this?
@leflon: Sams comment as the one adressed to Bill.
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, this will process all CSV files.  I added "_new" to the end of the existing filenames to get the new files with the updates in them.

@echo off
setlocal

set BaseDir=B:\EE\EE29147704\Data
set SourceString=5
set TargetString=6

(
    for %%A in ("%BaseDir%\*.csv") do (
        for /f "tokens=1-3* delims=;" %%a in ('type "%%~A"') do (
            if "%%c" EQU "%SourceString%" (
                echo %%a;%%b;%TargetString%;%%d
            ) else (
                echo %%a;%%b;%%c;%%d
            )
        )
    )>"%%~dpnA_new%%~xA"
)

Open in new window


»bp
leflonCommented:
And here's my PS version

param(
[string]$Folder,
[int]$Index,
[string]$From,
[string]$To)

Get-ChildItem $Folder -Filter *.csv | Foreach-Object {
	$fn = $_.FullName
	Get-Content $fn | ForEach-Object {
		$elements = $_.Split(';')
		if ($elements[$Index] -eq $From) { $elements[$Index] = $To }
		$elements -join ';'
	} | Out-File -FilePath $fn'.new'
}

Open in new window


called like .\ReplaceX.ps1 d:\temp 2 5 6.

First parameter is now directory. Result will go same filename with '.new' as new extension. Files are in same directory as source csv files.

leflon
LD16Author Commented:
@Bill: I tested your proposal but I got duplicate lines in new file as reported in screenshot: 2019-05-31_20h22_53.pngTarget file should contains exactly the same information as source file the unique difference is values reported in column C are replaced by 06.
Also the best will be to generate files in a different folder to don't mix source and new files. New files should be generated in a new folder.
Thank you.
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, this should handle those changes.

@echo off
setlocal

set BaseDir=B:\EE\EE29147704\In
set DestDir=B:\EE\EE29147704\Out
set SourceString=5
set TargetString=6

if not exist "%BaseDir%" (
    echo *ERROR* Base folder does not exist: "%BaseDir%"
    exit /b
)

if not exist "%DestDir%" (
    echo *ERROR* Destination folder does not exist: "%DestDir%"
    exit /b
)

for %%A in ("%BaseDir%\*.csv") do (
    for /f "tokens=1-3* delims=;" %%a in ('type "%%~A"') do (
        if "%%c" EQU "%SourceString%" (
            echo %%a;%%b;%TargetString%;%%d
        ) else (
            echo %%a;%%b;%%c;%%d
        )
    )
)>"%DestDir%\%%~nxA"

Open in new window


»bp
LD16Author Commented:
Hello Bill,
I tested but I got csv files in out folder with the new string.
Here is my version.
@echo off
setlocal

set BaseDir=%CD%\in
set DestDir=%CD%\Out
set SourceString=5
set TargetString=6

if not exist "%BaseDir%" (
    echo *ERROR* Base folder does not exist: "%BaseDir%"
    exit /b
)

if not exist "%DestDir%" (
    echo *ERROR* Destination folder does not exist: "%DestDir%"
    exit /b
)

for %%A in ("%BaseDir%\*.csv") do (
    for /f "tokens=1-3* delims=;" %%a in ('type "%%~A"') do (
        if "%%c" EQU "%SourceString%" (
            echo %%a;%%b;%TargetString%;%%d
        ) else (
            echo %%a;%%b;%%c;%%d
        )
    )
)>"%DestDir%\%%~nxA"

Pause

Open in new window

If you are able to review, could you please add if out folder doesn't exist create it?
Thank you very much for your help.
LD16Author Commented:
I tested with the following adjustment on line 21 and it works:
if %%c EQU %SourceString%  

Open in new window

I would like to know if this is ok. C column and Source string shouldn't be double quoted?
Thank you in advance for your help.
Bill PrewIT / Software Engineering ConsultantCommented:
I tested but I got csv files in out folder with the new string.
Isn't that the desired result, the files in the output folder with the changes made???
If you are able to review, could you please add if out folder doesn't exist create it?
Adjusted, see below.
I would like to know if this is okay. C column and Source string shouldn't be double quoted?
If the values in the C column and the SourceString variable do not contain double quotes then either what I proposed or what you changed it to should work.  Having the double quotes in the IF protects against a syntax error if the C column were ever blank, so I always use the IF with quotes.

@echo off
setlocal

set BaseDir=B:\EE\EE29147704\In
set DestDir=B:\EE\EE29147704\Out
set SourceString=5
set TargetString=6

if not exist "%BaseDir%" (
    echo *ERROR* Base folder does not exist: "%BaseDir%"
    exit /b
)

if not exist "%DestDir%" (
    echo *WARNING* Destination folder does not exist, creating it: "%DestDir%"
    md "%DestDir%"
)

for %%A in ("%BaseDir%\*.csv") do (
    for /f "tokens=1-3* delims=;" %%a in ('type "%%~A"') do (
        if "%%~c" EQU "%SourceString%" (
            echo %%a;%%b;%TargetString%;%%d
        ) else (
            echo %%a;%%b;%%c;%%d
        )
    )
)>"%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:
Ok, got it I will tested and keep you informed.
LD16Author Commented:
Bill, I tested again with "" and I don't have the replacement.
When I remove "" I have the replacement.
Anyway your proposal with adaptation work for me.
Thank you again for your help.
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
Powershell

From novice to tech pro — start learning today.