I wrote a script to capture all content from specific location of the content tree, as part of this script I need to take out only the text from the rich text fields. I use the HtmlAgilityPack to filter out the text out from HTML. I had about 3000 records before decoding now I only get around 1200 records. I can see all the records in result view but when export it excel format I only see limited content. other formats(csv.json) contain all the results

Function SanitiseHtml($html){
   $doc = New-Object HtmlAgilityPack.HtmlDocument
   $sanitise = $doc.DocumentNode.InnerText -replace 'amp;',' '
   $sanitise = $sanitise -replace ' ', ' '
   $sanitise = $sanitise -replace '’',' '
   $decoded = ([System.Web.HttpUtility]::HtmlDecode($sanitise))
   return $decoded
  • This seems to be a problem with how Excel shows the values. Can you see all the records if you open the CSV file with text editor? Commented Oct 28, 2020 at 8:20
  • Yes, I can see all in the CSV
    – Rilan
    Commented Oct 28, 2020 at 9:02
  • If you export to XLSX, then open the document in Excel do you see all the content in the box above the grid when you select the cell? Another test would be to open the xlsx in Excel and save as CSV to verify again that all the data is there. Commented Oct 28, 2020 at 13:16
  • I can see all the data as expected on the List View window. But lower number of rows (less than half) when I export it to Excel. The file size is much smaller than the size of the csv file. However, I do get all the records on XSL if I get rid of the decoding bit which is strange. Also, I couldn’t replicate this issue for smaller number of records. I really need the decoding bit ☹
    – Rilan
    Commented Oct 29, 2020 at 6:44
  • I had a similar kind of issue while dealing with accents but it was for Single line text. I run the script and I'll get all the data in the required format in List view but not when I tried downloading using CSV format. So I always download it in Excel format, open in Excel and save it as CSV UTF-8 format. Kindly try the same. Commented Oct 29, 2020 at 12:46


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.