codeflood logo

Importing lists with Revolver

Recently I had to create a list of reference items in Sitecore that was a few hundred items long. Doing this exercise manually would have been mind numbing and taken a few hours out of my day. Thanks goodness for Revolver!

Revolver contains the split command which can be used to split larger strings up. Combined with the echo command which can be used to input text from files and I’ve got the basic building blocks required to import large amounts of items from file.

Let’s give ourselves a solid example to work with. A common task for a lot of people may be to create a list of all the countries in the world for selection in a form. I’ll use Revolver to create a set of reference items containing the countries which can be read and populated into the form.

The first thing I need to do is find a source of all the countries. A bit of googling for “list of countries” brought me to http://www.listofcountriesoftheworld.com/.

list of countries site

This page is perfect for what I need to do.

Next, select the countries from the table and copy them. Now we’re going to use Excel to separate out that numeric identifier from the country name. One thing to note about the way Revolver works, it doesn’t assume you’re working in a vacuum. Yes, it is a command prompt, but unlike a command prompt for an OS it knows that you probably also have a mouse and can select things and copy them and such. Rather than try and replicate complex pasting behaviour into Revolver it makes more sense to use an external tool to get the data into the right format for processing.

So after copying the countries, paste them into Excel. Lucky for us Excel understands HTML tables and knows how to split the blob of HTML we have on the clipboard into separate cells in the spreadsheet. Now delete the number column (the first one). You should be left with a single column of country names.

list of countries excel

Now save the file as a CSV so we’re left with a text file containing the textual country names, one on each line of the file.

Fire up Sitecore, log in and start Revolver. I’ll first show you how to read the file using the echo command.

But first, a quick note about line endings. For some reason, when I was preparing this example the CSV file that Excel generated ended up with \r for line endings instead of the standard Windows \r\n line endings. Although I’ve updated Revolver 1.2.1 to deal with mixed line endings if you’re using an earlier version of Revolver you’ll need to convert the line endings to normal Windows line endings \r\n or the UI won’t handle the \r from the file.

echo can be used to input and output content from files using the -f parameter. To have the command input the file rather than output use the -i parameter.

echo -i -f c:\temp\countries.csv

This will list all the countries in the UI. We’ll need to combine this command with the split command to split the lines on the newline characters and execute a command against each line.

The split command contains a parameter for splitting on newline, -n.

split -n < (echo -i -f c:\temp\countries.csv) (echo (found line) $current$)

Note how we pass the previous echo command (the one used to read the file in) as a sub-command? The left angle bracket < causes Revolver to evaluate the next argument and pass the output of that execution into the command. The last parameter supplied to split is the command to execute against each line. We can make use of the special token $current$ in that parameter which will get replaced with the current line from the file. This command will get executed for each of the lines.

echo and split

Now all we have to do is change the command being executed against each line to create an item using the current line as the name.

split -n < (echo -i -f c:\temp\countries.csv) (touch -t (sample/sample item) ($current$))

Note how we’ve surrounded the $current$ token in parenthesis? This is because the current line may include spaces and we need to ensure they are handled as part of the name parameter and not treated as the path parameter.

But don’t get ahead of yourself and execute that command just yet…if only it was that simple. If you had a look at the country list you’d see that some of the country names contain illegal Sitecore name characters such as comma (,), apostrophe (') and double quotes ("). We’ll need to replace those.

Revolver contains the replace command which allows you to use regular expressions to replace matches in strings. Rather than replace each of the invalid characters, I’ll instead use a regular expression to replace any illegal character with nothing.

split -n < (echo -i -f c:\temp\countries.csv) (touch -t (sample/sample item) < (replace ($current$) [^\w\s] ()))

But I’m not quite happy with that. Although the above will create the list of reference items, I want to include the original country name in the title field of the reference item’s I’m creating for when the item is displayed on the website.

To execute multiple commands for each line, I’ll need to create a Revolver script and call that script in the split command. The script is going to be very simple, just create the item as we did above and set the title field from the original name. We’ll need to allow the original name to be passed into the script. Parameters can be passed to Revolver scripts as they are passed to commands. Inside the script I can reference the passed in parameters using a token $1$ where the number is the 1-based index of the parameter being passed in.

Scripts are created in the core database under the /sitecore/system/modules/revolver/scripts item and are based on the Revolver/Script template. Create a new script named create and fill in the following as the script:

set name < (replace ($1$) [^\w\s] ())
touch -t (sample/sample item) ($name$)
sf -nv title ($1$) ($name$)
set name

Let me explain the script above. Firstly we create an environment variable called name and set it as the output of the replace command we used in our original command above (stripping invalid name characters). We need to use this value multiple times so it’s easier to evaluate the value once and store it rather than continue to evaluate it. Notice the $1$ parameter? That will be substituted with the first parameter passed to the script when it’s executed. Next we create the item using the name we’ve created. After that we set the title field to the original name passed into the script. The last parameter passed to the sf (set field) command is a relative path to execute the command at which is how we have the command executed for the item we just created instead of on the current context item. By default Revolver will create a new version of an item before updating any field values so the -nv (no version) parameter is used to not create a new version of the item before updating the field value. The last command clears the environment variable we set. This is just good housekeeping.

Notice all the parenthesis around the variables $1$ and $name$? Those are to make sure the script works as expected when spaces have been included in those parameters.

To execute my script I just call it by name and pass it the unsafe name of the item to create.

create ("Bahamas, The")

I need to pass the parameter above enclosed in parenthesis cause the parameter does in fact have a space in it. The parenthesis cause the command parser to treat this as a single parameter rather than 2 separate parameters.

Now I can update my split command to make use of this new script to create the country items and set the title fields to the original values.

split -n < (echo -i -f c:\temp\countries.csv) (create ($current$))

And here’s the result of executing that command.

imported countries

Success!

So you can see how you can use Revolver to not only import content lists from file but also to transform the data in that file as well.

If you want more information on any of the commands used, keep in mind that Revolver contains built in help for all commands. All you need to do is type help and the name of the command to view help for the command.

Comments

Leave a comment

All fields are required.