Importing Data into an Interview : Excel Example

Importing Data into an Interview : Excel Example

Readers will remember a while ago I explained briefly how to use Microsoft Excel to act as a Connection Datasource – in this overview article, followed by this one in a little more detail. Now we look at another challenge : Importing Data into an Interview.

Well, here comes another example of the ubiquitous nature of Microsoft Excel. The customer requirement was as follows:

Using a simple mechanism, let the user upload an existing Excel spreadsheet into the Interview. Parse the spreadsheet, read the data in it, create corresponding rows in an Entity. Let the user review the data but do not require any new data entry. There may be up to 250 rows of data to import. So how do you go about about Importing Data into an Interview?

So how can we face up to a challenge like that? We need:

  • An upload that isn’t a standard File Upload Group
  • A parsing mechanism to read Excel and extract the data in a given tab, or wherever
  • A custom Entity Collect to handle the data import / create the rows in a Screen

The shopping list above isn’t that long.

The File Upload is essentially an HTML 5 component to let the user select a file on their computer. We cannot access an arbitrary local path from JavaScript, so we need the user to point to the file they want to upload.

There are a number of JavaScript-based Excel parsers, including the excellent SheetJS js-xlsx which we used. It is capable of converting to and from Excel, which is no easy task when you consider that an Excel file is basically a Zip file with a bunch of complicated stuff inside it. The library can convert to HTML, CSV and magically (for our requirement) JSON. Awesome!

Plus, in a previous post we’ve also looked at the (large amount of) work required to build a Custom Entity Collect Extension. In fact when I was writing that article I was thinking, for goodness sake Richard, when do you think you will actually need to go to the trouble of building that Entity Collect Extension? Well, I’ve finally found a use for it – Importing Data into an Interview!

We need an Entity Collect Extension since we need some way of getting the Excel data into the Entity Collect, which ultimately means we need to do some work behind the scenes between the import of the data and the display of the Entity Collect. We need to rewire the Entity Collect temporarily so that it sucks our Excel data up, before we show it to the user so they can examine the results.

For the purposes of a raw demo, I unplugged all the other functionality (delete buttons, add buttons, etc.) and just concentrated on getting the data into the Entity Collect. There are how ever a few caveats. Once you get into the larger imports, at least in the Debugger, you can expect to see “concurrent record editing” errors. I’m trying to find out what the limit is exactly. But up to a few hundred I think it’s OK.

So let’s look at the items in turn.

File Upload and Data Load

					//console.log("Starting customInput Mount");
					var div = document.createElement("input"); = "myFile";
					div.type = "File";
					//div.value = control.getValue();
					function handleFile(e) {
						var files =,
						f = files[0];
						var reader = new FileReader();
						reader.onload = function (e) {
							var data = new Uint8Array(;
							//console.log("In Change");
							var workbook =, {
									type: 'array'

							worksheet = workbook.Sheets["YOURWORKSHEET"];
							jsonoutput = XLSX.utils.sheet_to_json(worksheet, {
						raw: true, header : 1
						//console.log("Read " + jsonoutput );
						var completepath = $(':file').val();
						interview.setInputValue("rest_filenameandpath", completepath);
				var filedialog = document.getElementById("myFile");
					filedialog.addEventListener('change', handleFile, false);
					var completepath = $(':file').val();
						//console.log("Hello " + completepath);
						//console.log("Ending customInput Mount");

Assuming you have a custom Input framework as your starting point, the above code will be in the mount. This will build an HTML5 file upload control, and attach an event handler. The code regarding Excel depends upon xlsx.full.min.js being in the resources directory. But that’s it. You’ve loaded the Excel file into a JSON object.

Entity Collect

The next step is to include a Custom Entity Collect in your project, and use the jsonoutput object (which you just created from the imported file above) in the mount of the Entity Collect to load the JSON into the Entity Collect. The following is an extract from the mount code

var numEntities = Object.size(jsonoutput);
					// Remove header row if the file has one
					// load records into the Entity Collect

					if (control.getRows() == 0) {
						for (j = 0; j < numEntities - 1; j++) {
							var mycurrentrecords = control.getRows();

The end result is something like this:

Importing Data into an Interview

The File is loaded into the Entity Collect, and the contents displayed to the user. In my case I unhooked all the code related to modification (the onchange stuff from the original idea) and removed the add / delete buttons, since it was designed to just allow the user to see the loaded result, not modify it.

Importing Data into an Interview

If you want to have a look at the project, just download the very basic example here.

Richard Napier

Author: Richard Napier

Richard Napier joined Siebel Systems in 1999 and took up the role of managing the nascent Siebel University in Southern Europe. He subsequently was Director of Business Development and Education for InFact Group (now part of Business & Decisions) for 8 years. He now runs his Consulting and mentoring company, On Demand Consulting & Education Ltd and has run ODCE since 2010. Owner of the OPA Hub, he also is Co-Founder of the Siebel Hub.

3 thoughts on “Importing Data into an Interview : Excel Example

  1. Hi Richard,

    The example you have here is very close to a functionality that we want to replicate in one of our projects. We are making an API call to an external system and require to present the data in an entity container that may have one or many instances. I have tried to download the project that you have for this example, but was not able to open it from the email sent. Is there another way to view the code for this.

    Krestin Wood

    1. Hi Krestin. Thanks for getting in touch. Can you tell me what sort of issue you have been having – were you able to get to the ZIP file? Is it a version problem, or a more basic “cannot download” problem. Let me know and I will sort it out.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Logo by Southpaw Projects LLC