Help with adding column for input to existing PowerShell script

PowerShell Help
1

Hello Forum,

I have a PowerShell script that’s working for our usecase but I’d like to see how I can update it to make it even more useful.

Currently we input into the Powershell an Excel spreadsheet with hostname and IP address:

host1 192.168.10.1
host2 192.168.10.2
host3 192.168.10.3
The output of the input file is an excel spreadsheet where the hostname and IP address are added to a string. This is working well for us. But as a pre-task to running our script we create separate excel files by a tag. What I would like to add to our PowerShell script is to be able to read in and take account of each hostname/ip tag and create separate excel files for each tag. So I would like our input file to look instead like below:
host1 192.168.10.1 AWSL10A
host2 192.168.10.2 AWSL10A
host3 192.168.10.3 AWSL10A
host4 192.168.10.4 AWSL20A
host5 192.168.10.5 AWSL10A
I would like (in this above example) to have two output files created with the hostname/ip of each row being in their respective output file based on tag. The output filename should also have the tag added to the filename so we can know which output file is which (based on tag).

Here is the current code we use below. Would someone be able to point me or guide me in how I can add to this script the ability to take into account each tag in column 3 to create separate spreadsheet output files based on the criteria I outlined above.

Any advice or direction you can provide would be greatly appreciated.

 

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 = “XLSX (*.xlsx)| *.xlsx”
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
}

$file = Get-FileName “C:\temp”
$inputdata = get-content $file

# Get the filename and the folder where it lives:
$folder = [System.IO.Path]::GetDirectoryName($file)
Write-Host “‘$file’ lives in folder ‘$folder’”

#Capture filename from $file without extension. To be used to create the resulting XML file.
$filepath = Get-ChildItem $file
Write-Host $filepath.Basename

$batchFileName = $filepath.Basename
Write-Host $batchFileName

# Open Excel and load in the input file. Define columns from input file:
$batchDirectory = $folder
$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

# Create the header in the output file:
@‘
<?xml version=“1.0” encoding=“utf-8”?>
<ArrayOfSessionData xmlns:xsd=“XML Schema” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”>
’@ | Out-File -FilePath $batchDirectory$batchFileName"_"$batchNum.XML -Encoding ASCII

# Loop through excel input file and write out the body of the file inserting in variables from input file:
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
$output = “<SessionData SessionId=”“$($name.Trim())”" SessionName=“”$($name.Trim())“” ImageKey=““computer”” Host=“”$($ip.Trim())“” Port=““22"” Proto=”“SSH”" PuttySession=““Default Settings”” Username=““user”” ExtraArgs=“”“” SPSLFileName=“”“” RemotePath=“”“” LocalPath=“”“” />“”“
$output | Out-File -Append -FilePath $batchDirectory$batchFileName”_"$batchNum.XML -Encoding ASCII
if ($i % 800 -eq 0)
{

@‘
</ArrayOfSessionData>
’@ | Out-File -Append -FilePath $batchDirectory$batchFileName"_"$batchNum.XML -Encoding ASCII

++$batchNum
@‘
<?xml version=“1.0” encoding=“utf-8”?>
<ArrayOfSessionData xmlns:xsd=“XML Schema” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”>
’@ | Out-File -Append -FilePath $batchDirectory$batchFileName"_"$batchNum.XML -Encoding ASCII

}

}

@‘
</ArrayOfSessionData>
’@ | Out-File -Append -FilePath $batchDirectory$batchFileName"_"$batchNum.XML -Encoding ASCII

# Close Excel:
$workbook.Close($false)
$objExcel.Quit()

[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)

 

Thank you.

 

 

 

 

2

Please re-read the postRead Me Before Posting! You’ll be Glad You Did! and wrap your code in the PRE tags.

Take a look at Group-Object to group items by Tag. Here is an example:

$obj = @()
$obj += [pscustomobject]@{HostName='host1';IPAddress='192.168.10.1';Tag='AWSL10A'}
$obj += [pscustomobject]@{HostName='host2';IPAddress='192.168.10.2';Tag='AWSL10A'}
$obj += [pscustomobject]@{HostName='host3';IPAddress='192.168.10.3';Tag='AWSL10A'}
$obj += [pscustomobject]@{HostName='host4';IPAddress='192.168.10.4';Tag='AWSL20A'}
$obj += [pscustomobject]@{HostName='host5';IPAddress='192.168.10.5';Tag='AWSL10A'}

foreach ($grp in ($obj | Group-Object -Property Tag)) {
    $fileName = 'foo_{0}.xml' -f $grp.Name
    'Processing {0} rows for {1}' -f $grp.Count, $fileName
    foreach ($item in $grp.Group) {
        'Processing host {0} with ip {1}' -f $item.HostName, $item.IPAddress
    }
}

Output:

Processing 4 rows for foo_AWSL10A.xml
Processing host host1 with ip 192.168.10.1
Processing host host2 with ip 192.168.10.2
Processing host host3 with ip 192.168.10.3
Processing host host5 with ip 192.168.10.5
Processing 1 rows for foo_AWSL20A.xml
Processing host host4 with ip 192.168.10.4