Ez Importer Excel Import Utility
EzImporter is a shared source module for importing tabular data into Sitecore. It's unique because it maps tabular data to a tree structure by grouping the input data. It supports XLSX, XLS and CSV.
- Supports Sitecore 8.0, 8.1 and 8.2.
- XLSX, XLS & CSV file formats.
- Ability to skip, update, or add version for existing items.
- Set droptree, droplist, or droplink fields. Can automatically create link items.
Import map (created within the Content Editor)
Open the installation wizard in the Sitecore Desktop, upload and install the
To help with learning to use the tool, sample data is available on the marketplace page. Install the package
Sitecore.EzImporter.SampleMap-n.n.n.zip to see an example import map for the Cars.xlsx sample file.
Creating an Import Map
The mapping between input data and the content structure in Sitecore is defined in a map branch under the
system/Modules/Ez Importer/Maps node. A list of input column items is created, followed by a mapping structure defining what template to use and what input column maps to what template field.
Create a new map in the Content Editor by right clicking on the
/Maps item and selecting Insert > ImportMap. This will create a new map containing InputColumns and OutputMap subitems.
Items beneath InputColumns represent the columns of the excel / CSV file you wish to import. The names of these items do not have to match the column names in the excel file, but it may make life easier if they do.
The OutputMap item represents the template used for generated items. It has a field called TargetTemplate where the template is selected. Beneath this item is a Fields item which contains a sub-item for each populated template field. The names of the sub-items must match the template field names exactly. Within each OutputField item is a dropdown which links to the original input column names.
Multiple OutputMap items may exist beneath an ImportMap item. Each OutputMap may contain child OutputMap items. This mapping relationship is what enables building a tree structure from the tabular Excel data.
To start EzImporter go to the Sitecore Launchpad and click the Ez Importer button.
- Click “Browse for Media Files” or drag your xlsx/xls/csv file onto the upload area.
- Click the OPTIONS tab and choose the location within the content tree to perform the import (note that the import always goes into the master database).
- Select the target language.
- If necessary expand the Advanced Options section. Here there are 4 advanced settings:
- Existing Item Handling: controls whether to skip, update, or add a version to existing items
- Invalid Link Handling: controls whether broken link fields should try to create a new item, set the field with a broken link value, set the field as empty or simply skip that field.
- CSV Delimiter: applies to CSV import and is useful if a non-standard separator character was used.
- Multiple values separator: for multi-select fields controls the separator character used for different values.
- Click the MAPPING tab and select from the Existing Mapping dropdown mapping from the master database.
- Click Import.
Scheduled Task Configuration
/sitecore/system/Tasks/Commandscreate a new command item using the
- Set the fields under the Import Parameters section
- The Sitecore database in which the items will be created/updated eg master/core/web.
- If checked skips the first row of data.
- Import data file. Can be fully qualified eg C:\Temp\Cars.xlsx or relative to Sitecore webroot eg /temp/cars.xlsx.
- The location in the content tree to import data.
- The language in which to create items.
- The import map definition to use.
Set the fields under the Import Parameters (advanced) section
- Delimiter used when importing from CSV (ignored when using XLS or XLSX).
- Controls whether to add a new version, update current version or skip existing items.
- Controls whether to create a new item for missing link fields, set as broken link, set empty, or skip field.
- The separator to use to separate multiple link items e.g. when importing multiple selected options for a checklist field.
- The separator to use between folder names when setting a DropTree field type. Typically \ or /
- Create a new schedule item under
- Set the Command field to your newly created Import Command item.
- Leave the Items field empty.
- Set the Schedule field in the format
From|Until|DayOfWeek|Intervalwhere From/Until = yyyyMMdd, DayOfWeek = 1 (sunday), 2 (monday) or 127 (everyday), Interval = HH:mm:ss
Thanks for reading! If you have any questions or suggestions, please leave a comment below or via twitter.
If you find a bug, feel free to create an issue on the GitHub page here.