1

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.

  • As Piero answered... Also the $query.RowLimit=500000 is usually way past the allowed limit ;) (Default is 5000 max and you should implement paging if you need more) – Heiko Hatzfeld_MSFT Sep 27 '18 at 8:11
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"
  • Thanks for helping me. I added above code, but no use. Same old results coming. any other solution please. – murali kuruva Sep 27 '18 at 10:46
  • Try now with this full solution. I've tryed and it works. – Piero Sep 27 '18 at 16:38
  • Thank you Piero. Yes the script which you shared was worked and output coming correctly. – murali kuruva Sep 28 '18 at 4:02
  • Your welcome Murali. You can set it as a correct solution, if you think it. – Piero Sep 28 '18 at 7:06
0

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 + '"'
  • Hi sssreddy, The above script which is shared by Piero was worked correctly. Thanks for your help also. – murali kuruva Sep 28 '18 at 4:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.