Trim trailing characters

I am attempting to trim trailing characters in each column of a csv import for a SharePoint script I’m writing.

$docSetInfos = Import-CSV C:\Users\SP2019-Farm\Desktop\customerstest.csv -Header Name,Description,CustCode,CustName -Delimiter ","  |  Foreach-Object { $_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }}

Is not working, it is removing everything. I tried Trimend and it also removes everything.

Any help would be apprciated!!!

Thans,
Chuck

Charles,

welcome to the forums.

I think there are charachters missing in your code. When you post code, error messages, sample data or console output format it as code, please.

Here you can read how that works: Guide to Posting Code.

You can go back and edit your existing post. You don’t have to create a new one. :wink:

Thanks in advance.

You didn’t show what you attempted with TrimEnd(), but I assure you it doesn’t “remove everything”. What it does do is remove any of the characters you supply from the end of the line until the first character that isn’t one of the characters you supplied.

# Just removes the last t
"here is a line of text".TrimEnd('t')

here is a line of tex

# Removes just the t because it stops at X
"here is a line of text".TrimEnd('te')

here is a line of tex

# Removes the t and x
"here is a line of text".TrimEnd('tx')

here is a line of te

# Removes the entire word text because each letter is supplied, stops at the space since we didn't specify a space
"here is a line of text".TrimEnd('txe')

here is a line of

# Removes the f on of, the entire word text, and the space between them
"here is a line of text".TrimEnd('f txe')

here is a line o

# Doesn't remove f because we didn't supply space
"here is a line of text".TrimEnd('ftxe')

here is a line of 

Hopefully these examples will help clarify. You can also use regex if this isn’t sufficient.

Here’s the contents of the csv:

 |Test Name1   |Test Name1   |99999999|Test Name1   |
|Test Name 2|Test Name 2|38357|Test Name 2|
|Test Name 3            |Test Name 3            |32434|Test Name 3            |
|Test Name 4     |Test Name 4     |36181|Test Name 4     |
|Test Name 5         |Test Name 5         |36111|Test Name 5         | 

If I comment out

 | Foreach-Object { $_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }}

My SharePoint list loads, when it is not comment out script finishes and there are no SharePoint records.

Also tried exporting to another CSV, when

| Foreach-Object { $_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }}

commented out I get all data in new csv, when not commented out gwt nothing in the new csv.

Please,
When you post code, error messages, sample data or console output format it as code, please.

Here you can read how that works: Guide to Posting Code.

You can go back and edit your existing post. You don’t have to create a new one. :wink:

Thanks in advance.

I tried editing, not sure I understand even with tutorial

Just use the </> icon in the edit bar.

Not sure if I relied correctly, did you see this reply?

Here’s the contents of the csv:

 |Test Name1   |Test Name1   |99999999|Test Name1   |
|Test Name 2|Test Name 2|38357|Test Name 2|
|Test Name 3            |Test Name 3            |32434|Test Name 3            |
|Test Name 4     |Test Name 4     |36181|Test Name 4     |
|Test Name 5         |Test Name 5         |36111|Test Name 5         | 

If I comment out

 | Foreach-Object { $_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }}

My SharePoint list loads, when it is not comment out script finishes and there are no SharePoint records.

Also tried exporting to another CSV, when

| Foreach-Object { $_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }}

commented out I get all data in new csv, when not commented out gwt nothing in the new csv.

You are making this so much harder on yourself than it has to be. Your delimiter is not a comma, it’s a pipe character | and to get rid of the junk pipe in the front, you don’t even have to trim it… just account for and exclude it.

$testfile = New-TemporaryFile

@'
 |Test Name1   |Test Name1   |99999999|Test Name1   |
|Test Name 2|Test Name 2|38357|Test Name 2|
|Test Name 3            |Test Name 3            |32434|Test Name 3            |
|Test Name 4     |Test Name 4     |36181|Test Name 4     |
|Test Name 5         |Test Name 5         |36111|Test Name 5         | 
'@ | Set-Content -Path $testfile -Encoding UTF8

$docSetInfos = Import-CSV $testfile -Header junk,Name,Description,CustCode,CustName -Delimiter "|" |
    Select-Object * -ExcludeProperty Junk

$docSetInfos |
    ForEach-Object {$_.psobject.properties |
        ForEach-Object {$_.value = $_.value.trim()}}

$docSetInfos | ConvertTo-Csv -NoTypeInformation

The resulting CSV data is…

"Name","Description","CustCode","CustName"
"Test Name1","Test Name1","99999999","Test Name1"
"Test Name 2","Test Name 2","38357","Test Name 2"
"Test Name 3","Test Name 3","32434","Test Name 3"
"Test Name 4","Test Name 4","36181","Test Name 4"
"Test Name 5","Test Name 5","36111","Test Name 5"

Thanks, didn’t notice that, it was an excel “save as”. It looks good in the file with commas, the post of the data was a copy from the csv cells and then pasted into the forum, not sure if that changed it.

I will interrogate the file…Thanks for your time.

Gere’s what it looks like in notepad:

Test Name1   ,Test Name1b   ,99999999,Test Name1a  
Test Name 2,Test Name 2b,38357,Test Name 2a
Test Name 3            ,Test Name 3b            ,32434,Test Name 3a            
Test Name 4     ,Test Name 4b     ,36181,Test Name 4a     
Test Name 5         ,Test Name 5b         ,36111,Test Name 5a  

You didn’t show how you put that out to notepad.

If you run this exact code

$testfile = New-TemporaryFile

@'
 |Test Name 1   |Test Name 6   |99999999|Test Name 11   |
|Test Name 2|Test Name 7|38357|Test Name 12|
|Test Name 3            |Test Name 8            |32434|Test Name 13            |
|Test Name 4     |Test Name 9     |36181|Test Name 14     |
|Test Name 5         |Test Name 10         |36111|Test Name 15         | 
'@ | Set-Content -Path $testfile -Encoding UTF8

$docSetInfos = Import-CSV $testfile -Header junk,Name,Description,CustCode,CustName -Delimiter "|" |
    Select-Object * -ExcludeProperty Junk

$docSetInfos |
    ForEach-Object {$_.psobject.properties |
        ForEach-Object {$_.value = $_.value.trim()}}

$docSetInfos | Export-Csv $testfile -NoTypeInformation

notepad $testfile

Notepad should end up showing

"Name","Description","CustCode","CustName"
"Test Name 1","Test Name 6","99999999","Test Name 11"
"Test Name 2","Test Name 7","38357","Test Name 12"
"Test Name 3","Test Name 8","32434","Test Name 13"
"Test Name 4","Test Name 9","36181","Test Name 14"
"Test Name 5","Test Name 10","36111","Test Name 15"

Just opened the csv with notepad.

OK let’s avoid any programs adding/changing characters .What does the output of

Get-Content -Path yourcsvfile

Show?

PS C:\Windows\system32> Get-Content -Path C:\Users\SP2019-Farm\Desktop\customerstest.csv
Test Name1   ,Test Name1b   ,99999999,Test Name1a  
Test Name 2,Test Name 2b,38357,Test Name 2a
Test Name 3            ,Test Name 3b            ,32434,Test Name 3a            
Test Name 4     ,Test Name 4b     ,36181,Test Name 4a     
Test Name 5         ,Test Name 5b         ,36111,Test Name 5a         

PS C:\Windows\system32>

OK then this updated example should help.

$testfile = New-TemporaryFile

@'
Test Name1   ,Test Name1b   ,99999999,Test Name1a  
Test Name 2,Test Name 2b,38357,Test Name 2a
Test Name 3            ,Test Name 3b            ,32434,Test Name 3a            
Test Name 4     ,Test Name 4b     ,36181,Test Name 4a     
Test Name 5         ,Test Name 5b         ,36111,Test Name 5a  
'@ | Set-Content -Path $testfile -Encoding UTF8

$docSetInfos = Import-CSV $testfile -Header Name,Description,CustCode,CustName

$docSetInfos |
    ForEach-Object {$_.psobject.properties |
        ForEach-Object {$_.value = $_.value.trim()}}

$docSetInfos | Export-Csv $testfile -NoTypeInformation

notepad $testfile

Notepad now shows

"Name","Description","CustCode","CustName"
"Test Name1","Test Name1b","99999999","Test Name1a"
"Test Name 2","Test Name 2b","38357","Test Name 2a"
"Test Name 3","Test Name 3b","32434","Test Name 3a"
"Test Name 4","Test Name 4b","36181","Test Name 4a"
"Test Name 5","Test Name 5b","36111","Test Name 5a"

Which would make your code

$path = 'C:\Users\SP2019-Farm\Desktop\customerstest.csv'

$docSetInfos = Import-CSV $path -Header Name,Description,CustCode,CustName

$docSetInfos |
    ForEach-Object {$_.psobject.properties |
        ForEach-Object {$_.value = $_.value.trim()}}

$docSetInfos | Export-Csv $path -NoTypeInformation

Here is the 2 results when exporting to csv, first has records. No records when using trim.

PS C:\Windows\system32> $docSetInfos = Import-CSV C:\Users\SP2019-Farm\Desktop\customerstest.csv -Header Name,Description,CustCode,CustName -Delimiter ","
$docSetInfos | ConvertTo-Csv -UseCulture -NoTypeInformation | Out-File  C:\Users\SP2019-Farm\Desktop\customerstesttest.csv
Get-Content -Path C:\Users\SP2019-Farm\Desktop\customerstesttest.csv
"Name","Description","CustCode","CustName"
"Test Name1���","Test Name1b���","99999999","Test Name1a��"
"Test Name 2","Test Name 2b","38357","Test Name 2a"
"Test Name 3������������","Test Name 3b������������","32434","Test Name 3a������������"
"Test Name 4�����","Test Name 4b�����","36181","Test Name 4a�����"
"Test Name 5���������","Test Name 5b���������","36111","Test Name 5a���������"

PS C:\Windows\system32> $docSetInfos = Import-CSV C:\Users\SP2019-Farm\Desktop\customerstest.csv -Header Name,Description,CustCode,CustName -Delimiter "," | Foreach-Object { $_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }}
$docSetInfos | ConvertTo-Csv -UseCulture -NoTypeInformation | Out-File  C:\Users\SP2019-Farm\Desktop\customerstesttest.csv
Get-Content -Path C:\Users\SP2019-Farm\Desktop\customerstesttest.csv

PS C:\Windows\system32> 

Yeah no clue why you’re doing all that. Please look at my example. You don’t want to be using get-content and out-file.

1 Like

No clue here either, this code in my sharepoint script, which is your example:

$ErrorActionPreference = "Stop"
$ver = $host | select version 
if($Ver.version.major -gt 1) {$Host.Runspace.ThreadOptions = "ReuseThread"} 
if(!(Get-PSSnapin Microsoft.SharePoint.PowerShell -ea 0)) 
{ 
Write-Progress -Activity "Loading Modules" -Status "Loading Microsoft.SharePoint.PowerShell" 
Add-PSSnapin Microsoft.SharePoint.PowerShell 
} 
 
$DestinationWebURL = "https://ciweb.colind.com/custmgmt" 
$DestinationLibraryTitle = "Customers" 
# $CSV = import-csv  'C:\Users\SP2019-Farm\Desktop\customerstest.csv' # | Foreach-Object { $_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }}
# $CSV | ConvertTo-Csv -NoTypeInformation | ForEach-Object{$_ -replace '\s',','} | ForEach-Object{$_ -replace '["()$-]', ''} | Out-File 'C:\Users\SP2019-Farm\Desktop\customerstesttrimmed.csv' -fo -en ascii
# $docSetInfos = Import-CSV C:\Users\SP2019-Farm\Desktop\customerstest.csv -Header Name,Description,CustCode,CustName -Delimiter ","  | Foreach-Object { $_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }}
$path = 'C:\Users\SP2019-Farm\Desktop\customerstest.csv'

$docSetInfos = Import-CSV $path -Header Name,Description,CustCode,CustName

$docSetInfos |
    ForEach-Object {$_.psobject.properties |
        ForEach-Object {$_.value = $_.value.trim()}}

$docSetInfos | Export-Csv $path -NoTypeInformation
$dWeb = Get-SPWeb $DestinationWebURL 
$dList = $dWeb.Lists | ? {$_.title -like $DestinationLibraryTitle} 

$cType = $dList.ContentTypes["Customer"] 

foreach($docSetInfo in $docSetInfos) 
{ 
	#Build properties hash table from $docSetInfos[]
	$docsetProperties = @{}
	

    $docsetProperties.Add('Title',$docSetInfo.Title)
    $docsetProperties.Add('Name',$docSetInfo.Name)
    $docsetProperties.Add('DocumentSetDescription',$docSetInfo.Description)
    $docsetProperties.Add('Customer_x0020_Code',$docSetInfo.CustCode)
    $docsetProperties.Add('Customer_x0020_Name',$docSetInfo.CustName)
								

	$NewFolder = [Microsoft.Office.DocumentManagement.DocumentSets.DocumentSet]::Create($dlist.RootFolder, $docSetInfo.CustCode, $cType.Id, $docsetProperties)
}

Produces This csv file:

PS C:\Windows\system32> Get-Content -Path C:\Users\SP2019-Farm\Desktop\customerstest.csv
"Name","Description","CustCode","CustName"
"Test Name1???","Test Name1???","99999999","Test Name1???"
"Test Name 2","Test Name 2","38357","Test Name 2"
"Test Name 3????????????","Test Name 3????????????","32434","Test Name 3????????????"
"Test Name 4?????","Test Name 4?????","36181","Test Name 4?????"
"Test Name 5?????????","Test Name 5?????????","36111","Test Name 5?????????"

PS C:\Windows\system32> 

It does it stand alone outside of my SharePoint script also.

Here is your code with clean csv file, it contains no headers to begin with. If I get it to wor I will tae the header entries out of your code.

PS C:\Windows\system32> Get-Content -Path C:\Users\SP2019-Farm\Desktop\customerstest.csv
Test Name1   ,Test Name1   ,99999999,Test Name1   
Test Name 2,Test Name 2,38357,Test Name 2
Test Name 3            ,Test Name 3            ,32434,Test Name 3            
Test Name 4     ,Test Name 4     ,36181,Test Name 4     
Test Name 5         ,Test Name 5         ,36111,Test Name 5         

PS C:\Windows\system32> $path = 'C:\Users\SP2019-Farm\Desktop\customerstest.csv'

$docSetInfos = Import-CSV $path -Header Name,Description,CustCode,CustName

$docSetInfos |
    ForEach-Object {$_.psobject.properties |
        ForEach-Object {$_.value = $_.value.trim()}}

$docSetInfos | Export-Csv $path -NoTypeInformation

PS C:\Windows\system32> Get-Content -Path C:\Users\SP2019-Farm\Desktop\customerstest.csv
"Name","Description","CustCode","CustName"
"Test Name1???","Test Name1???","99999999","Test Name1???"
"Test Name 2","Test Name 2","38357","Test Name 2"
"Test Name 3????????????","Test Name 3????????????","32434","Test Name 3????????????"
"Test Name 4?????","Test Name 4?????","36181","Test Name 4?????"
"Test Name 5?????????","Test Name 5?????????","36111","Test Name 5?????????"

PS C:\Windows\system32>