.Trim() function not working when being used in a foreach loop

November 13, 2023

I’m trying to create a script that will take a list of staff members name from an Excel sheet, use that name as a variable to find a jpg filename that matches and then upload it to be their Office365 profile picture… however I am having difficulties when importing Excel data.

It outputs as:
"Name=Firstname Lastname} @{Name=Firstname Lastname} @{Name=Firstname Lastname}"

I have code to remove the mumbo jumbo, but once I do that it comes with whitespaces that for the life of me I cannot remove. I’m trying to use foreach loop that will .Trim() the whitespaces off the front and end of the string but it does not work.

There is no code for the profile picture uploading in the script as I cannot even format the names properly for now.

Install-Module -Name ImportExcel
$names = Import-Excel "C:\Users\...." -HeaderName "Name"


foreach($Name in $names){
    $firstSplit = $name -split "@{Name="
    $splitString = $firstSplit -split "}"

    $splitString = $splitString.Trim(" ")
    
    Write-Host $splitString
}

It will always return the string with whitespaces at the front and end of the string.

>Solution :

The output from Import-Excel is going to consist of 0 or more objects, each representing a row from the spreadsheet – with the named properties on the object mapped to the columns in the sheet. So @{Name=FirstName LastName} is not actually a string value stored in the $name variable – it’s a string representation of the complex object stored in $name.

To get the raw string value stored in a particular property, use the . member-access operator:

Install-Module -Name ImportExcel
$spreadSheetData = Import-Excel "C:\Users\...." -HeaderName "Name"

foreach($row in $spreadSheetData) {
  # Access the `Name` property on each row object to get the raw cell value
  Write-Host "The name is: '$($row.Name)'"
}