Bonjour Olaf,
I came to my senses and dropped the XL module for this script. You are right, it can be done much easier.
I have adopted your model above at first, it was easy, but then run into few problems until I found something different and simpler I think.
Here is my code:
#Select the Mac address file and VM Inventory file
#
#Mac Address File
$MacFileBrowser = ''
$MacFileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{
InitialDirectory = [Environment]::GetFolderPath('Desktop')
Filter = 'Textfiles (*.txt)|*.txt'
}
$null = $MacFileBrowser.ShowDialog()
#VM inventory file
$VMSourceFileBrowser = ''
$VMSourceFileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{
InitialDirectory = [Environment]::GetFolderPath('Desktop')
Filter = 'SpreadSheet (*.xlsx)|*.xlsx'
}
$null = $VMSourceFileBrowser.ShowDialog()
## Convert each worksheet in the excel file into a csv file.
#find each sheet in the workbook and convert into
$sheets = (Get-ExcelSheetInfo -Path $VMSourceFileBrowser.FileName).Name
## read each sheet and create a CSV file with the same name
foreach ($sheet in $sheets) {
Import-Excel -WorksheetName $sheet -Path $VMSourceFileBrowser.FileName| Export-Csv "./$sheet.csv" -NoTypeInformation
}
#Search into a CSV file for Mac Adress
clear
$MACAddresses = Get-Content -Path ($MacFileBrowser).FileName
$User = $env:USERPROFILE
$CsvFiles = (dir *.csv).Name
ForEach ($MACAddresse in $MACAddresses) {
$n= (Get-Content $CsvFiles| select-string -pattern $MACAddresse).length
if ($n -gt 1)
{
Write-host " "
Write-host " "
Write-host "########################################################################################"
Write-Warning "[$n] x references are found for Mac Address [$MACAddresse] on the spreadsheet!!!!"
Write-Warning "The corresponding VM will be ignored. Please take a note of the MAC Address"
Write-host "########################################################################################"
Write-host " "
Write-host " "
}
else
{
Write-host " "
Write-host " "
Write-host "####################################################################################################"
Write-Host "[$n] x instance(s) of MAC Address [$MACAddresse] found the following GU Server or Cluster:"
Write-host "---------------------------------------------------------------------------------------------"
Write-host "[$($CsvFile)].The corresponding VM will be restarted soon. Please confirm the VM details beforehand"
Write-host "####################################################################################################"
Write-host " "
Write-host " "
#Restart-VM ($VMname).VMName -force
#restart-vm -vmname DC -force
#Search into a CSV file for Mac Adress
(Select-string $CsvFiles -pattern $MACAddresse |Select-Object *).line
}
}
and here is the output
####################################################################################################
[1] x instance(s) of MAC Address [AAAAAAAAAAAA] found the following GPU Server or Cluster:
---------------------------------------------------------------------------------------------
[SRV1-2022-09-06-04-40.csv].The corresponding VM will be restarted soon. Please confirm the VM details beforehand
####################################################################################################
"SRV-HostA","Microsoft Windows Server 2019 Standard","192.168.1.100","fe80::d37b:0::da1","TestVMA","AAAAAAAAAAAA","04/02/2021 19:56:00","112","d41a6622-1298-4988-9e42-ad5f0530413e","100","Boulbul"
########################################################################################
AVERTISSEMENT : [2] x references are found for Mac Address [BBBBBBBBBBBB] on the spreadsheet!!!!
AVERTISSEMENT : The corresponding VM will be ignored. Please take a note of the MAC Address
########################################################################################
########################################################################################
AVERTISSEMENT : [3] x references are found for Mac Address [CCCCCCCCCCCC] on the spreadsheet!!!!
AVERTISSEMENT : The corresponding VM will be ignored. Please take a note of the MAC Address
########################################################################################
####################################################################################################
[1] x instance(s) of MAC Address [DDDDDDDDDDDD] found the following GPU Server or Cluster:
---------------------------------------------------------------------------------------------
[SRV1-2022-09-06-04-40.csv].The corresponding VM will be restarted soon. Please confirm the VM details beforehand
####################################################################################################
"SRV-HostD","Microsoft Windows Server 2019 Datacenter","192.168.1.103","fe80::d37b:0::da4","TestVMD","DDDDDDDDDDDD","04/02/2021 19:43:37","116","14ed3aaf-dfeb-4ac8-8a31-efce22711f64","200","Boulbul"
From the command
select-string $CsvFiles -pattern $MACAddresse
I could not select an object from this command… I checked with get-member and I got the following:
TypeName : Microsoft.PowerShell.Commands.MatchInfo
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
RelativePath Method string RelativePath(string directory)
ToString Method string ToString(), string ToString(string directory)
Context Property Microsoft.PowerShell.Commands.MatchInfoContext Context {get;set;}
Filename Property string Filename {get;}
IgnoreCase Property bool IgnoreCase {get;set;}
Line Property string Line {get;set;}
LineNumber Property int LineNumber {get;set;}
Matches Property System.Text.RegularExpressions.Match[] Matches {get;set;}
Path Property string Path {get;set;}
Pattern Property string Pattern {get;set;}
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
RelativePath Method string RelativePath(string directory)
ToString Method string ToString(), string ToString(string directory)
Context Property Microsoft.PowerShell.Commands.MatchInfoContext Context {get;set;}
Filename Property string Filename {get;}
IgnoreCase Property bool IgnoreCase {get;set;}
Line Property string Line {get;set;}
LineNumber Property int LineNumber {get;set;}
Matches Property System.Text.RegularExpressions.Match[] Matches {get;set;}
Path Property string Path {get;set;}
Pattern Property string Pattern {get;set;}
I checked Select-Object
select-string $CsvFiles -pattern $MACAddresse | Select-Object *
I got this
IgnoreCase : True
LineNumber : 5
Line : "SRV-HostD","Microsoft Windows Server 2019 Datacenter","192.168.1.103","fe80::d37b:0::da4","TestVMD","DDDDDDDDDDDD","04/02/2021 19:43:37","116","14ed3aaf-dfeb-4ac8-8a31-efce22711f64","200","Boulbul"
Filename : Boubul-2022-09-06-04-42.csv
Path : C:\Users\nabil.adamou\Boubul-2022-09-06-04-42.csv
Pattern : DDDDDDDDDDDD
Context :
Matches : {0}
And
$Result.Line
Yields the following
"SRV-HostD","Microsoft Windows Server 2019 Datacenter","192.168.1.103","fe80::d37b:0::da4","TestVMD","DDDDDDDDDDDD","04/02/2021 19:43:37","116","14ed3aaf-dfeb-4ac8-8a31-efce22711f64","200","Boulbul"
The question is, how make custom objects from $Result , knowing that every string is separated by a comma?
Thanks in advance.
Regards,
Boulbul