Import Data from Excel Sheet into Sitecore

I managed to work recently on many sites that required importing Data into Sitecore from an Excel Sheet. These Data might be from Sharepoint, xml, Migrated data, actually any kind of data that you managed to store in Excel and need to import into Sitecore.

Below, a number of code snippets that allows you to do so by using CSharpJExcel.Jxl library to read Excel sheets.

Create a method to that takes the destination path (where the new Item would be stored in Sitecore tree), Item name and the template ID from which the new Item would be created.


Once the Sitecore item is created, you can loop through all the item fields to update the Sitecore Item field with the new value. This method takes a Sitecore item, Sitecore field and the new value to update the field.

The last step would be to read the Excel sheet, loop through the entire sheet and create a new Sitecore item at each row. Once an item has been created, we would update fields based on columns values. In this case, I had a dictionary that maps these column names in the Excel sheet with the Sitecore fields. Another approach would be saving data into the Excel sheet with same name of the Sitecore fields id you already know the template otherwise, you need to create a mapping file (xml for example), that directs the mapping between the Excel sheet columns and Sitecore fields.

That’s it, you can create another method to validate the Item name and put these all together in a simple import page with a button.

5 thoughts on “Import Data from Excel Sheet into Sitecore

  1. Hi Ishraq,

    Thats a great idea of importing content from Excel to Sitecore and really appreciate your efforts and idea put together to build such functionality. However, I am curious to understand, how this approach can be expanded in order to have multi-locale / multi-lingual site on single instance. I have been working long on this but failed to understand / implement the below approaches:

    1. Utilizing importing content in multi-site / multi-lingual website on single instance,
    2. As item get created on the fly, how to check if the item-path exist to avoid duplicacy on the item names.
    a. if item path exist, then only the content should be updated else
    b. if item path does not exist, then a new item with content should be created.

    I am looking forward for your ideas around the points I mentioned above, would appreciate your feedback on blog too.

    Regards,
    Amit

    • Hi Amit,

      Sorry for the late reply, been away from my blog for a while now.
      I totally agree with your assumptions, as this code was written to handle a one time import for data where no need to check for Item uniqueness.

      To expand this and make sure if the item already exists then update rather than create, we usually use something like an import field with a unique id, so if this field is used many times in the Excel sheet, we can always get item by this unique field and do the update. The other solution as you suggested to check for names, in our case we will define our sitecoreItem as follows:
      // Parent Item is the folder we passed to the create method as destination path
      // In this case we need to move this logic outside the create method
      sitecoreItem = parentItem.Axes.GetChild(itemName);
      if (sitecoreItem == null)
      {
      // Call create Sitecore Item method
      // First code snippet
      }
      else
      {
      // Call Update Sitecore fields method
      // Second code snippet
      }

      Regarding creating multilingual instances from the item we will use Versions. In this case, we can create a copy from the original item in a new version with the same content, then update the fields with the new content.
      private void CreateVersionedItem(ID id, Language sourceLanguage, Language newLanguage)
      {
      Sitecore.Data.Database master = Sitecore.Data.Database.GetDatabase(“master”);
      Item sourceItem = master.GetItem(id, sourceLanguage);
      Item newItem = master.GetItem(id, newLanguage);

      if (sourceItem == null || newItem == null || newItem.Versions.Count > 0) return;

      using (new Sitecore.SecurityModel.SecurityDisabler())
      {
      newItem.Versions.AddVersion();

      newItem.Editing.BeginEdit();
      foreach (Field field in sourceItem.Fields)
      {
      newItem[field.Name] = sourceItem[field.Name];
      }
      newItem.Editing.EndEdit();
      }
      }

      or you can just skip the editing part and return the newItem and update the content through the import loop.
      You can always get the new item using:
      master.GetItem(id, newLanguage)

      Please let me know if this helps.

  2. Pingback: Sitecore Content Migration Basics | Horizontal Integration
  3. Pingback: Re-blogged: Import Data from Excel Sheet into Sitecore | Sitecoring

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s