Olaf,
Thank you so much for all your guidance. I really do appreciate all the time you have spent on this with me. I will look at the Where-Object you referenced and see if I can make that happen. I am going to test this on the real data and let you know how it goes!! Again, thank you so much for your time!!
BD
Hi,
I have one more dumb question. How do I bring the data in via a file on my network? Here you have the data being passed directly into the script it seems. Thanks again.
BD
Hi Billy,
If you are looking to bring data into a variable from a file on the network, all you need to do is use the Get-Content or Import-CSV cmdlets.
$NetworkFile = Get-Content -Path "path to the file on the network"
$NetworkFile = Import-CSV -Path "path to the file on the network"
Hi @tbingeman,
Are you suggesting that I take the code above that Olaf was nice enough to provide and replace with one of your options? Do I have to do this in all locations or can I tell the script to just look for your variable?
$CSVInputDatape
to
$NetworkFile = Get-Content -Path "path to the file on the network"
$NetworkFile = Import-CSV -Path "path to the file on the network"
To make my code suggestions reproducable and independend from input data I didnât have I provided the test data along with my code suggestions as plain text formatted CSV data.
Instead of the test data I provided you should import your production data into the variable $CSVInputData. Then you can use the rest of the code I suggested almost without a change.
Regardless of that - you should start to learn the very basics of PowerShell first. This way you understand the help you get here or in other forums. This way you can transfer the techniques shown as examples to your production data.
To learn what the code does you may run every single command or part of a pipeline on its own to see what the result is. Play with it. Change this and that and see what it looks when you cahnged it.
Thank you Olaf. That makes perfect sense. I just purchased a book as well so I can study PowerShell in more depth.
I did realize that my production data doesnât have a Header. It must have been something I was toying with when I was looking into a SQL solution. Any suggestions on how I can bypass that part but still parse it the way you did?
I also used this as my code to load the file as a variable. Does that look right to you?
$CSVInputData = Get-Content "C:\Column\PRD.csv" -Raw
BD
Not without having an idea of how your input data actually looks like. Of course you should sanitize it from sensitive information but they actually should stay as close to the original format as possible. When your original input file has proper headers of course you can use them.
No. If the input file is a valid CSV file you should use Import-Csv instead.
Hi,
I have sanitized the data. This is exactly what this vendor would provide us. As you can see they donât give us a header line.
20220111,5,N,,102345,4,,1,1,,1,M,M7045471953F.tif,"Letter",,"RR","UH",,,,,EEEEE325605,,"TEST","JOE",,,
20220111,5,N,,102345,4,,1,1,,2,M,M7045471954B.tif,"Letter",,,"UH",,,,,,,,,,,
20220111,5,N,,102345,4,,1,1,,3,M,M7045471955F.tif,"Letter",,,"UH",,,,,,,,,,,
20220111,5,N,,102345,4,,1,1,,4,M,M7045471956B.tif,"Letter",,,"UH",,,,,,,,,,,
20220111,5,N,,102345,4,,1,1,,5,M,M7045471957F.tif,"Letter",,,"UH",,,,,,,,,,,
20220111,5,N,,102345,4,,1,1,,6,M,M7045471958B.tif,"Letter",,,"UH",,,,,,,,,,,
20220111,5,N,,102345,4,,1,1,,7,M,M7045471959F.tif,"Letter",,,"UH",,,,,,,,,,,
20220111,5,N,,102345,4,,1,1,,8,M,M7045471960B.tif,"Letter",,,"UH",,,,,,,,,,,
20220111,5,N,,102345,4,,1,1,1,1,M,M7045471961F.tif,"Letter",,,"UH",,,,,,,,,,,
20220111,5,N,,102345,4,,1,1,,9,M,M7045471962B.tif,"Letter",,,"UH",,,,,,,,,,,
20220111,5,N,,102345,4,,1,2,1,1,M,M7045471963F.tif,"Letter",,"BD","MHS",,,,,XXXXX0000000,,"TEST","JANE",,,
20220111,5,N,,102345,4,,1,2,,1,M,M7045471964B.tif,"Letter",,,"MHS",,,,,,,,,,,
20220111,5,N,,102345,4,,1,3,1,1,M,M7045471965F.tif,"Letter",,"AR","UH",,,,,E1111111111,,"TEST","LARRY",,,
20220111,5,N,,102345,4,,1,3,,1,M,M7045471966B.tif,"Letter",,,"UH",,,,,,,,,,,
20220111,5,N,,102345,4,,1,4,1,1,M,M7045471967F.tif,"Letter",,"Refund Request","3C",,,,,999999999,,,,,,
20220111,5,N,,102345,4,,1,4,,1,M,M7045471968B.tif,"Letter",,,"3C",,,,,,,,,,,
20220111,5,N,,102345,4,,1,4,,2,M,M7045471969F.tif,"Letter",,,"3C",,,,,,,,,,,
20220111,5,N,,102345,4,,1,4,,3,M,M7045471970B.tif,"Letter",,,"3C",,,,,,,,,,,
20220111,5,N,,102345,4,,1,4,,4,M,M7045471971F.tif,"Letter",,,"3C",,,,,,,,,,,
20220111,5,N,,102345,4,,1,4,,5,M,M7045471972B.tif,"Letter",,,"3C",,,,,,,,,,,
20220111,5,N,,102345,4,,2,1,,1,M,M7045471973F.tif,"Letter",,"RR","PGBA",,,,,E2222222222,,"TEST","PAUL",9884,,
20220111,5,N,,102345,4,,2,1,,2,M,M7045471974B.tif,"Letter",,,"PGBA",,,,,,,,,,,
20220111,5,N,,102345,4,,2,1,1,1,M,M7045471975F.tif,"Letter",,,"PGBA",,,,,,,,,,,
20220111,5,N,,102345,4,,2,1,,3,M,M7045471976B.tif,"Letter",,,"PGBA",,,,,,,,,,,
20220111,5,N,,102345,4,,2,1,,4,M,M7045471977F.tif,"Letter",,,"PGBA",,,,,,,,,,,
20220111,5,N,,102345,4,,2,1,,5,M,M7045471978B.tif,"Letter",,,"PGBA",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,1,1,M,M7045471983F.tif,"Letter",,"AIR","HAIOC",,,,,123456789,,"TEST","HARRY",15680.6,,
20220111,5,N,,102345,4,,3,1,,1,M,M7045471979F.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,2,M,M7045471980B.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,3,M,M7045471981F.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,4,M,M7045471982B.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,5,M,M7045471984B.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,6,M,M7045471985F.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,7,M,M7045471986B.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,8,M,M7045471987F.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,9,M,M7045471988B.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,10,M,M7045471989F.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,11,M,M7045471990B.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,12,M,M7045471991F.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,13,M,M7045471992B.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,14,M,M7045471993F.tif,"Letter",,,"HAIOC",,,,,,,,,,,
20220111,5,N,,102345,4,,3,1,,15,M,M7045471994B.tif,"Letter",,,"HAIOC",,,,,,,,,,,
Thank you again for helping me.
OK, so you have to ask your vender to add standard compliant headers or add them by yourself during the import of the data just like I showed it with the parameter -Header for the cmdlet Import-Csv. Of course you may add more verbose names than just numbered columns.
Hi,
If I told you that vendor was a multi-billion dollar company would you believe me? They have been unwilling to do anything to help us. This is why I am in the position I am in.
Are you saying to leave your code the way it is and just try to import the file that they are sending me? Will this do it? I havenât been able to get it load with out an error.
$CSVInputData = Import-Csv -Path "C:\Column\PRD.csv"
I canât seem to get the file to load. Using this as the way to bring it in. I get this error.
The property 'Clmn22' cannot be found on this object. Verify that the property exists and can be set.
At line:23 char:13
+ $_.Clmn22 = $CurrentValue
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : PropertyAssignmentException
Of course I would. The bigger a company is the more complex and unflexible are their processes and the less willing are they to change them for a smaller customer.
But hey ⌠no problem at all - we have PowerShell to help us. 
In my last answer I already mentioned to use the parameter -Header for the import with Import-Csv to add your own headers. Otherwise PowerShell will use the first line as the header line and complain when there are doubletts and will fill in empty cells with H1, H2 ⌠and so on. Actually you should get another error before the one you just posted.
It is beyond the scope of a forum to teach you basic computer science or a complex technology like scripting with PowerShell. You should learn the very basics for yourself first. That is needed to understand the help you get in a forum like this. So please ⌠read the help for the cmdlets youâre about to use completely including the examples to learn how to use them. Start with
Hi Olaf,
I created another .csv that has the names of each of the columns the vendor will be sending. Using the guide you provided and your code I was able to convert the names to Clmn1 through Clmn28. Now I donât know where to go to get the Header I created to populate the first row of my production data. I did paste that row that I created off that other file into the data into my production file. When I do that and run your code. It works seamlessly. What is the best way to pipe that data into my production data file?
BD