Hello,

I'm attempting to export an external list to a CSV file using power shell. I already have a script to export SharePoint lists, however, it hasn't worked but gave me the below error.

An error occurred while enumerating through a collection: The shim execution failed unexpectedly - Could not load file or assembly 'System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
 or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)..
At C:\ExportListToCSV\Test.ps1:33 char:8
+ foreach <<<<  ($eitem in $eitems) {
    + CategoryInfo          : InvalidOperation: (Microsoft.Share...tanceEnumerator:SPEntityInstanceEnumerator) [], RuntimeException
    + FullyQualifiedErrorId : BadEnumeration

After doing some searching I came across a blog suggesting to add the below two statements, however, I'm still getting the same error. Now, the blog was referring to SharePoint 2013 while mine is 2010.

$ctx = Get-SPServiceContext http://sharepoint.domain.com/

$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx

Below is the powershell script I am using. Can someone point me in the right direction?

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#lines only for calling External Lists
$ctx = Get-SPServiceContex http://sharepoint.domain.com/write-host "CTX " $ctx

$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx
write-host "Scope " $scope

#Get the Web
$web = Get-SPWeb -identity http://sharepoint.domain.com/sites/level1

#Get the Target List
$elist = $web.Lists["ProductInventory"]

Write-Host The external list contains $elist.Items.Count items

#Get the target items
$eitems = $elist.GetItems()

foreach ($eitem in $eitems) {

$ExportItem = New-Object PSObject
#$ExportItem | Add-Member -MemberType NoteProperty -name "ItemID" -value $_["ItemID"]
$ExportItem | Add-Member -MemberType NoteProperty -name "ProductID" -value $_["ProductID"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "Name" -value $_["Name"]
$ExportItem | Add-Member -MemberType NoteProperty -name "MakeFlag" -value $_["MakeFlag"]
$ExportItem | Add-Member -MemberType NoteProperty -name "FinishedGoodsFlag" -value $_["FinishedGoodsFlag"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "Color" -value $_["Color"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "SafetyStockLevel" -value $_["SafetyStockLevel"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "ReorderPoint" -value $_["ReorderPoint"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "StandardCost" -value $_["StandardCost"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "ListPrice" -value $_["ListPrice"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "Size" -value $_["Size"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "SizeUnitMeasureCode" -value $_["SizeUnitMeasureCode"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "Weight" -value $_["Weight"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "DaysToManufacture" -value $_["DaysToManufacture"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "ProductLine" -value $_["ProductLine"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "Class" -value $_["Class"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "Style" -value $_["Style"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "ProductSubcategoryID" -value $_["ProductSubcategoryID"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "SellStartDate" -value $_["SellStartDate"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "SellEndDate" -value $_["SellEndDate"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "DiscontinuedDate" -value $_["DiscontinuedDate"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "ModifiedDate" -value $_["ModifiedDate"]

 #Add the object with property to an Array

 $ListItemCollection += $ExportItem

 }

#Export the result Array to CSV file
$ListItemCollection | Export-CSV "D:\Install\scripts\ExportListToCSV\TestProductInventory\output.csv" -NoTypeInformation                      

#Dispose the web Object
$web.Dispose()

Hi Darogael,

I have done a test in my SharePoint 2013 and I can reproduce the issue. I solved the issue finally.

For the error message:An error occurred while enumerating through a collection: The shim execution failed unexpectedly.

Just add the lines below above your get-spweb:

$ctx = Get-SPServiceContext http://sharepoint.domain.com/

$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx

But from the powershell script you offered, we can find that you missed a letter"t" of the Get-SPServiceContext.

For the second issue, the issue exists in the loop of the list.

Change the "foreach ($eitem in $eitems)" to "$ListItemCollection = @()  $eitems | foreach"

So, try the following PowerShell script:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#lines only for calling External Lists
$ctx = Get-SPServiceContext http://sharepoint.domain.com/
write-host "CTX " $ctx

$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx
write-host "Scope " $scope

#Get the Web
$web = Get-SPWeb -identity http://sharepoint.domain.com/sites/level1

#Get the Target List
$elist = $web.Lists["ProductInventory"]

Write-Host The external list contains $elist.Items.Count items

#Get the target items
$eitems = $elist.GetItems()

$ListItemCollection = @()

$eitems | foreach {

$ExportItem = New-Object PSObject 
#$ExportItem | Add-Member -MemberType NoteProperty -name "ItemID" -value $_["ItemID"]
$ExportItem | Add-Member -MemberType NoteProperty -name "ProductID" -value $_["ProductID"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "Name" -value $_["Name"]
$ExportItem | Add-Member -MemberType NoteProperty -name "MakeFlag" -value $_["MakeFlag"]
$ExportItem | Add-Member -MemberType NoteProperty -name "FinishedGoodsFlag" -value $_["FinishedGoodsFlag"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "Color" -value $_["Color"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "SafetyStockLevel" -value $_["SafetyStockLevel"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "ReorderPoint" -value $_["ReorderPoint"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "StandardCost" -value $_["StandardCost"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "ListPrice" -value $_["ListPrice"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "Size" -value $_["Size"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "SizeUnitMeasureCode" -value $_["SizeUnitMeasureCode"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "Weight" -value $_["Weight"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "DaysToManufacture" -value $_["DaysToManufacture"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "ProductLine" -value $_["ProductLine"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "Class" -value $_["Class"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "Style" -value $_["Style"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "ProductSubcategoryID" -value $_["ProductSubcategoryID"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "SellStartDate" -value $_["SellStartDate"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "SellEndDate" -value $_["SellEndDate"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "DiscontinuedDate" -value $_["DiscontinuedDate"] 
$ExportItem | Add-Member -MemberType NoteProperty -Name "ModifiedDate" -value $_["ModifiedDate"]

 #Add the object with property to an Array

 $ListItemCollection += $ExportItem

 }

#Export the result Array to CSV file
$ListItemCollection | Export-CSV "D:\Install\scripts\ExportListToCSV\TestProductInventory\output.csv" -NoTypeInformation                      

#Dispose the web Object
$web.Dispose()

For your reference:

http://www.sharepointdiary.com/2013/04/export-sharepoint-list-items-to-csv-using-powershell.html 

If you have problems, feel free to let me know.

Best Regards,

Lisa Chen 

Hi Lisa,

I tried the PS you provided and below is the error I am getting in my SharePoint 2010 enterprise environment.

CTX  Microsoft.SharePoint.SPServiceContext
Scope  Microsoft.SharePoint.SPServiceContextScope
The external list contains items
An error occurred while enumerating through a collection: The shim execution failed unexpectedly - Could not load file or assembly 'System.Data.OracleClient, Version=2.0.0.0, Cultu
re=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Ex
ception from HRESULT: 0x80070542)..
At D:\Install\scripts\ExportListToCSV\TestProductInventory\ExportOnlyV1.ps1:23 char:1
+  <<<< $eitems | foreach {
    + CategoryInfo          : InvalidOperation: (Microsoft.Share...tanceEnumerator:SPEntityInstanceEnumerator) [], RuntimeException
    + FullyQualifiedErrorId : BadEnumeration

Hi Lisa,

Any updates on this?

Hi,

From the error message, it is the enumerating error.

Does you use  Oracle as external list data source? In my SharePoint 2010 enterprise, I am using SQL as the external list data source. I can't met the issue.

Try to use the following command, change the site to your site and the external list to your external list:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#lines only for calling External Lists

$ctx = Get-SPServiceContext "http://sp"
write-host "CTX " $ctx
$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx
write-host "Scope " $scope

#Get the Web
$web = Get-SPWeb -identity "http://sp"

#Get the Target List
$elist = $web.Lists["ect"]

Write-Host The external list contains $elist.Items.Count items

#Get the target items
$eitems = $elist.GetItems()
write-host $eitems

$ListItemCollection = @()

$eitems | foreach {
$ExportItem = New-Object PSObject 
$ExportItem | Add-Member -MemberType NoteProperty -Name "Name" -value $_["Name"]

 #Add the object with property to an Array

 $ListItemCollection += $ExportItem

 }

#Export the result Array to CSV file
$ListItemCollection | Export-CSV "c:\ExportList.csv" -NoTypeInformation                      

#Dispose the web Object
$web.Dispose() 

Best Regards

Lisa Chen