Create, Find & Replace

I have a CSV file with several columns and 9 rows (master.csv). I need to end up with this data, all the data in all the columns, replicated 53 times into one file, Then I need to get values from a second file (list.csv) and have some of the data in file1 (master.csv) replaced in four of its columns with data from the second file. The four columns (or comma separated values) are SKU, Parent, Name, Description. Once we have a file with the data replicated 53 times we will need to find the first 9 instance of LMM in columns SKU & Parent (columns 1 and 2) and replace it with the value in line 1, column 1 from list.csv. Then we need to replace the first 9 instances of "Blazers" in columns 3 and 4 (Name & Description) with the value from  line 1, column 2 from list.csv and continue to do so for the all 53 lines in list.csv (the next 9 instances with line 2, the following 9 with line 3, etc.). The attached example file result.csv is what we need to end up with. If you compare it, result.csv, to master.csv you will see only the data in SKU, Parent, Name, Description changed and it changed with data from line 1 of list.csv. Hoping someone has an easier way that to manually use "find and replace". Thanks.
master.csv
list.csv
result.csv
LVL 26
Lionel MMSmall Business IT ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

aikimarkCommented:
replicated 53 times into one file
Your result example does not reflect this requirement.
0
Lionel MMSmall Business IT ConsultantAuthor Commented:
that is part of the question how to replicate the master files with its 9 rows 53 times, either to create 53 files with the same 9 rows or into one file with 53 x 9 rows -- whatever would make it easier for whoever can provide me with a solution.
0
aikimarkCommented:
Please post a result that reflects the problem solution data state.
0
Defend Against the Q2 Top Security Threats

Were you aware that overall malware worldwide was down a surprising 42% from Q1'18? Every quarter, the WatchGuard Threat Lab releases an Internet Security Report that analyzes the top threat trends impacting companies worldwide. Learn more by viewing our on-demand webinar today!

Lionel MMSmall Business IT ConsultantAuthor Commented:
if I could post that result I would not to ask this question so you must totally not understand the question
0
aikimarkCommented:
If you have the two files, you should be able to copy/paste and do some find/replace operations on the result.

I do NOT understand your problem, given the result file you posted.  There is no discern-able "53 copies" in that result.
0
Lionel MMSmall Business IT ConsultantAuthor Commented:
open all 3 files in Excel--then look at the file named master.csv-- you will see in columns 2 & 3 values with LMM in them; then if you look in the file list.csv you will see column 1 then in file result.csv you see that the information in columns 2 & 3 have changed from LMM to the value on line 1 in column 1 in list.csv (from LMM to 061) -- you will also see that in columns 5 & 6 the word "Blazers" in those two columns have been replaced with the data from line 1 column 2 in list.csv (from Blazers to Blaze). All other data in all other columns remain unchanged. I need that process to be done for all the rows in list.csv so end the end I would end up with 9 rows for 53 items in list.csv
0
Lionel MMSmall Business IT ConsultantAuthor Commented:
If you look at this attached file (my crude attempt to create 53 duplicates of the 9 lines) you will see a repeated pattern of LMM and Blazers. The first 9 need to be replaced with data from line 1 in list.csv, & the next 9 replaced with data in line 2 & in the 3rd nine with data from line 3 all the way down to the 53rd set of 9 replaced with data from line 53 in list.csv
529-rows.csv
1
aikimarkCommented:
Please do the replacement for the first three sets of lines and repost.
0
Lionel MMSmall Business IT ConsultantAuthor Commented:
Thanks for trying to help--here are the first 3 sets as requested
First-3-Sets.csv
0
aikimarkCommented:
So, the first thing that needs to happen is to change the master.  You had a string (LMM) for the SKU replacement.  I've added a new string (LMM_name) for the name replacement.  Is this an acceptable change to the master file?
master.csv
0
Lionel MMSmall Business IT ConsultantAuthor Commented:
yes, anything that will work--I just put LMM in there as a way to find and replace. Blazers is also like that just something in the master file do do a search and replace on
0
aikimarkCommented:
The following PS script produces the uploaded output file
$master = import-csv -Path '\users\mark\downloads\master.csv'
$list = import-csv -Path '\users\mark\downloads\list.csv'
$list | %{
            $l = $_   #saved for next iteration
            
            foreach ($m in $master){
                $temp = $m.psobject.copy()
                $temp.SKU = $temp.SKU -replace "LMM", $l.SKU
                $temp.Parent = $temp.Parent -replace "LMM", $l.SKU
                $temp.Name = $temp.Name -replace "LMM_name", $l.NAME
                $temp.Description = $temp.Description -replace "LMM_name", $l.NAME
                $temp
                }
        } | Export-Csv -Path '\users\mark\downloads\Q_29071715.csv' -NoTypeInformation

Open in new window

Q_29071715.csv
0

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:
WOW so you did it for me already--thanks so much. I do like to learn as I ask so can you tell me how you did what each line is doing. I would appreciate that very much but understand if you don't have the time for it. Again thank you so much for sticking with me and helping.
0
aikimarkCommented:
You asked this question in the Powershell zone.  I'm not sure this is question thread is the place to teach an "Intro to Powershell" tutorial.

% is an alias for ForEach-Object
$_ is a variable used in the iteration of the input stream
0
Lionel MMSmall Business IT ConsultantAuthor Commented:
Thank you for a very good solution.
0
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
Powershell

From novice to tech pro — start learning today.