Forum Discussion

Tom_MolskowTMolskow's avatar
Tom_MolskowTMolskow
Copper Contributor
Jan 28, 2021

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

Resources