extract data from SharePoint document library to CSV using powershell

Question

i am trying extract data of SharePoint document library to CSV file using powershell. I am getting data correct on CSv file. But one column i.e "Description" have more text data on it.So when run the script, data coming coming into another rows(its not coming in one row). For reference had written script below and my out-file in below.

Powershell Script

$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='DocumentSetDescription'/>"
$query.RowLimit=500000
do
{
    $caseLibItems=$caseLib.GetItems($query)
    $query.ListItemCollectionPosition=$caseLibItems.ListItemCollectionPosition
    $listItemsTotal = $caseLibItems.Count
    $x = 0
    for($x=0;$x -lt $listItemsTotal; $x++)
    {
        $Description = $caseLibItems[$x]["DocumentSetDescription"]
        $str = ""
        if('$Description' -ne $null)
        {
            $str = $caseLibItems[$x]["LinkFilename"].ToString() + '}' + $Description
        }
        else
        {
            $str = $caseLibItems[$x]["LinkFilename"].ToString()
        }
        Write-Output $str | Out-File "Path" 
        import-csv Data.csv -delimiter "}" -Header "Number", "Description"  | export-csv -NoTypeInformation  -Path "C:\csvfile1.csv"     
    }
} while ($query.ListItemCollectionPosition -ne $null)

Write-Host "Exiting"

Output file for reference

Name            Description 

ABCD-123        This file imported data of system.

XYZA-231        Data migrated to next session

file need to upload on another server.

System update required.

CDFC-231        New file need to create on system

XYZA-984        system creating problem.

Source code error. update new file

HGFC-453        Maintenance updated file.

Output file i want required as below

Name            Description 

ABCD-123        This file imported data of system.

XYZA-231        Data migrated to next session.file need to upload on another server. System update required.

CDFC-231        New file need to create on system

XYZA-984        system creating problem. Source code error. update new file.

HGFC-453        Maintenance updated file.

Hope you guys understand my requirement. I want description column data need to me in one row only.

Can anyone please help me or correct me on this script. Many many thanks in Advance.

Answer 1

Replace line breaks with spaces before using $Description.

    $web = get-spweb $siteUrl
$caseLib = $web.lists | where {$_.title -eq $listTitle}
$query=new-object Microsoft.SharePoint.SPQuery 
$query.ViewFields = "<FieldRef Name='LinkFilename'/><FieldRef Name='DocumentSetDescription'/>"
$query.RowLimit=500000

Write-Output "Header}Description" | Out-File "temp.csv" 

do
{
    $caseLibItems=$caseLib.GetItems($query)
    $query.ListItemCollectionPosition=$caseLibItems.ListItemCollectionPosition
    $listItemsTotal = $caseLibItems.Count
    $x = 0
    for($x=0;$x -lt $listItemsTotal; $x++)
    {
        $Description = $caseLibItems[$x]["DocumentSetDescription"]
        $str = ""
        if('$Description' -ne $null)
        {
            $Description = $Description -replace "`n"," " -replace "`r"," "
            $str = $caseLibItems[$x]["LinkFilename"].ToString() + '}' + $Description
        }
        else
        {
            $str = $caseLibItems[$x]["LinkFilename"].ToString()
        }
        Write-Output $str | Out-File -Append "temp.csv" 
    }
} while ($query.ListItemCollectionPosition -ne $null)

import-csv temp.csv -delimiter "}" | export-csv -NoTypeInformation  -Path "result.csv"

Write-Host "Exiting"

Answer 2

You seem to have line breaks in the description field. You will need to enclose the data in double quotes before you write to the file.

$str = $caseLibItems[$x]["LinkFilename"].ToString() + '}' + '"' + $Description + '"'