Help with adding column for input to existing PowerShell script

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:

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 AWSL10A
host2 AWSL10A
host3 AWSL10A
host4 AWSL20A
host5 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(“”) | Out-Null

$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.filter = “XLSX (*.xlsx)| *.xlsx”
$OpenFileDialog.ShowDialog() | Out-Null

$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)
$rowMax = ($sheet.UsedRange.Rows).count
$rowHostname,$ColHostname = 0,1

# Create the header in the output file:
<?xml version=“1.0” encoding=“utf-8”?>
<ArrayOfSessionData xmlns:xsd=“XML Schema” xmlns:xsi=“”>
’@ | 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)

’@ | Out-File -Append -FilePath $batchDirectory$batchFileName"_"$batchNum.XML -Encoding ASCII

<?xml version=“1.0” encoding=“utf-8”?>
<ArrayOfSessionData xmlns:xsd=“XML Schema” xmlns:xsi=“”>
’@ | Out-File -Append -FilePath $batchDirectory$batchFileName"_"$batchNum.XML -Encoding ASCII



’@ | Out-File -Append -FilePath $batchDirectory$batchFileName"_"$batchNum.XML -Encoding ASCII

# Close Excel:




Thank you.





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='';Tag='AWSL10A'}
$obj += [pscustomobject]@{HostName='host2';IPAddress='';Tag='AWSL10A'}
$obj += [pscustomobject]@{HostName='host3';IPAddress='';Tag='AWSL10A'}
$obj += [pscustomobject]@{HostName='host4';IPAddress='';Tag='AWSL20A'}
$obj += [pscustomobject]@{HostName='host5';IPAddress='';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


Processing 4 rows for foo_AWSL10A.xml
Processing host host1 with ip
Processing host host2 with ip
Processing host host3 with ip
Processing host host5 with ip
Processing 1 rows for foo_AWSL20A.xml
Processing host host4 with ip