© 2019 by Sayantan Basu

  • Sayantan Basu

Import XML / Excel / CSV to Tridon Content Manager Using Core Service API

This topic may already be discussed in different blogs but I would like to share my ideas to consolidate all in one place. Very often your client will ask to migrate or import the data from 3rd party system into Tridion item. So here I will share one of the possible ways to import the content into the Tridion component.

Scenario:

In my recent project business guys want to create or update the content of frequently asked question on their site periodically and for that they provide the content in Excel or CSV or XML format. This type of situation is perfect to automate the component creation using Core Service API.

Task to Follow:

The most common scenario is clients have data in CSV, Microsoft Excel files or XML files for content import, But to make it more generic I am using the XML format. So if required you can easily convert your excel or csv to xml. Then import it using custom page and create the component.


I have divided the whole task in below sub tasks: –

  1. Make your XML file ready to import.

  2. Create your web application to upload and read the xml.

  3. Create the model as per your schema and set the value from the xml to your model.

  4. Using the core service API create your component.

  5. Integrate your application with Tridion as a custom page.

Make your XML file ready to import: Double check the sample xml file and validate it with your schema.

Create your web application to upload and read the xml:

1)     Design of the web application Create one web application with the file upload option. Here is the design of my web application.

You can personalize this design as per your requirement. Here is the code for Upload button click –

 /// <summary> /// On Upload button click Save the data as a component into TCM /// </summary> /// <param name=”sender”></param> /// <param name=”e”></param> protected void Upload_Click(object sender, EventArgs e) { string result; CSVFileUpload.SaveAs(Server.MapPath(“~/tmpdir/”) + CSVFileUpload.FileName); System.IO.StreamReader myFile = new System.IO.StreamReader(Server.MapPath(“~/tmpdir/”) + CSVFileUpload.FileName); XDocument doc = XDocument.Load(myFile); myFile.Close(); List<ModelFAQ> modelFaqList = new List<ModelFAQ>(); result =Utility.CSVFileValidation(doc);// This method is used to validate the xml file if (result == “OK”) { modelFaqList = Utility.FAQModelList(doc); // Read your xml and set the value into the model class CreateComponent _createComponent = new CreateComponent(); result = _createComponent.SetFAQComponent(modelFaqList); // Create your component messageLabel.Visible = false; messageLabel1.Visible = true; messageLabel1.Text = result; } else { messageLabel.Visible = true; messageLabel.Text = “Data is not valid. ” + result; } } }

2)     Model to Store the XML data: To store the data of xml add a new model class in your project. Once your class is ready then create a method to save the data from xml into your class. Now your data is ready to use.

Create the model as per your schema and set the value from the xml to your model:

Here I have created another Model class with the same field name like my schema. For your understanding 1)      Here is my schema details –

I have created that schema using one embedded schema and a link to my Keyword in the metadata design so that I can cover the entire possible situation in one place.

My Main Schema is FrequentlyAskedQuestion and as you can see I have an embedded schema within it called “Paragraph” to store the answer of the question.

So in my code I have one Model class for the FrequentlyAskedQuestion and another model class for Paragraph.

Below are the Model classes –

Model class for “Paragraph” schema –

namespace ImportToTridionComponent { [Serializable] [XmlRoot(ElementName = “Paragraph”, Namespace = “FAQ”)] public class ParagraphXMLEntities { # region Paragraph XML Properties [XmlElement(“paragraph”)] public string Paragraph { get; set; } #endregion public ParagraphXMLEntities(ParagraphXMLEntities _ParagraphXMLEntities) { this.Paragraph = _ParagraphXMLEntities.Paragraph; } public ParagraphXMLEntities() { } public string Serialize() { XmlWriterSettings settings = new XmlWriterSettings { OmitXmlDeclaration = true, Indent = true, Encoding = Encoding.ASCII }; using (MemoryStream stream = new MemoryStream()) using (XmlWriter writer = XmlWriter.Create(stream, settings)) { XmlSerializerNamespaces ns = new XmlSerializerNamespaces(); ns.Add(string.Empty, “Paragraph”); XmlSerializer serializer = new XmlSerializer(typeof(ParagraphXMLEntities)); serializer.Serialize(writer, this, ns); string xml = Encoding.ASCII.GetString(stream.ToArray()); return xml; } } public static ParagraphXMLEntities Deserialize(string xml) { ParagraphXMLEntities result; using (StringReader stringReader = new StringReader(xml)) using (XmlReader xmlReader = XmlReader.Create(stringReader)) { XmlSerializer serializer = new XmlSerializer(typeof(ParagraphXMLEntities)); result = (ParagraphXMLEntities)serializer.Deserialize(xmlReader); } return result; } } }

Model class for “FrequentlyAskedQuestion” schema –

namespace ImportToTridionComponent { [Serializable] [XmlRoot(ElementName = “FAQ”, Namespace = “FAQ”)] public class FAQXMLEntities { private ParagraphXMLEntities _ParagraphXMLEntities = new ParagraphXMLEntities(); public FAQXMLEntities(FAQXMLEntities _FAQXMLEntities) { this.Heading = _FAQXMLEntities.Heading; this.Paragraph = _FAQXMLEntities.Paragraph; } public FAQXMLEntities() { } # region FAQ XML Properties [XmlIgnore] public string TcmId { get; set; } [XmlElement(“heading”)] public string Heading { get; set; } [XmlElement(“paragraph”)] public ParagraphXMLEntities Paragraph { get; set; } //As Paragraph is an Embedded field, so Create an instance of Paragraph Model #endregion public string Serialize() { XmlWriterSettings settings = new XmlWriterSettings { OmitXmlDeclaration = true, Indent = true, Encoding = Encoding.ASCII }; using (MemoryStream stream = new MemoryStream()) using (XmlWriter writer = XmlWriter.Create(stream, settings)) { XmlSerializerNamespaces ns = new XmlSerializerNamespaces(); ns.Add(string.Empty, “FAQ”); XmlSerializer serializer = new XmlSerializer(typeof(FAQXMLEntities)); serializer.Serialize(writer, this, ns); string xml = Encoding.ASCII.GetString(stream.ToArray()); return xml; } } public static FAQXMLEntities Deserialize(string xml) { FAQXMLEntities result; using (StringReader stringReader = new StringReader(xml)) using (XmlReader xmlReader = XmlReader.Create(stringReader)) { XmlSerializer serializer = new XmlSerializer(typeof(FAQXMLEntities)); result = (FAQXMLEntities)serializer.Deserialize(xmlReader); } return result; } } }

Using the Core Service API create your component:

Here is my final code to create the component. 1)     First create a client for Core Service As I am using Tridion 2013 so here is my core service client –

    public static CoreServiceClient GetCoreServiceSettings() { var binding = new NetTcpBinding { MaxReceivedMessageSize = 2147483647, ReaderQuotas = new XmlDictionaryReaderQuotas { MaxStringContentLength = 2147483647, MaxArrayLength = 2147483647 } }; var endpoint = new EndpointAddress(“net.tcp://localhost:2660/CoreService/2013/netTcp”); var client = new CoreServiceClient(binding, endpoint); client.ChannelFactory.Credentials.Windows.ClientCredential = new NetworkCredential(“Admin User id”, “Password of your Admin user”); return client; }

As I am going to connect the TCM from client machine so don’t forget to override the client credential with the admin credential to avoid the access related issue.

2)     Method to create the component using that client: Now using this client I will create my new component –

Create an instance of the client –

var client = GetCoreServiceSettings();

Now set the value into the schema model-

FAQXMLEntities _FAQXMLEntities = new FAQXMLEntities(); ParagraphXMLEntities _ParagraphXMLEntities = new ParagraphXMLEntities(); _FAQXMLEntities.Heading = item.question; _ParagraphXMLEntities.Paragraph = item.answer; _FAQXMLEntities.Paragraph = _ParagraphXMLEntities;

I have already defined the path of the component and schema id within the app settings of the web config-

<appSettings> <add key=”Path” value=”/webdav/200%20Master%20Content%20(ENG)/Building%20Blocks/Content/FAQ/”/> <add key=”200 FAQ Schema” value=”tcm:5-3467-8″/> </appSettings>

Therefore prepare the content of the component –

ComponentData comp = = new ComponentData(); comp.Id = “tcm:0-0-0”;//hard coding will be placed in some constent. comp.Title = _FAQXMLEntities.Heading; comp.ComponentType = ComponentType.Normal; comp.LocationInfo = new LocationInfo() { OrganizationalItem = new

Here I am reading the component location and schema information from web configuration settings –

LinkToOrganizationalItemData() { WebDavUrl = System.Configuration.ConfigurationManager.AppSettings[“Path”];// read the path from the web config file } }; schemaId = System.Configuration.ConfigurationManager.AppSettings[“200 FAQ Schema”];//read the schema id from the web config file comp.Schema = new LinkToSchemaData() { IdRef = schemaId };

To read the metadata of the component I used the below code –

comp.Metadata = @”<Metadata xmlns=””FAQ””>” + @”<keywordLink>” + _modelFaq.subCatagory + @”</keywordLink> </Metadata>”; comp.Content = _FAQXMLEntities.Serialize();

Next I have created a method called GetTcmId() to check the component already exist or not.

Here is the final step –

If component does not exist then –

ComponentData newComp = client.Create(comp, new ReadOptions()) as ComponentData;

I have provided the full code below –

        /// <summary> /// Retrive all the components from the folder specified by the user and check if that component exists then return that id ///  else return the empty string /// </summary> /// <param name=”client”>Coreservice Client</param> /// <param name=”Id”>Tcm Component Title</param> /// <param name=”locationTcmID”>folder location</param> /// <returns></returns> public string GetTcmId(CoreServiceClient client, string Id, string locationTcmID) { try { string tcmID = string.Empty; //retrive the XML list of component from the folder var productsXML = client.GetListXml(locationTcmID, new OrganizationalItemItemsFilterData { ItemTypes = new[] { ItemType.Component } }); //loop through each item and find out if it is the product we want already exist foreach (var product in productsXML.Elements()) { tcmID = product.Attribute(“ID”).Value; var productData = client.Read(product.Attribute(“ID”).Value, null) as ComponentData; var schemaFields = client.ReadSchemaFields(productData.Schema.IdRef, false, null); XNamespace ns = schemaFields.NamespaceUri; //check if the product id’s match if ((XDocument.Parse(productData.Content)).Root.Element(ns + “heading”) != null) { if (Id == (XDocument.Parse(productData.Content)).Root.Element(ns + “heading”).Value) { return tcmID; } } } return String.Empty; } catch (Exception Ex) { return string.Empty; } }

Integrate your application with Tridion as a custom page:-

Now your code is ready to publish. So build your code and deploy it within the web server where Tridion is installed. Create a custom page in Tridion and map it with your web page.

Summary:

So just to summarize the whole thing –

  1. Validate the xml that you want to import into the Tridion CMS.

  2. Create a web application with file upload option and read the xml file.

  3. Create your model ready as per the schema design.

  4. Instantiate a client of the Core Service and using it create your component.

Hey! I know this is one of the lengthy articles that I have created, but I just want to consolidate the entire code at one place. Thanks for reading this article. If you need the solution file or any further information for more clarification then please reply on this blog. I will mail you the entire solution.

#TridionCoreService