CODEWITHSUNDEEP
excelpowershellnew-item
pwsh2succeed
pwsh2succeed

Reputation: 1

Powershell Script: Search folder, Get Filename, Search Excel, Export String to new File, and more

I have been a lurker here on Stack Overflow for years and have found many helpful posts in said time. I am looking for help on creating a Powershell script. I am looking for something simple (as possible) and doesn't need to have a bunch of advanced logic. Just need it to work. I will do my best to explain.

Objects:

The Goal:

My goal is to have a Powershell script that will take the name of a file inside of the folder, search excel for the file name, when it finds the name which will be in Column (B), take the column (B) string, append the string from column (A) to it separated by a hyphen, and create a .txt file in the same folder with this new string of text as the name.

The folder contains many files, thus the script would need to be wrapped in a foreach loop, so that it creates a .txt for each file in the folder after finding it in the excel document. Also, since the string of text in Column (B) doesn't contain the file extension of the file, I'm guessing it would be a 'contains' search, rather than 'equals' search. For example, if a file name in the folder is ExistingFile.pdf , the output of the script would be ExistingFile-TextString1.txt, instead of ExistingFile.pdf-TextString1.txt. If that makes sense.

A screenshot is provided for a visual.

https://i.sstatic.net/lzqBM.jpg

I noticed some OPs don't like lengthy responses explaining the logic behind a reply script. I personally enjoy learning logic and seeing why someone handled the script the way they did in detail. Feel free to explain in detail or keep it simple.

Thanks in advance!

I have not tried to create this script because I realize some of the logic needed is a bit out of my realm. But I do plan to learn from any responses I receive.

Upvotes: 0

Views: 342

Answers (1)

Martin Iszac
Martin Iszac

Reputation: 534

Use the Import-Excel module: https://www.powershellgallery.com/packages/ImportExcel/5.4.2

$folderPath = "...\folder" 
$data = Import-Excel -Path "$folderPath\file.xlsx"
$files = Get-ChildItem $folderPath -File
foreach ($file in $files) {
   if ($file.Extension -ne ".xlsx") {
       $match = $data | Where-Object { $_.B -eq $file.BaseName }
       if ($match) {
           $newFileName = $file.BaseName + "-" + $match.A + ".txt"
           New-Item -Path "$folderPath\$newFileName" -ItemType File
       }
   }
}

This will iterate through each file in the folder and, if it finds a match, will create a new text file with a name based on the file BaseName and the data from the excel file.

Try this and let me know if it helps.

Upvotes: 0

Related Questions