How to determine the last item from the foreach-object cmdlet from the pipeline
How can I determine if a process is in the last item from the ForEach-Object pipeline cmdlet?
Example: Let's say that I previously defined a connection for OLEDB to access the database to get the column definitions for each local table. I want the list of each of these columns to be comma separated - except for handling the LAST column. I want to omit the comma.
ForEach($table in $Tables)
{
$SchemaColumn = $Connection.GetSchema("Columns")
$listarray = $schemaColumn |
foreach-Object {
If($_.Table_Name -eq $Table) {
IF "Last Item in Pipeline" <---------------------------what would go here?
{
"$($_.COLUMN_Name) $(Get-DataType($_.DATA_TYPE))" <---- no comma!
}
ELSE ## not the end of pipeline listing
{
"$($_.COLUMN_Name) $(Get-DataType($_.DATA_TYPE)), " <----yes comma!
}
}
} #end Foreach-Object
source to share
Can I suggest a different approach that doesn't depend on whether it is the last element or not?
ForEach($table in $Tables) { $SchemaColumn = $Connection.GetSchema("Columns") $listarray = $schemaColumn | foreach-Object { If($_.Table_Name -eq $Table) { "$($_.COLUMN_Name) $(Get-DataType($_.DATA_TYPE))" # <---- no comma! } } -join ',' #end Foreach-Object
Pay attention to the last line. The call result ForEach-Object
(all results "without comma") will be concatenated with symbols ,
.
If this is not what you are looking for, can you explain how you want to use the end result?
source to share
If you don't already have a generated array, you would like to do a complete construct instead of a pipeline for foreach.
$testarray = 1..10
Foreach ($Item in $TestArray)
{
if ($Item -eq $testarray[-1])
{ "last item in array is $Item"}
}
To clarify, each time through the array, it checks if the $ element matches the last element in $ testarray. [-1] selects the last element in the array
But, in your case, since you already have an array stored in $ SchemaColumn, you can do the same as I said above with a pipeline ... it would look like this:
if ($_ -eq $SchemaColumn[-1]) { 'stuff' }
source to share
Alternatively, you can skip the check to see if the last column is, and then just trim the trailing commas.
ForEach($table in $Tables)
{
$SchemaColumn = $Connection.GetSchema("Columns")
$listarray = $schemaColumn | Where{$_.Table_Name -eq $Table} |
foreach-Object {"$($_.COLUMN_Name) $(Get-DataType($_.DATA_TYPE)), "}
$ListArray | ForEach{$_ = $_.TrimEnd(, )}
} #end Foreach-Object
source to share
A simple solution. If you know the total number of items, you can use a counter to compare with:
$Total = $Items.count
$Counter = 0
foreach ($Item in $Items) {
# This will run on the final iteration
$Counter += 1
if($Counter -ne $Total) {
#This won't execute on the final iteration
}
}
source to share
Returns each column information as a string. This will create an array from which you can use a PowerShell statement -join
to join them with a semicolon.
foreach( $table in $Tables )
{
$columns = $Connection.GetSchema('Columns') |
Where-Object { $_.TABLE_NAME -eq $table } |
ForEach-Object { '{0} {1}' -f $_.COLUMN_Name,(Get-DataType $_.DATA_TYPE) }
$columns -join ', '
}
It looks like you are writing some kind of report. Instead of returning strings, have you considered returning objects?
$Connection.GetSchema('Columns') |
Select-Object -Property 'COLUMN_NAME',@{ Name = 'DATATYPE'; Expression = { Get-DataType $_.DATA_TYPE }},'TABLE_NAME'
Then someone could do this:
Get-Column |
Sort-Object 'TABLE_NAME' |
Format-Table -AutoSize -GroupBy 'TABLE_NAME' |
Set-Content 'schema.txt'
Or that:
Get-Column | Export-Csv
Or that:
Get-Column |
Sort-Object -Property 'TABLE_NAME' |
Format-Table
source to share