Forum Discussion
Tom_MolskowTMolskow
Jan 28, 2021Copper Contributor
Export Data from SharePoint List into CSV File - Extra Characters Appearing
Hello Community,
I am working with SharePoint 2013 and PowerShell to export data from a SharePoint list into a CSV and in the process, I have noticed some odd characters being added. Specifically, in front of many new sentences I see the "?" (question mark" symbol), and the "​" (European dollars symbol). A portion of my code conversion code is shown below. Has anyone else seen and solved this problem and if so can you guidance and code examples to resolve it?
Thanks!
Tom
Foreach($Field in $view.ViewFields | Where {!$_.hidden} )
{
# Process Field Names for String Manipulation
$RepField0 = $Field.Replace("Additional_x0020_Information_x00","Additional Information 2")
$RepField1 = $RepField0.Replace("_x0020_"," ")
$RepField2 = $RepField1.Replace(";#","")
$RepField3 = $RepField2.Replace("_x00","")
$RepField4 = $RepField3.Replace("Second User Submitte","Second User Submitted")
$RepField5 = $RepField4.Replace("Second Submitter Dat","Second Submitted Date")
$RepField6 = $RepField5.Replace("Transformer Serial N","Transformer Serial Number")
$RepField7 = $RepField6.Replace("PeopleSoft WO Activi","PeopleSoft WO Activity")
$RepField8 = $RepField7.Replace("Second Submitter Use","Second Submitter User Name")
$RepField9 = $RepField8.Replace("Second Submitter User Name0","Second Submitter User Email")
$RepField10 = $RepField9.Replace("MSR or AS Orde","MSR or AS Order Number")
$RepField11 = $RepField10.Replace("Author","Created By")
$RepField12 = $RepField11.Replace("ol_Department","Department")
$RepField13 = $RepField12.Replace("UniqueID0","UniqueID")
#Process Author Field Value
if ($Field -eq "Author"){
# Process Field Values for String Manipulation
$RepValueAuthor1 = $_["Author"]
$RepValueAuthor2 = $RepValueAuthor1 -replace '^\d+'
$RepValueAuthor3 = $RepValueAuthor2.Replace(";#","")
#*** Write Item to CSV File ***#
$ExportItem | Add-Member -MemberType NoteProperty -name $RepField13 -value $RepValueAuthor3 -Force
#Write-Host 'Name: ' $RepField12 'Author:' $RepValueAuthor3 #//For Testing Only
} elseif($Field -ne "Created By") {
#*** Write Item to CSV File ***#
$ExportItem | Add-Member -MemberType NoteProperty -name $RepField13 -value $_[$Field] -Force
#Write-Host 'Name: ' $RepField13 'Value:' $_[$Field] #//For Testing Only
}
#Process Additional Information
if ($RepField13 -eq "Additional Info" -or $RepField13 -eq "Additional Info 2" -or $RepField13 -eq "Additional Information" -or
$RepField13 -eq "Additional Information 2" -And $_[$Field].length -gt 70){
Write-Host "Field Name: " $RepField13
$addInfo = $_[$Field]
# Manage HTML Tags 1
if($addInfo -like '*Span*'){
$addInfoSplit1 = $addInfo.Split('>')[3]
#Write-Host "Span - addInfoSplit1: " $addInfoSplit1 #//For Testing Only
$addInfoSplit1Ndx = $addInfoSplit1.IndexOf("<br/>")
#Write-Host "addInfoSplit1Ndx: " $addInfoSplit1Ndx #//For Testing Only
$addInfoNdx1 = $addInfoSplit1.IndexOf("<")
#Write-Host "addInfoNdx1: " $addInfoNdx1 #//For Testing Only
$addInfoLen1 = $addInfoSplit1.Length
#Write-Host "addInfoLen1: " $addInfoLen1 #//For Testing Only
if($addInfoLen1 -gt 0){
$addInfoSubStr1 = $addInfoSplit1.Substring(0,$addInfoNdx1)}
Write-Host "addInfoSplit1: " $addInfoSplit1 #//For Testing Only
Write-Host "addInfoSubStr1: " $addInfoSubStr1 "`n" #//For Testing Only
$addInfoSubStr3 = $addInfoSubStr1.Trim()
# Manage HTML Tags 2
} else {
$addInfoSplit1 = $addInfo.Split('>')[2]
#Write-Host "No Span - addInfoSplit1: " $addInfoSplit1 #//For Testing Only
$addInfoSplit1Ndx = $addInfoSplit1.IndexOf("<br/> ")
#Write-Host "addInfoSplit1Ndx: " $addInfoSplit1Ndx #//For Testing Only
$addInfoNdx1 = $addInfoSplit1.IndexOf("<")
#Write-Host "addInfoNdx1: " $addInfoNdx1 #//For Testing Only
$addInfoLen1 = $addInfoSplit1.Length
#Write-Host "addInfoLen1: " $addInfoLen1 #//For Testing Only
if($addInfoLen1 -gt 0){
$addInfoSubStr1 = $addInfoSplit1.Substring(0,$addInfoNdx1)}
Write-Host "addInfoSplit1: " $addInfoSplit1 #//For Testing Only
Write-Host "addInfoSubStr1: " $addInfoSubStr1 "`n" #//For Testing Only
$addInfoSubStr3 = $addInfoSubStr1.Trim()
}
#*** Write Item to CSV File ***#
$ExportItem | Add-Member -MemberType NoteProperty -name $RepField13 -value $addInfoSubStr3.ToString() -Force
} elseif($RepField13 -eq "Additional Info" -or $RepField13 -eq "Additional Info 2" -or $RepField13 -eq "Additional Information" -or
$RepField13 -eq "Additional Information 2") {
#*** Write Item to CSV File ***#
$ExportItem | Add-Member -MemberType NoteProperty -name $RepField13 -value " " -Force
}
No RepliesBe the first to reply