Ramblings of a data dork: from BI and Big Data to Travel and Food
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.
So after doing this multiple times, I proceeded to do my usual atom feed import like I usually do:
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
6) But instead of the nice message indicating that you had imported data properly:
so it shows up nicely in your PowerPivot workbook
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.
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