One of the cool things with PowerPivot is to click on an SharePoint List’s Export as Data Feed (i.e. an Atom feed) and its data can be imported directly into a PowerPivot for Excel workbook. To get this thing to work, one of the key things that I remind people regularly is to make sure you install ADO.NET Data Services Update for .NET Framework 3.5 SP1.
- ADO.NET Data Services Update for .NET Framework 3.5 SP1 for Windows 2000, Windows Server 2003, Windows XP, Windows Vista and Windows Server 2008
- ADO.NET Data Services Update for .NET Framework 3.5 SP1 for Windows 7 and Windows Server 2008 R2
So after doing this multiple times, I proceeded to do my usual atom feed import like I usually do:
Repro the issue
1) Go to my SharePoint List and click on Export as Data Feed
2) When requested to do a File Download of the List.atomsvc file, click Open
3) Because you installed PowerPivot on your box, it will give you the option to import this Atom feed into an existing (e.g. Book1) or new workbook. Choose appropriately and click OK.
4) It’ll provide you the Table Import Wizard so you can import your feed
5) Everything works like usual in terms of a PowerPivot data import
What did I expect?
6) But instead of the nice message indicating that you had imported data properly:
so it shows up nicely in your PowerPivot workbook
What did I actually get?
7) you get this strange error message:
in text this is:
For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method.
So how do I FIX this?
Thanks to John McConnell for reminding me that sometimes the solution is to do something simple (for context, I just had published the PowerPivot Security Architecture so my brain is still fried).
So the solution is to simply re-install ADO.NET Data Services Update for .NET Framework 3.5 SP1 again. Yup, rather long bit of writing to simply say – install it again!
But there you have it