Link to home
Start Free TrialLog in
Avatar of Lionel MM
Lionel MMFlag for United States of America

asked on

Find and Replace Batch File

I am trying to take a value in a CSV file and then search the rest of the file for that value and replace it with another value.  This is a small portion of the file (see attached); so on line one you have these values
"061", "Blaze",      "blaze.jpg","015, 019, postman-joyner.jpg" & "aaron.jpg gingerland.jpg candidum-jr.jpg"
I want to take the 3rd value (blaze.jpg) and then look for blaze.jpg in 4th and 5th columns and in all the rows of those two values/columns and replace it with "061" -- the first value in the row. The goal is to end up with no names in values 4 or 5 (or columns 4 & 5) and have them replaced by values from the first value. So wherever the name blaze.jpg shows up in the rest of the file, have it replaced by the corresponding value in value/column 1 -- hope that makes sense. I did a few of them manually so you can see what happens in columns 4 & 5.
Related-Product.csv
Avatar of Qlemo
Qlemo
Flag of Germany image

Are you open to PowerShell too?
Avatar of Bill Prew
Bill Prew

PowerShell would probably be the preferred play here, since you have commas as field separators, and also embedded in quoted fields.  Neither BAT or VBS will handle those without lots of gymnastics.

You could load into Excel and do the work in VBA there, but feels a lot heavier than what I'll bet Qlemo could whip up in a modest sized PowerShell script.


»bp
Avatar of Lionel MM

ASKER

sure if you don't mind explaining it to me--thanks
Bill i put the quotes there for you to see the different "columns" if you look at the attached file there are no " just commas which makes it tough to tell where one set of values start and ends
Could you post a true sample of the input file, with no editing?  I saw the following, but it sounds like this is not real data?

"015, 019, postman-joyner.jpg"

If the commas are only between fields then BAT could be an option...


»bp
sorry Bill you're right I was talking about the "'s in the explanation above -- I thought those were the quotes you were saying is going to be a problem and not the quotes for separating one set or row of data from another.
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That works--amazing--don't know how you keep all these many conditions in mind--thanks. and thanks for the explanation--at least now I can see what each command is doing--double thanks for taking the time for that--appreciate it.
Works great and so sorry I did not award point earlier--thanks for your time and explanations