How can we help you?
How to import and export with Excel
Nobody loves a spreadsheet. But sometimes Excel is just the right tool for the job. For instance, you might want to exchange information with people outside your organisation, who don’t have access to BeBanjo. For those occasions, BeBanjo has powerful Excel import / export functionality. Here is a guide to help you make the most of it.
Please note that BeBanjo supports spreadsheets in the Excel 97-2004 Workbook
.xls format or the latest Excel Workbook
.xlsx format. Contact your Technical Account Manager for more details about enabling support for
About IDs and External IDs
First, a few words on identifiers (IDs). Most things in BeBanjo can be identified by:
IDthat BeBanjo automatically generates every time you create content (e.g., Title ID:
External IDthat you can set to the value of your choice (e.g., Title External ID:
DES-001-02-03). This is useful if your content already has IDs defined in systems outside BeBanjo. You can record those External IDs in BeBanjo, allowing you to track content throughout your organisation.
ID and External IDs can be recorded in BeBanjo:
At the level of a brand (an example of a brand is: “Desperate Housewives”).
At the level of a series (an example of a series is: “Desperate Housewives, Season 1”).
At the level of a title (an example of a title is: “Desperate Housewives, Season 1, Episode 4”).
Note that not all titles in BeBanjo need to belong to a series or brand. For instance, a feature film (e.g., “Avatar”) does not belong to a series or brand.
Creating new titles in bulk using Excel import
Now, let’s use BeBanjo’s Title Import functionality to add new titles to the BeBanjo catalogue, in bulk. Go to the “Catalogue” page, click the “Create or Import” button, and choose “Import Titles”.
From this page, click “Download example file”, and you’ll get an Excel spreadsheet template. It contains a set of standard columns named: Type, ID, External ID, Name, Episode Number, Brand ID, Brand External ID, Brand Name, Series ID, Series External ID, Series Name, Season Number, Licensor, Tags, etc… The spreadsheet also contains other columns, that are specific to how BeBanjo was configured for your organisation. So you may find columns such as: Synopsis, Genre, Category… and all the other metadata fields you see when you look at a title’s metadata in the BeBanjo catalogue.
Now, let’s fill in the spreadsheet with the details of a few new titles: Alien, Aliens, Alien 3, and Alien: Resurrection. Note that we leave the ID fields empty: it’s up to BeBanjo to automatically generate its internal IDs. However, we can set the External IDs of our choice. It’s not required to do so but it can be useful; we’ll see why later.
Let’s import the spreadsheet
And our new titles have been created in the BeBanjo catalogue!
Dealing with errors
Sometimes, the Excel import will fail. For instance, it is required that you fill in the type column for every title you import, and you must set its value to either feature (for films) or episode. If you forgot to fill in the type column or put a random value there, the import will fail. Similarly, if you have metadata fields that appear as drop-down menus, then the Excel import will only accept the values set in the drop-down menus. This ensures the good quality of the metadata in your account.
What happens when we put in incorrect data in our spreadsheet? Let’s try with this example where the type of one of the titles has been left empty.
After importing the spreadsheet, BeBanjo flags the error:
We can click “Download Excel file” to see details of what we need to fix. The nice thing is that BeBanjo will successfully process all the titles it can and flag specifically those it can’t. So if you made 1 mistake amongst 100 titles, then BeBanjo will successfully import 99 titles the first time round.
Updating existing titles in bulk using Excel import
The Excel import functionality can be used, not only to create new titles, but also to update existing titles. Let’s change the Licensor for all the titles in our spreadsheet:
Let’s import it: BeBanjo does not create new titles, but instead updates the existing titles with the amended Licensor information.
How does BeBanjo recognise we’re talking about existing titles? Because we’ve used the same External IDs we had used when creating the titles initially. This is how handy External IDs can be.
When updating titles via Excel import, BeBanjo will ignore the cells you leave empty. So, for instance, if you leave the Synopsis column empty for a title that already has a Synopsis, then BeBanjo will not delete the existing Synopsis. That’s very handy too: you only need to update in Excel the fields you want to modify. You don’t need to fill in the rest.
Hey, but what if you actually want to delete the value that’s in a cell? Just type in
[[BLANK]] in the cell, and BeBanjo will know that you don’t want to leave the value unchanged, and BeBanjo will actually delete it.
Metadata fields with multiple values
Some metadata fields can accept, not just one, but multiple values. For instance, a film may have the followings genres: Science Fiction and Horror. To import that information, simply have multiple columns in you spreadsheet, all with the same column heading named Title:Genre (assuming you have a
Title:Genre metadata field configured in your account).
The only exception is the Tags column which accepts multiple values as comma-separated lists, like in the example below.
There are two ways to export information out of BeBanjo in the Excel format. The first one is called the catalogue export.
Search for some titles in the BeBanjo catalogue, using the simple search box in the top-right of any screen. On the results page, select the titles you want. The shortcut link (e.g., Select remaining N results?”) makes it easy to select all titles in one click.
Then click “Export” and BeBanjo will produce a spreadsheet with all the metadata for the selected titles. You will be prompted whether you want to export all the metadata available, or just a specific subset.
The second way to export data is called the schedule export, and gives you access to not only title metadata, but also to scheduling information.
Go to the Schedule page of BeBanjo, and filter it (by time period, by licensor, by platform…) to see the view you’re interested in. For instance, let’s see all titles going online in August on our iOS service.
Select the titles you’re interested in (again, the shortcut link makes it easy to select all titles in one click) and press “Export”. Again, the drop-down menus that appear then allow you to select what information you want to export. You may want all schedule and metadata information, or you may be interested in just a subset.
If you’ve chosen to download schedule information - as well as metadata - then the platform, put up and take down columns in the Excel schedule export give you just that.
Export and import work nicely together
When you download an Excel export, you’ll notice that the ID columns contain the IDs that BeBanjo automatically generated when creating the titles. So we can:
- Download an export.
- Make changes to it.
- Import it again.
BeBanjo will recognise that we are updating existing titles, not creating new ones. This is very useful. It allows you to quickly make complex updates in bulk, even if you don’t use External IDs.
Constraints for the Excel export and import feature
The Excel export and import are implemented using asynchronous jobs meaning that an export or import job runs in the background and may be queued if all of the available resources are busy; we therefore apply some constraints to the amount of data that be exported or imported to try and ensure a reliable experience for all of our clients.
An Excel document being imported is limited to 2,000 rows and only one simultaneous import per user is processed, i.e. subsequent imports will be queued for processing.
Also, if you need to create or update a large amount of content we recommend that you remove any unnecessary metadata columns from the Excel document (i.e. columns whose values you’re not making updates to).
A single Non-Linear or Schedule page export is limited to 32,000 rows and no more that 64,000 rows can be exported concurrently (across all users of the company).
- Bob starts a Non-Linear or Schedule page export of 30,000 rows, and
- Jane then starts a second Non-Linear or Schedule page export of 30,000 rows, and
- Mary then attempts to start a third Non-Linear or Schedule page export of 10,000 rows; this will result in an error being shown in the Non-Linear or Schedule page for Mary because the concurrent 64,000 row limit would be exceeded.
Note that these restrictions do not (yet) apply to exports from the Catalogue page.