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.

Interested in JavaScript Extensions – here are some more articles you might find interesting.

Richard Napier

Author: Richard Napier

After 8 years in case management and ERP software roles, 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 Intelligent Advisor IT Consulting OÜ. Owner of, he also is Co-Founder of the Siebel Hub.

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

  2. Hi Rich,

    I downloaded this example. I think you are loading the data into Entity in next screen. Is it possible to load the data on the same screen?

    For Ex, I am trying to search using employer id(global) and call REST api and once I have the data, need to load the data into entity. pon How can I call custom entity collector ureceiving the data from REST API ? I did lot of research but can’t proceed further.
    Also, I don’t want to display entity data with same headers repetitively. What I am looking for is a simple Table with the data. How can I achieve this ?

    Any help would be greatly appreciated.


    1. Hi.
      To cut a long story short: if I can summarize briefly, what you are looking for is the ability to insert instances into an entity, from (for example) a label control or another control. You cannot currently do that, since there is no “addNewRow” method availble in any other control type (only in entity collect). “setInputValue” requires you to know the entity instance id which you will not be able to use since you want to create a new instance, not update an existing one. So you will need to use one control for the REST API, and an entity collect control for the creating of instances (since there you will be able to use addNewRow). That’s why the Excel example has 2 different elements – the first to load the array of data and then the entity collect to create the instances.

      1. Thank you for your response Rich!
        I am not sure if I followed you correctly. I think my use case also I have two elements but on the same screen.

        My use case is ..
        I have Employer id which is global entity attribute and one search button.

        When I enter value and click search button, I need to fetch data from REST api and display in a table format on the same screen. Can I do this with Custom Entity extension?

        I tried this use case with this excel example, but no luck.

        1. Hi Narendra

          To continue, you will need two controls, one for the REST search and one for entity collect. Only the entity collect has access to the “addNewRows” method that you are looking for. Use the update of the entity collect to catch when your global attribute is changed and then populate the entity. I have sent you some example code and hopefully you will go from there.


Leave a Reply

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

Intelligent Advisor IT Consulting Serving Customers Worldwide
Hide picture