Export from Excel to Iguana

Introduction

The Excel Adapter Modules can be used to import and export data to and from Iguana. The two channels Export 1: Export From Excel and Export 2: Process Export Data work together to demonstrate how export data from Excel to Iguana.

This tool was built to meet the need we had to do some mass updates to data we had in our salesforce.com CRM system. Being able to edit the data in Excel and then upload it to Iguana to use the salesforce.com RESTful web services was a convenient way to do it especially since it allows a non technical team member to handle the data in Excel.

This demonstration requires two channels:

  • Export 1: Export From Excel: this channel serves up the Excel spreadsheet with the export logic. It receives the webservice call from Excel, reformats and queues each row of uploaded data as a JSON object. 
  • Export 2: Process Export Data: this channel receives data from the Export 1: Export From Excel channel. This is not much code in it. It is where code could be written to process each row of data which is encoded in JSON.

This example could have been implemented with just one channel. The benefit of breaking things apart into two channels is that it makes it possible to stop and start the Export 1: Export From Excel channel independently of the Export 2: Process Export Data channel. This means one can check the data before going ahead and processing it. It also makes the code in the Export 2: Process Export Data channel much simpler.

For best results we recommend you use the Office 365 version of Excel on Windows or OS X, although many customers have had success with the previous couple of versions of Excel. The last two versions on Windows are quite reasonable (Excel 2013 on Mac treats dates a little oddly) I would recommend only using the latest version of Excel on Mac.

Some hospital networks have very locked down security settings with Microsoft Office. It may be better to try this on a home computer where Excel is installed before trying it within a work context.

If you have any questions please contact us at support@interfaceware.com

Note: The Excel screenshots on this page were made using earlier versions of Excel and Windows, and will look a bit different from the latest releases — but the same principles still apply.

Using the Channels [top]

  • Make sure you have a working copy of Microsoft Excel installed
  • Import the Export 1: Export From Excel and the Export 2: Process Export Data channels from the Builtin: Iguana Excel repository
  • Make sure the Export 1: Export From Excel channel is running
  • Stop the Export 2: Process Export Data channel
  • Click on the URL for the Export 1: Export From Excel channel, it should be something like http://localhost:6544/export/
  • You will be asked to login, use a valid Iguana username and password
  • After successfully logging in you should see something like:
  • Click on the link to Download a sample spreadsheet, then open the downloaded spreadsheet
  • It’s super important to Enable macros! This is required because the communication between Excel and Iguana is implemented using VBA (Visual Basic for Applications) macros within Excel.
    Different versions of Excel will display slightly different alarming dialogs warning you that the Excel spreadsheet has macros like this:
    Screen Shot 2016-05-11 at 4.46.23 PM
    Note: While a malicious programmer can write malware in this language, in this case you can trust the code.
  • Once the spreadsheet is open go to the GetData tab
  • Click on the Send Data to Iguana button.
  • Enter the Iguana password for the user shown in field C3 into the dialog box
    Note: Older versions of Excel may open the dialog in a odd place off the screen so look around if you do not see it.
  • This will send the data from the Excel table Accounts in the Accounts tab to Iguana
  • The status column in the Export table should be green and have a message with the current timestamp and a message like Thanks for your data, … like so:

    Tip: This spreadsheet is very similar to the one used for the Import Logs into Excel from Iguana example. We simply greyed-out the buttons we are not using, and set the enabled field to FALSE for the associated Excel Table. The reason we did this was to highlight how we used different Excel Tables (Query and Export) for the two channels.

    You can use the spreadsheet with both examples, simply change Enabled to TRUE.

  • Click on the Accounts tab to see the Accounts table with the data that was pushed to Iguana:
  • Go to the Dashboard of Iguana. You should see recent activity in the Last Activity column for the Export 1: Export From Excel channel and 14 queued transactions in the Export 2: Process Export Data channel, and hovering over the Last Activity link will show a summary of historical activity:
  • Click on Last Activity Link for the Export 1: Export From Excel to go to the logs.
  • Scroll down until you see the log entry for the HTTP transaction which came from Excel that had the data. This has the Excel data in comma delimited format: 
    POST /export/upload HTTP/1.1
Host: localhost:6544
Authorization: Basic YWRtaW46cGFzc3dvcmQ=
User-Agent: curl/7.43.0
Accept: */*
Content-Length: 314
Content-Type: application/x-www-form-urlencoded

Id,Name,Manager
233,Calypso Inc,Paul
232,Mercy,Jeff
322,Java Joes,Fred
2222,Smithsonian,Mary
323,Andersons,Paul
32,Beehive Inc.,Fred
23,Juju Corp,Paul
2323,Life Sciences,Mary
23,Delphi,Reagan
23,Microsoft Services,Art
23,PwC,Harold
23232,Medlink Inc.,Paul
32323,Cow Sciences,Jeff
2332,Caledonia Enterprises,Reagan
  • Now browse through the logs. For every row in the comma delimited data, the Export 1: Export From Excel channel has transformed row into a JSON object. The names of the fields correspond to the original column names which came from Excel, namely Id, Manager and Name: 
    {
   "Id": "23",
   "Manager": "Art",
   "Name": "Microsoft Services"
}
  • Now open up the translator editor for the To Translator in the the Export 2: Process Export Data channel. The JSON data for the rows comes into the main function which can be parsed. From here you can use all the tools of Iguana to process the data:

If you wanted to change the data set being sent to Iguana from Excel it can be done without any code changes.

  • Go to the Accounts tab in the spreadsheet in Excel.
  • Right click with the mouse on any cell in the 3rd column with the heading Manager
  • From the context menu select Insert->Table Columns to the Right:
    Screen Shot 2016-05-13 at 2.50.42 PM
  • In the newly created column, change the default title from Column1 to Phone
  • Enter a few phone numbers into the column so that you have something like this:
    Screen Shot 2016-05-13 at 2.55.27 PM
  • Go back to the GetData tab and click the Push Data to Iguana button again.
  • Go to the Iguana log viewer. You should see new messages of JSON with the extra Phone field just like this: 
    {
   "Id": "23",
   "Manager": "Art",
   "Name": "Microsoft Services",
   "Phone": "32323233"
}

So as you can see it is very easy to upload any arbitrary dataset you wish.

How it works [top]

The code is implemented using Lua on the Iguana side and VBA (Visual Basic for Applications) code within Excel. The article Import Logs into Excel from Iguana has more detail about both the Iguana and Excel implementation.

The VBA code is designed to avoid the need to change it for standard use. The configuration of the code is done via an Excel table called Export which is defined in the GetData tab. When we click Push Data to Iguana the VBA code iterates through the Export table. There are four columns defined in the table:

  • Enabled: Unless this is set to TRUE then the row will be ignored. This is useful for disabling exporting tables without needing to delete the row.
  • Status: This is updated by the VBA code to give an indication of whether or not it was successful uploading the data.
  • Table: This is the name of tab and the Excel table that the data is being pulled from. An empty tab and table of the name given will be created if they do not already exist – this can be a helpful technique.
  • URL: This is the address of the webservice URL in Iguana to upload the data to.

Here is a screenshot of the table which shows two exports, one from an Accounts table and another from a Life table:

The VBA code reads the data from the given tables and formats it into an HTTP POST in the comma delimited format shown already.

On the Iguana side in the Export 1: Export From Excel from HTTP translator instance we can read the code which processes the inbound data. There is a handler registered for upload which we can see in the main module:

The upload handler is very simple. It makes use of the shared excel module in Iguana to parse the delimited data, then uses another helper function called excel.package to generate the convenient JSON based rendering of each row of the uploaded data:

So from here it should be clear how you can:

  1. Upload tabular data from Excel tables to Iguana.
  2. Edit and extend those tables.
  3. Process each row of data in Iguana.

Let us know if you have any questions about using this adapter.

More information [top]

Tagged: