Avatar of Lionel MM
Lionel MM
Flag 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
Windows BatchVBA

Avatar of undefined
Last Comment
Lionel MM

8/22/2022 - Mon
Qlemo

Are you open to PowerShell too?
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
Lionel MM

ASKER
sure if you don't mind explaining it to me--thanks
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Lionel MM

ASKER
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
Bill Prew

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
Lionel MM

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lionel MM

ASKER
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.
Lionel MM

ASKER
Works great and so sorry I did not award point earlier--thanks for your time and explanations