Replace string in files located in specific folder

Luis Diaz
Luis Diaz used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
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
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
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Luis DiazIT consultant

Author

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 Consultant
Top Expert 2016

Commented:
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
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
Luis DiazIT consultant

Author

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 Consultant
Top Expert 2016

Commented:
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
Luis DiazIT consultant

Author

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.
Luis DiazIT consultant

Author

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.
IT / Software Engineering Consultant
Top Expert 2016
Commented:
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
Luis DiazIT consultant

Author

Commented:
Ok, got it I will tested and keep you informed.
Luis DiazIT consultant

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial