0

i am trying export library data into CSV file. i had used string functionality on my script to get content. my script was working fine but i am getting output in single string (or) one column. My script and out as below.

Script

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

$query = new-object Microsoft.SharePoint.SPQuery 
$query.ViewFields = "<FieldRef Name='LinkFilename'/><FieldRef Name='DocumentSetDescription'/><FieldRef Name='Party_x0020_Lookup'/><FieldRef Name='State_x0020_code'/><FieldRef Name='Case_x0020_Manager'/>"
$query.RowLimit=50000

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"]
        $CaseManager = $caseLibItems[$x]["Case_x0020_Manager"]

        $str = ""
        if('$Description; $CaseManager' -ne $null)
        {
            $str = $caseLibItems[$x]["LinkFilename"].ToString() 
                + $Description 
                + $caseLibItems[$x]["Party_x0020_Lookup"].ToString() 
                + $caseLibItems[$x]["State_x0020_code"].ToString() 
                + $CaseManager 
        }
        else
        {
            $str = $caseLibItems[$x]["LinkFilename"].ToString()  
        }
        Write-Output $str | Out-File "C:\test.csv"  -Append
    }
} while ($query.ListItemCollectionPosition -ne $null)

Write-Host "Exiting"

Output file in CSV

XXXXX-2-AB-123,file was uploaded,country1,12345,xxxx@gmail.com

XXXXX-3-CB-124,data received,country2,12365,xyzab@gmail.com

XXXXX-8-AC-246,file mismatch uploaded,country1,12345,xxxx@gmail.com

I want to add headers on CSV and i want output file data below format on CSV file (Means, name content should come in one column, Description data should come in another column,Party lockup another column etc ).. How to split string in array. If anyone had idea please share with updated powershell script

Name Description Party Lookup State code Case Manager

XXXXX-2-AB-123 file was uploaded country1 12345
xxxx@gmail.com

XXXXX-3-CB-124 data received country2 1236 xyzab@gmail.com

XXXXX-8-AC-246 file mismatch uploaded country1 12345 xxxx@gmail.com

0

Create your outfile first and add a header to it:

$outFile = New-Item "C:\test.csv" -ItemType file -Force

Note that -Force will cause the file to be overwritten each time. Now add your header:

"Name,Description,Party Lookup,State code, Case Manager" | Out-File $outFile

You don't need $str just write your stuff out e.g.:

"$($caseLibItems[$x]["LinkFilename"].ToString()),$Description,$($caseLibItems[$x]["Party_x0020_Lookup"].ToString()),$($caseLibItems[$x]["State_x0020_code"].ToString()), $CaseManager" | Out-File $outFile -Append

For items that don't have all the values just add commas:

"$($caseLibItems[$x]["LinkFilename"].ToString()),,,," | Out-File $outFile -Append
  • Sorry, again i am getting output file same. Values are coming one column only. I need each header data need to another column. Means in "Name" column it should come "XXXXX-2-AB-123" and in "Description" need to come next column with data and etc. Column A = Name data, Column B= Description data, Column C= Party Lookup data etc. Hope you got my requirement. My final requirement is export all column data of SharePoint document library into CSV. Please help me with your updated code. – murali kuruva Sep 20 '18 at 11:10
  • How are you opening your files? If they are comma separated then they will be in separate columns. Just make sure you import them correctly. – Bunzab Sep 21 '18 at 7:41

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.