menu

Export Sitecore items to Excel (CSV)

I needed to quickly export some data for a client and found a very convenient way to do this by using LinqPad.

With a post to the Sitecore Webservice we can easily extract data from the Sitecore System. Here is an example of the post in Fiddler:

exportcsvfiddler

By using Linqpad (https://www.linqpad.net/Download.aspx) I can use the WebClient class to get the data and quickly transform it to an CSV:

linqpadexportsitecore


System.Net.WebClient client = new System.Net.WebClient();
client.Headers.Add("Content-Type", "application/soap+xml; charset=utf-8");
string postData = @"<?xml version=""1.0"" encoding=""utf-8""?>
<soap12:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap12=""http://www.w3.org/2003/05/soap-envelope"">
<soap12:Body>
<GetXML xmlns=""http://sitecore.net/visual/"">
<id>{76345ED0-8D94-4186-9E19-9D38715E467E}</id>
<deep>true</deep>
<databaseName>master</databaseName>
<credentials>
<CustomData></CustomData>
<Password>passwordgoeshere</Password>
<UserName>usernamegoeshere</UserName>
</credentials>
</GetXML>
</soap12:Body>
</soap12:Envelope>";

var restult = client.UploadString("{URLGOESHERE}/sitecore/shell/WebService/service.asmx", postData);

var root = XElement.Parse(restult);
string lang = "nl-NL";
IEnumerable<XElement> items =
from el in root.Descendants()
where
el.Name == "item" && el.Attribute("template")?.Value == "route pagina"
select el;

Console.WriteLine("ItemGuid,ItemName,ItemLongitude,ItemLatitude");
foreach(var item in items){
Console.WriteLine(String.Format("{0},{1},{2},{3}",
item.Attribute("id")?.Value,
item.Attribute("name")?.Value,
item.Elements("version").FirstOrDefault(s => s.Attribute("language")?.Value == lang).Descendants("field").FirstOrDefault(x => x.Attribute("key")?.Value == "gd_longitude")?.Value,
item.Elements("version").FirstOrDefault(s => s.Attribute("language")?.Value == lang).Descendants("field").FirstOrDefault(x => x.Attribute("key")?.Value == "gd_latitude")?.Value
));
}

Just save the output into a csv file and you are done! Thats it!


June 13, 2016
wpDomitnator909

Leave A Reply

You must be logged in to post a comment.