Trim trailing characters

ccroasmun67 · April 2, 2021, 5:22pm

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

Olaf · April 2, 2021, 5:43pm

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.

Thanks in advance.

krzydoug · April 2, 2021, 5:47pm

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.

ccroasmun67 · April 2, 2021, 6:04pm

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.

Olaf · April 2, 2021, 6:13pm

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.

Thanks in advance.

ccroasmun67 · April 2, 2021, 6:39pm

I tried editing, not sure I understand even with tutorial

Olaf · April 2, 2021, 6:44pm

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

ccroasmun67 · April 2, 2021, 6:59pm

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.

krzydoug · April 2, 2021, 7:13pm

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"

ccroasmun67 · April 2, 2021, 7:23pm

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.

ccroasmun67 · April 2, 2021, 7:24pm

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

krzydoug · April 2, 2021, 7:29pm

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"

ccroasmun67 · April 2, 2021, 7:31pm

Just opened the csv with notepad.

krzydoug · April 2, 2021, 7:33pm

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

Get-Content -Path yourcsvfile

Show?

ccroasmun67 · April 2, 2021, 7:36pm

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>

krzydoug · April 2, 2021, 7:52pm

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

ccroasmun67 · April 2, 2021, 7:53pm

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>

krzydoug · April 2, 2021, 8:00pm

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.

ccroasmun67 · April 2, 2021, 8:09pm

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.

ccroasmun67 · April 2, 2021, 8:23pm

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>

Topic		Replies	Views
String Manipulation to CSV field PowerShell Help	11	1686	May 16, 2024
String Method TrimEnd not trimming as expected PowerShell Help	4	781	May 16, 2024
Triming PowerShell Help	11	337	May 16, 2024
Importing CSV and Removing Data/Cells PowerShell Help	17	388	May 16, 2024
Correct use of TrimEnd PowerShell Help	3	214	May 16, 2024

Trim trailing characters

Related topics