We help IT Professionals succeed at work.

Find and Replace Batch File

Lionel MM
Lionel MM asked
on
153 Views
Last Modified: 2018-02-22
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
Comment
Watch Question

Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
Are you open to PowerShell too?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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 MMSmall Business IT Consultant
CERTIFIED EXPERT

Author

Commented:
sure if you don't mind explaining it to me--thanks
Lionel MMSmall Business IT Consultant
CERTIFIED EXPERT

Author

Commented:
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 PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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 MMSmall Business IT Consultant
CERTIFIED EXPERT

Author

Commented:
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.
"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Lionel MMSmall Business IT Consultant
CERTIFIED EXPERT

Author

Commented:
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 MMSmall Business IT Consultant
CERTIFIED EXPERT

Author

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