Hello Forum,
I’ve managed to cobble together two PowerShell scripts with the following details:
Input file in Script #1 is an excel spreadsheet (.xlsx) that has this type of format:
Where column A in the spreadsheet is the Sever Hostname and Column B in the spreadsheet is the IP address. There are no headings in this input file.Server1 192.168.40.10Server2 192.168.40.11
Server3 192.168.4012
The output from Script #1 is to create .csv files of the input file broken down into 12 servers per .csv file. There will be as many .csv files as necessary based on the number of rows in the .xlsx file.
The input of Script #2 are the .csv files. Currently I need to run Script #2 for each .csv file created from Script #1.
I’m looking to bring together both scripts so that I read in the .xlsx file, and then create the output file of Script #2 where as many .mxtsessions files will be created based on 12 servers per .mxtsessions file. I hope I’m explaining this properly. Here are both scripts. Any advice or pointers you can provide on how to bring together both scripts into one would be greatly appreciated.
PowerShell script #1:
$file = "C:\mydirectory\InputFile.xlsx"
$batchDirectory = "C:\mydirectory"
$batchFileName="Results"
$sheetName = "Sheet1"
$batchNum = 1
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($file)
$sheet = $workbook.Worksheets.Item($sheetName)
$objExcel.Visible=$false
$rowMax = ($sheet.UsedRange.Rows).count
$rowHostname,$ColHostname = 0,1
$rowIP,$ColIP=0,2
for ($i=1; $i -le $rowMax; $i++)
{
$percentageComplete = ($i / $rowMax) * 100
$status= [Math]::Round($percentageComplete,2)
Write-Progress -Activity 'Creating Batches' -status "$status % complete" -PercentComplete $percentageComplete
$name = $sheet.Cells.Item($rowHostname+$i,$colHostName).text
$ip = $sheet.Cells.Item($rowIP+$i,$colIP).text
$csvRow= [pscustomobject]@{'hostname' = $name.Trim() ; 'ip' = $ip.Trim()}
$csvRow | Export-Csv -NoTypeInformation -Append -Path $batchDirectory\$batchFileName$batchNum.csv
if ($i % 12 -eq 0)
{
++$batchNum
}
}
PowerShell Script #2:
Function Get-FileName($initialDirectory)
{
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.filter = "CSV (*.csv)| *.csv"
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
}
$csvFilename = Get-FileName "C:\temp"
$inputdata = get-content $csvFilename
$csv = Import-Csv -Path $csvFilename -Delimiter ','
$SaveChooser = New-Object -TypeName System.Windows.Forms.SaveFileDialog
$SaveChooser.filter = "MXTSESSIONS (*.mxtsessions)| *.mxtsessions"
$SaveChooser.ShowDialog()
@'
[Bookmarks]
SubRep=
ImgNum=42
'@ | Out-File $SaveChooser.Filename -Encoding ASCII
$output = foreach ($line in $csv) {
"$($line.hostname)= #109#0%$($line.ip)%22%[username]%%-1%-1%%%22%%0%0%0%%%-1%0%0%0%%1080%%0%0%1#MobaFont
%10%0%0%0%15%236,236,236%0,0,0%180,180,192%0%-1%0%%xterm%-
1%0%0,0,0%54,54,54%255,96,96%255,128,128%96,255,96%128,255,128%255,255,54%255,255,128%96,96,255%128,128,255%255,54,255%255,128,255%5
4,255,255%128,255,255%236,236,236%255,255,255%80%24%0%1%-1%<none>%%0#0#"
}
$output | Out-File $SaveChooser.Filename -Append -Encoding ASCII
I know I need to start with PowerShell script #1 and add somewhere in the for loop statement create the 12 serves per list AND add in the details from Script #2 to create the required .mxtsssions output file with the details I have in the output from Script #2…but I’m at a loss as to how to start this.
Any advice or pointers on what bits of Script #2 need to be added to the For loops of Script #1 to make this work for me would be greatly appreciated.
[ForEach-Object], RuntimeException