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.
LVL 26
Lionel MMSmall Business IT ConsultantAsked:
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.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Are you open to PowerShell too?
Bill PrewIT / Software Engineering ConsultantCommented:
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.

Lionel MMSmall Business IT ConsultantAuthor Commented:
sure if you don't mind explaining it to me--thanks
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Lionel MMSmall Business IT ConsultantAuthor 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 PrewIT / Software Engineering ConsultantCommented:
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...

Lionel MMSmall Business IT ConsultantAuthor 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.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
This is my PowerShell suggestion.
function replace-sku ([String] $property)
    $property -split ' ' |
    % {
      $s = $sku[$_]
      if ($s) { "$s," } else { $_ }
  ) -join ' '

$data = import-csv C:\temp\ee\Related-Product.csv

$sku = @{}
foreach ($row in $data) { $sku += @{ $row.images = $row.sku } }

foreach ($row in $data)
  $row.{Cross-sells} = replace-sku $row.{Cross-sells}
  $row.Upsells       = replace-sku $row.Upsells

$data | Export-CSV -NoType C:\temp\ee\Realted-Product-revised.csv

Open in new window

The resulting file will contain double quotes around all values (Export-CSV is doing that). Changing that means we have to create the formatted output ourself.

The script is indeed surprisingly complex, combining several "advanced" techniques.
(1-10) First we set up a function for later use, because we have to apply the same changes to two different columns. The function does not explicitly return anything (no return statement), so whatever output is created by the function code is returned as result. This is the default behaviour for PS in general.
So the function will return the result of all that in $(...). And that is first splitting the content at each space. We now have an array of strings, and no spaces (the delimiter is removed).
That is pushed down the pipe to be processed by the next command, % { ... }, which is an abbreviation for foreach-object and executing the part in curly braces for each individual object it gets via pipe. The current object is $_ by definition.
All we have to do now is to look up the image name, get the associated SKU, and if found replace; otherwise leave as-is.
As we have an array of strings now, we need to recollect them into a single string, and that is what -join ' ' does , adding spaces between the strings.

(12) Then we import the original file, considering it to be a regular CSV file with comma delimiters, optional double quotes around strings, and titles in the first line. The result is an array of objects, with the lines (rows) as index and the titles as properties. This means $data[0].sku is "061" (index starts with zero in PowerShell).

(14-15) This creates an empty hash table and fills it with image/sku pairs. That is, you can index into it with an image name to get the sku.

(17-21) This loops thru the lines (rows) and performs the replacements in two properties (columns). Note that because of the nature of PS functions, content provided as parameter is never directly modified, so we have to return a result in the function to reassign it to a variable (the column).

(23) Does the obvious dump of the final results into a standard CSV file. We need to provide -NoTypeInformation (here used abbreviated), otherwise PS would write a first line with .NET type info you usually cannot use anywhere but in PS itself, so its is useless most of the time.

Save the code into a .PS1 file somewhere, with the path names changed to your liking. Then start the PowerShell console, and type the full name of the script (you can use Tab key to let PS suggest the next best folder/file name fitting to what you already typed in).
After running the script, you can look at most of the variables used by just typing in their name, like $data.

Of course I could not explain everything, but I hope you at least get an idea how that code works ;-).

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
Lionel MMSmall Business IT ConsultantAuthor 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 ConsultantAuthor Commented:
Works great and so sorry I did not award point earlier--thanks for your time and explanations
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
Windows Batch

From novice to tech pro — start learning today.