Ez Importer Excel Import Utility

Excel data import utility for Sitecore
07 November 2016

About

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.

Ez Importer Shared Source Module

Features

  • 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.

Screenshots

Launchpad shortcut

Ez Importer Launchpad Icon

Upload screen

Ez Importer Upload Data

Options screen

Ez Importer Options

Mapping screen

Ez Importer Mapping

Import map (created within the Content Editor)

Ez Importer Map Structure

Download

Installation

Open the installation wizard in the Sitecore Desktop, upload and install the Sitecore.EzImporter-n.n.n.zip package.

Sample Data

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.

Running

To start EzImporter go to the Sitecore Launchpad and click the Ez Importer button.

  1. Click “Browse for Media Files” or drag your xlsx/xls/csv file onto the upload area.
  2. 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).
  3. Select the target language.
  4. 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.
  5. Click the MAPPING tab and select from the Existing Mapping dropdown mapping from the master database.
  6. Click Import.

Scheduled Task Configuration

  1. Under /sitecore/system/Tasks/Commands create a new command item using the Import Command template.
  2. Set the fields under the Import Parameters section
    Database
    The Sitecore database in which the items will be created/updated eg master/core/web.
    FirstRowAsColumnNames
    If checked skips the first row of data.
    FileName
    Import data file. Can be fully qualified eg C:\Temp\Cars.xlsx or relative to Sitecore webroot eg /temp/cars.xlsx.
    ImportLocation
    The location in the content tree to import data.
    TargetLanguage
    The language in which to create items.
    ImportMap
    The import map definition to use.
  3. Set the fields under the Import Parameters (advanced) section
    CsvDelimiter
    Delimiter used when importing from CSV (ignored when using XLS or XLSX).
    ExistingItemHandling
    Controls whether to add a new version, update current version or skip existing items.
    InvalidLinkHandling
    Controls whether to create a new item for missing link fields, set as broken link, set empty, or skip field.
    MultipleValuesImportSeparator
    The separator to use to separate multiple link items e.g. when importing multiple selected options for a checklist field.
    TreePathValuesImportSeparator
    The separator to use between folder names when setting a DropTree field type. Typically \ or /
  4. Create a new schedule item under /sitecore/system/Tasks/Schedules.
    • 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|Interval where From/Until = yyyyMMdd, DayOfWeek = 1 (sunday), 2 (monday) or 127 (everyday), Interval = HH:mm:ss

Feedback?

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.

Tags: Shared Source Modules
comments powered by Disqus