Excel Transfer Utility for Sitecore

38 Replies

During my tenure working with Sitecore I have had a few clients that wanted the ability to import data into Sitecore items from an Excel spreadsheet as well as export Sitecore item/s data to an excel spreadsheet.  I was unable to find a shared source module that would do this simple transfer easily so I ended up having to build a utility for every client.  I finally decided to build a Shared Source Module for this basic task, http://marketplace.sitecore.net/en/Modules/Excel_Transfer_Utility.aspx.  The Excel Transfer Utility is a simple module that will give you the ability to import data from an Excel spreadsheet that are converted into newly created Sitecore items in the content tree. Also, the export functionality will export the data from a Sitecore item (single item or all children items) data to a .csv file for use with an Excel spreadsheet. This post will detail out the utility and how to use it.

Import

The Import functionality is intended to be used given the following requirements:

  • Microsoft Excel 97-2003 files only (Newer Excel files must be converted to 97-2003 before uploading)
  • Headers are in the first row of the spreadsheet
  • Item name is the first column of the spreadsheet

In this example we are going to import a simple spreadsheet into a folder item called “Excel Transfer Folder” in the Sitecore content tree as seen below:

Import Folder

Import Folder with ID: {A91FD891-C477-45F5-B20A-7CFA7F8B53E5}

We will also need to know the template ID of the template we want to use for the imported items. Below is the template we will use:

Sample Template

Sample Template with ID: {D2775315-00DC-4CF4-8B68-E9748127D188}

Below is an example with a simple spreadsheet with 3 fields (Single Line Text Field, Checkbox Field, and MultiList Field) and data for those fields:

Sample Excel Spreadsheet

Spreadsheet with 3 fields and sample data.

Now that we have a spreadsheet with data ready to import we simply open up the Excel Transfer Utility from the Sitecore admin area using the Desktop view:

Excel Transfer Utility Shortcut

Excel Transfer Utility Shortcut in Applications

Once clicked on the application will open up in a new window:

Excel Transfer Utility Home

Excel Transfer Utility Home Screen

Click the Import button and you will see the following options. Choose your file and click the Upload button:

File Selection for Import

File Selection for Import

Once the file is uploaded the sheet names in the Excel spreadsheet will be in the DropDownList.  Select the sheet with the data to import. Next, enter the template ID to use for the imported items and click the Next button:

Sheet & Template Selection

Sheet & Template Selection

Now select the Header name to be used for the item names.  Then select the Template Item Fields that are to be mapped with each of the spreadsheet fields (picked up from the Header in the first column). Next, enter the parent item ID where all items in the spreadsheet will be imported to as children and click Import:

Mapping & Item Selections

Mapping & Item Selections

If the import was a success then you will see the success screen as seen below:

Import Success Message

Import Success Message

Now when we take a look at the “Excel Transfer Folder” in the Sitecore content tree we can see the imported items, in this case since there is only one one row of data, there is only one item that was created:

Imported Items

Imported Items

When we look in the item at the field data we can see the data was imported into the fields for the item:

Imported Item Field Data

Imported Item Field Data

Text is imported as text, values for checkboxes are either “true” or “false” in the spreadsheet not 0 or 1, and lastly multilist values in the spreadsheet must be a pipe separated list of GUID’s.  Please note: mapping data to incorrect fields will product unexpected results such as a text field to a checkbox field in a Sitecore item.

Export

In this example we will export the item created in the Import example to a .csv file for use with Microsoft Excel:

Imported Items

Item to Export

Again, we simply open up the Excel Transfer Utility from the Sitecore admin area using the Desktop view and click on the Export button this time. Now, we select multiple items because I want to select all the items in the Excel Transfer Folder, which is one, but if there were several items all of them would get exported:

Initial Export Selection

Initial Export Selection

Next, we either insert the item ID (if single item) or the parent item ID (if multiple items) and the children items of the parent will be exported:

Multiple Items Export

Multiple Items Export

Once we enter the ID we click on the Export button and the items are exported to a .csv file:

Export Success with File Path

Export Success with File Path

Copy the file path into Windows Explorer and the file should open up in Microsoft Excel as seen below:

Exported Items

Exported Items

If you have any questions or ideas on how to make to the tool better please comment.  Thanks and happy coding!

38 thoughts on “Excel Transfer Utility for Sitecore”

    1. Toby Gutierrez

      At this time the utility does not support localization. Once the items are created you will need to create a new language version for those items and populate the data on your own manually.

      Reply ↓
      1. Amit Celly

        Hi Toby,

        Thanks for your reply. Is it someway poosible for you to provide the localization (multi-language) implementation?

        thanks in advance.

        Regards,
        Amit Celly

  3. Jayesh Sheth

    Hi Toby,
    I did use the Utility and thanks for sharing it.

    Usually this utility would be used to import large content into Sitecore.
    After Import, it names the Items as “New Item X” so after the successful import, you would have to manually rename these content items which would be very time consuming.
    Rather my thought was, can that be tied to the one of the column name from the spreadsheet?

    Reply ↓
      1. Jayesh Sheth

        How about making the Content Item name to be a Dropdown so that it can be chosen from the list of columns and not restricting it to only 1st column so that the end user decided what column needs to be used for creating Content Name.

      3. Tanner

        Hi Toby,
        I have downloaded the latest version from MarketPlace, but I am running into the same issue as stated above: The 10 items that I have uploaded all have the same item name “Title”. I think you stated that you were looking into this and would have it working a few months back, is there any update or possibly I have downloaded the wrong version. Or is there a different solution you may be able to propose for uploading ~200 items?

        Version download from MarketPlace:
        Release date: 2/6/2014 12:23:23 AM
        Revision date: 5/20/2014 7:25:54 AM

        Thank you!

      4. Toby Gutierrez Post author

        Hi Tanner! I apologize I have been wrapped up in project after project and haven’t had the time to update the module. As a matter of fact if you are anyone else is interested in contributing to this module please let me know. I need help to maintain and actually extend it to make it better.

        It is going to be another couple of weeks before I can get to this module. I am also waiting on Sitecore rep to give me access to my module on the Sitecore Marketplace to upload my revisions. I will let you know when I get it updated. Thanks for your patience!

    1. Toby Gutierrez Post author

      Tom, let me know what added features you were thinking of and I would be open to that. I just updated the module on the Sitecore Marketplace with the ability to choose which header name to use instead of “New Item X”.

      Reply ↓
      1. Rajni

        I used Your module today. It Works fine but With one problem:-(
        I selected EmployeeID as header name and thought that each item name will have unique name
        based on it’s value from Excel sheet but each and every item is having EmployeeID as item name
        Is it supposed to work this way? i have to rename every item With the value of EmployeeID.

      2. Toby Gutierrez Post author

        No, it should put the unique name from the EmployeeID field in this case. Thank you for bringing to my attention. Let me look into this bug and get back to you ASAP. Sorry for the inconvenience.

  5. Anuj Chawla

    Hi Toby,

    I tried the above package and exported the the item and its children by providing the parent Item ID. It is returning standard value of each item not the actual content of that item. Is there anything that I am missing.

    I actually wanted to extract the data from bucket.

    Can you help me in this case.

    Thanks a lot!

    Reply ↓
  6. Karthic

    First of all, Let me thank you for the Excellent you have here which otherwise would have taken my whole day .. I found 2 issues and seek your help on that.. the first one .. My item has only 2 fields.. where as my excel has around 15 columns which i dont care. .. So when i Mapped my
    Title -> Column1 ,
    Field1 -> Column 2,
    Field2-> Column 3.
    i left the other 13 mappings empty.. It throws an object reference Error..
    So i deleted the not wanted columns and tried. This time .. i still have 3 Mappings.. So i selected Title -> Column1 ,
    Field1->Column 2,
    Field2->Column 3

    But it still shows Column 1 ( which i dont want to map for fields because i want to map it to title). So i just selected Field2 for sake of testing.
    Items got imported. But it threw an error saying Cannot fine …/temp/Filename.xls. And all the items had the item name as “Title”
    Any help?

    Reply ↓
    1. Toby Gutierrez Post author

      Thanks so much for using the module and the feedback. I have been tied up with my typical day job doing Sitecore work. I will be getting to this asap for a couple of bugs as well as enhancements to the module hopefully next week. Are you still in need of help on this?

      Reply ↓
  7. Thomas Seyssens

    Hello Toby,
    We’re trying to use the module in Sitecore 7.2, but it seems like it’s not compatible. It installed correctly and without a problem. But when i try to open de module from the start-menu, all i see is a big error that the module is searching, but not finding this: “{E18F4BC6-46A2-4842-898B-B6613733F06F}”, any idea what i’m doing wrong?

    Reply ↓
  8. l0ddifyr

    Hi!
    I was trying this module on a Sitecore 6.6 installation today because one of my customers was requesting an itemtree as excel. If i try to export one item it work without any problems. But as soon as I use export mulitple items I get Error: The given key was not present in the dictionary. What is wrong? I can’t seem to find any log of what was wrong, and google isn’t very helpful either..

    best regards
    Henning

    Reply ↓
    1. Toby Gutierrez Post author

      Sounds like there is a missing item in the dictionary Sitecore is looking for. I will have to log this as a ticket and try to reproduce. You are saying that when you try to export multiple parent items (with children) you then get this error correct?

      Reply ↓
      1. l0ddifyr

        That is correct 🙂 Exporting one item works fine.. Selecting export multiple, id of a folder/parent Item and the error of missing key.

      2. l0ddifyr

        Hi again! Did you manage to reproduce this error? Or do you have an idea of what item Sitecore is looking for in the dictionary? Just wondering if I have to tell my client that I have to make an custom module for him instead of using this if I can’t get the multipel export to work

      3. l0ddifyr

        Just another info: I testet the module on a 7.1 installation of Sitecore, and there was no problems with the module there. So I guess the error has to be with my Sitecore is 6.6.0 (rev. 120918).

  9. l0ddifyr

    It finally works 🙂 Now I just have an idea on another feature 🙂 I know that the standard field delimiters for CSV files are commas, and on American Windows versions the comma is set as default as list seperator. But as a Norwegian, on a European Windows the comma is reserved as the decimal symbol, and the list seperator is the semicolon. It would have been great to had a config file where we could change the seperator char.

    Reply ↓
    1. Toby Gutierrez Post author

      This is great news! I have been tied down on other projects and haven’t had the time I needed to fix or update the module. Good to know about the separator character. Thank you!

      Reply ↓
    2. Albert

      Hi!
      I have the same issue(The given key was not present in the dictionary. ) while I was trying to export multiple items on my 7.1 Sitecore instance. Seems it’s not a Sitecore version issue.

      Best,
      Albert

      Reply ↓
  10. AnhTran

    Hi Toby Gutierrez,
    First thank you for contributing this module.
    It is useful.
    Did you update error using column name for item name yet?
    I want to use value of each cell in excel sheet as item name.

    Thanks.

    Reply ↓
    1. Toby Gutierrez Post author

      This should have been taken care of in a previous update. Also, I have a couple of other contributors to the module now, and that will help us to get bugs fixed, and new features out more promptly. Thanks Akismet!

      Reply ↓
  11. Nilesh Thakkar

    Hello Toby,

    First of all thanks for sharing such useful tool sharing in Sitecore community. I am using this tool in my two Sitecore projects.

    I found one thing missing in tool, so can you please share the code of this tool so that I can customize it as per my need.

    The missing thing is when you provide “template id”, it ONLY pull fields of that template ID and NOT inherited template fields so that I cannot use this tool many times.

    So please share the code.
    My email id is : nilesh.mscit@gmail.com

    Reply ↓
    1. Toby Gutierrez Post author

      Nilesh, I just created a space on GitHub to put in the source code. However, it’s not public just yet and we are updating the code, and testing to make sure it’s functional before we release to the Sitecore community. Once I set to public, I will announce via Twitter so just follow me on Twitter at https://twitter.com/sitecoresandbox. @sitecoresandbox

      Reply ↓
  13. Paras

    Hi Toby,

    I am trying to use this tool in Sitecore 8.0 but it is not working. I am using Excel file 97-2003 but still tool showing the error message “Upload Status: Only Microsoft 97-2003 Excel files are accepted”.
    Can you please on this issue?

    Thanks!
    Paras Joshi

    Reply ↓

Leave a Reply

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

Gravatar
WordPress.com Logo

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

Facebook photo

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

Cancel

Connecting to %s