1

I need to export an entire list as a .csv file. I actually have this code which works great but I need to write each column I want to export:

$listTitle = "Example"
$list = $ctx.get_web().get_lists().getByTitle($listTitle);
$query = New-Object Microsoft.SharePoint.Client.CamlQuery;
$query.ViewXml = "<View><Query><Where><Geq><FieldRef Name='ID' /><Value Type='Counter'>1</Value></Geq></Where></Query></View>"
$listItems = $list.GetItems($query);
$ctx.Load($listItems);
$ctx.ExecuteQuery();
$tableau =@();
foreach ($listItem in $listItems)
{
    $result = new-object psobject
    $result | Add-Member -MemberType noteproperty -Name Title -value $listItem['Title'];
    $result | Add-Member -MemberType noteproperty -Name Description -value $listItem['Description'];
    $result | Add-Member -MemberType noteproperty -Name Age -value $listItem['Age'];
    $result | Add-Member -MemberType noteproperty -Name Sexe -value $listItem['Sexe'];
    $tableau += $result;
}
$CsvName2 = "Export_"+$ListTitle.replace(' ','_')+".csv"
$tableau | export-csv $CsvName2 -notype;

The part I want to improve is the foreach loop:

foreach ($listItem in $listItems)
{
    $result = new-object psobject
    $result | Add-Member -MemberType noteproperty -Name Title -value $listItem['Title'];
    $result | Add-Member -MemberType noteproperty -Name Description -value $listItem['Description'];
    $result | Add-Member -MemberType noteproperty -Name Age -value $listItem['Age'];
    $result | Add-Member -MemberType noteproperty -Name Sexe -value $listItem['Sexe'];
    $tableau += $result;
}

I want the script collects all columns but I don't know how to do it...

Thanks for your help

1

You need another foreach cycle:

Replace this:

foreach ($listItem in $listItems)
{
    $result = new-object psobject
    $result | Add-Member -MemberType noteproperty -Name Title -value $listItem['Title'];
    $result | Add-Member -MemberType noteproperty -Name Description -value $listItem['Description'];
    $result | Add-Member -MemberType noteproperty -Name Age -value $listItem['Age'];
    $result | Add-Member -MemberType noteproperty -Name Sexe -value $listItem['Sexe'];
    $tableau += $result;
}

with something similar:

foreach ($listItem in $listItems)
{
    $result = new-object psobject
    foreach ($field in $listItem.Fields) 
    {
        if ($field.Hidden -eq $false)
        {
            $result | Add-Member -MemberType noteproperty -Name $field.Title -value $listItem[$field.InternalName]; 
        }
    }
    $tableau += $result;
}

Don't have access to PS now, but this should work (maybe with minor tweaks).

  • Thanks for you ultra-rapid answer ! The first test I made using your code resulted in a blank .csv file, I try to find the problem and tell you here. – Gordon Amable Oct 14 '15 at 14:16
  • Hi, can't fix the error... I'm quite a nooby in CSOM/Powershell – Gordon Amable Oct 15 '15 at 7:55

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.