Tag Archives: sitecore

Sitecore CMS

Excel Transfer Utility for Sitecore

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!

Sitecore Query with MultiList and multiple sites

Have you ever been in the situation where you would like to point the “Source” field of template field to an item that will be used with a List Type field such as the MultiList, but you wanted that field to be pointed to a folder in the site the user is currently visiting?

Multiple Sites

Multiple sites in Sitecore

One reason you may want to set a “Source” field to an item in the context site may be because the item you are creating is intended to be used with the DMS (Digital Marketing System) for personalization, A/B, and multivariate testing.  We can get solid results in those areas with a data source component-based architecture where components can be conditionally rendered across pages in the same site or even across all sites in the Sitecore instance.

Anyways, an easy way of setting up a “Source” for a MultiList is simply just to point the “Source” field to the /sitecore/content/ item and let the user drill down into the folders to get to the folder they want.  However, depending on how many sites the Sitecore instance has, in addition to the amount of content, this will turn out in most cases to be a bad user experience, and could lead to error where the user may accidentally choose items from another site.

A better solution is to use Sitecore query to set the “Source” field in the template field.  If you are not familiar with the Sitecore Query or Sitecore Fast Query, I would recommend visiting the Sitecore Developer Network and reading up on it.  The most recent Sitecore Query documentation I found was here: Sitecore Fast Query documenation.  You will find it’s not that difficult to grasp and you can start using it right away once you see how the useful the queries can be in your day to day Sitecore development.  In addition, Sitecore was kind enough to provide us Developers with the XPath Builder available in the Developer Center application via Tools –> XPath Builder.  Below is a screen shot of the XPath Builder application:

XPath Builder

XPath Builder application in Sitecore Developer Center

All you need to do is set the Context Node to the item you want to start with for the query, build your XPath expression in the next field, and hit the Evaluate button and you will see the results in the Result field.  Using this tool you don’t have to guess if your query is correct when you insert it in the “Source” field.  Build it here, test it, then just copy and paste it into the “Source” field (along with “query:” added to the beginning of the query).  That simple!

In one of my projects, I needed to point to a data folder in the context site that had the items I needed, as children, that I wanted to show in my MultiList.  In the example, below I wanted to set the “Source” field in my ScriptSelectionItem to the ScriptItems folder for the context site so the user would only see the ScriptItems created for that site only and the user can just select the ones they want.  Again, we want to get away from drilling down into the site specific folders and then making selections to create a better experience for the user and reduce errors.

Sitecore template

We will set the Source field with the Sitecore query built and tested in XPath Builder

Below is my query that I built and tested in the XPath Builder to start with the ancestor-or-self of the context item and then match up the template ids of the items once the initial ancestor-or-self item is established:

./ancestor-or-self::*[@@templateid='SITE TEMPLATE ID']//*[@@templateid='DATA TEMPLATE ID']//*[@@templateid='SCRIPTS TEMPLATE ID']//*[@@templateid='SCRIPTITEMS TEMPLATE ID']/*

When you add the query to a “Source” field you need to add the “query:” to the front of the query as such:

query:./ancestor-or-self::*[@@templateid='SITE TEMPLATE ID']//*[@@templateid='DATA TEMPLATE ID']//*[@@templateid='SCRIPTS TEMPLATE ID']//*[@@templateid='SCRIPTITEMS TEMPLATE ID']/*

Now, when I add the query to the “Source” field in my ScriptsSelectionItem template field and open the ScriptSelectionItem in the Content tree, I now see that the MultiList field points to only the items in the context site for the user.  I have not gotten this to work with the DropTree List Type but the MultiList has worked just fine for my purposes.  Happy coding!

The ScriptSelectionItemSiteB should only point to the 1 script in the ScriptItems folder

The ScriptSelectionItemSiteB should only point to the 1 script in the ScriptItems folder

Notice only the 1 script is available for Site B

Notice only the 1 script is available for Site B

Now for Site A:

The ScriptSelectionItemSiteA should only point to the 3 scripts in the ScriptItems folder

The ScriptSelectionItemSiteA should only point to the 3 scripts in the ScriptItems folder

Notice the 3 scripts are available for Site A

Notice the 3 scripts are available for Site A

FieldRenderer and EnclosingTag property

So I was working on a sublayout earlier today and I ran across a piece of HTML that made me say, “Hmmm…, what is the best way to tackle this situation”?  I had the HTML below to work with for the “Page Title” and “Sub Title” fields that were to be simply rendered in a FieldRenderer control.

<h1>My Account <small>Manage your account online</small></h1>

The title “My Account” would always exist but the “Manage your account online” in the <small> element would be optional for the Content Editor to use to display sub-titles.  Well, I am a programmer who likes to leave no markup behind so I quickly remembered the EnclosingTag property of the FieldRenderer that made perfect sense in this situation.  Below is the markup I used to pull in the fields:

<h1>
<sc:FieldRenderer runat="server" FieldName="Page Title" ID="frPageTitle"></sc:FieldRenderer>
<sc:FieldRenderer runat="server" FieldName="Sub Title" ID="frSubtitle" EnclosingTag="small"></sc:FieldRenderer>
</h1>

Instead of leaving the <small> HTML element in the markup on the page if the “Sub Title” field is blank, I used the EnclosingTag property to ensure that if there is a value to render it inside the <small> HTML element. However, if the field is blank then don’t display the <small> HTML element either. Pretty slick when you need it sometimes! Happy coding!

Easy deleting of Sub-Items in Sitecore

Ok, so I have been working in the Sitecore Content Editor for 5 years now and it shows that you learn something new every day.  I needed a quick and easy way to delete a bunch of sub-items.  For the longest time I would go through and delete each item from the list.  If the deleted item had sub-items then I would delete that whole folder with the sub-items as well.  However, what if I just wanted to delete the items that were sub-items but not the actual item itself?  This is where I started looking through the Ribbon for the answer and found it.  Take a look at the given Colors item list:

Colors Item List

All you have to do is click on the parent item, which in this case is the Colors folder item, and then click on the Home tab in the Ribbon, and then click on the Delete drop-down in the Operations chunk.  You will then see the action to “Delete Subitems”, click on the icon and you will get a dialog box that says, “Are you sure you want to delete these 11 items?”  Click OK and voila they are all gone but the parent folder is left behind as seen below.  Easy peezy.  Happy coding!

Colors Item List - No Subitems

Fast and easy sorting in Sitecore

You can easily sort items in the content tree by clicking on the Alt button and then dragging the item where you want it and then letting go.  You will then receive a dialog box that says, “Are you sure you want to move ‘XYZa’ item before ‘XYZb’ item?”  Just click the OK button the item will now be there.  For a quick sort this has saved me time and hopefully it will help you.  Happy coding!