Hashtags are coming when export data to CSV file by powershell script

Question

I am running a powershell script for getting data from SharePoint document library. While run the script, data came to csv file, but columns names are coming with ";#" hashtags. For example, names, manager names etc are coming along with there ID number hashtags(123;#). I want to get only names, manager names, not hashtags or ID number on output CSV. Could anyone please paste updated script for the same.

$web = get-spweb "site Url"

$caseLib = $web.lists | where {$_.title -eq "Document Library"}

$query=new-object Microsoft.SharePoint.SPQuery 

$query.ViewFields = "<FieldRef Name='LinkFilename'/><FieldRef Name='Source_x0020_Name'/><FieldRef Name='Content_x0020_Lookup'/><FieldRef Name='Manager'/><FieldRef Name='Assigned_x0020_Person'/><FieldRef Name='City_x0020_Name'/>"

do

{

$caseLibItems=$caseLib.GetItems($query) 
$query.ListItemCollectionPosition=$caseLibItems.ListItemCollectionPosition

$listItemsTotal = $caseLibItems.Count

$x = 0

for($x=0;$x -lt $listItemsTotal; $x++)

   {

    $SourceName = $caseLibItems[$x]["Source_x0020_Name"]

    $ContentLookup = $caseLibItems[$x]["Content_x0020_Lookup"]

    $Manager = $caseLibItems[$x]["Manager"]

    $AssignedTo = $caseLibItems[$x]["Assigned_x0020_To"]

    $CityName = $caseLibItems[$x]["City_x0020_Name"]

    $str = ""
    if('$SourceName; $ContentLookup; $Manager; $AssignedTo; $CityName' -ne $null)

   {

 $str =  $caseLibItems[$x]["LinkFilename"].ToString() + $SourceName + $ContentLookup + $Manager + $AssignedTo + $CityName

}

else

   {

    $str = $caseLibItems[$x]["LinkFilename"].ToString()

   }

Write-Output $str| Out-File "path"  -Append

}

}while ($query.ListItemCollectionPosition -ne $null)

Write-Host "Exiting"

Output file I am getting

ABCD;"1234;#ABC travels";"48;#outdated";"157;#Rajukumar";"1246;#Raju";"1534;#England"

AFGV;"2678;#CDF travels";"26;#sourcedata";"24;#Johnson";"2323;#Kumar";"1298;#Japan"

xyza;"45324;#KHR travels";"324;#conducteddate";"136;#Peter";"231;#Helen";"1212;#USA"

MNGR;"11225;#XYZ travels";"368;#mangeddated";"157;#Helen";"1246;#Johnson";"3567;#AUS"

Etc

In the output file I am getting list ID name with hashtags. I don't want ID name and hashtags. Only names I need to get in the output file. Can anyone please help on this script?

Answer 1

You don't show what you want for your end use case. Please do so, or we have to guess. What are you considering names?

You can parse the file results using the built-in CSV cmdlets. Example:

$DataSet = '
ABCD;"1234;#ABC travels";"48;#outdated";"157;#Rajukumar";"1246;#Raju";"1534;#England"
AFGV;"2678;#CDF travels";"26;#sourcedata";"24;#Johnson";"2323;#Kumar";"1298;#Japan"
xyza;"45324;#KHR travels";"324;#conducteddate";"136;#Peter";"231;#Helen";"1212;#USA"
MNGR;"11225;#XYZ travels";"368;#mangeddated";"157;#Helen";"1246;#Johnson";"3567;#AUS"
' 
$DataSet -replace '#|"' | 
ConvertFrom-Csv -Delimiter ';' -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10 | 
Format-Table -AutoSize

# Results

H1   H2    H3          H4  H5            H6  H7        H8   H9      H10 
--   --    --          --  --            --  --        --   --      --- 
ABCD 1234  ABC travels 48  outdated      157 Rajukumar 1246 Raju    1534
AFGV 2678  CDF travels 26  sourcedata    24  Johnson   2323 Kumar   1298
xyza 45324 KHR travels 324 conducteddate 136 Peter     231  Helen   1212
MNGR 11225 XYZ travels 368 mangeddated   157 Helen     1246 Johnson 3567

I put in the headers to identify the specific columns. You can then cherry pick what you want to output.

I manually removed the line breaks, so you need to change your code so, that is not an output item or you'll have to handle that as well.