Transferring data between excel and Iguana

In Iguana 6.0 if you import a channel you will find an Iguana-Excel repository with 4 channels.

These channels act as templates showing how you can get live feeds of data into Excel and also feed edited data back into Iguana.

See http://help.interfaceware.com/v6/excel-adapter

Attachments:

You must be logged in to view attached files.

This is a pivot table of log data – unfortunately it’s randomly generated ADT data so no interesting trends to be seen…

Attachments:

You must be logged in to view attached files.

This is the main tab used to get data. Notice how you can say what queries should be run and optionally pass parameters through to Iguana in the sheet.

The queries are set up in a table so you can add new rows. The value in the “Table” column determines the name of the worksheet to feed the data into. The code in spreadsheet will create this sheet if it does not exist and it will create an excel ‘table’ with the same name.

This makes it very convenient to build pivot tables from the data since you can reference the excel table you want to pivot off by name.

Attachments:

You must be logged in to view attached files.

And here’s some of the sample log data in the spreadsheet:

Attachments:

You must be logged in to view attached files.

This screen shot shows creating a new Pivot table, notice how I refer to the source table LogData in the dialog:

Attachments:

You must be logged in to view attached files.

This screen shot shows what you can do with pivot tables picking out some of the data:

Attachments:

You must be logged in to view attached files.

You will get a scary dialog from Excel when you open the spreadsheet like this:

Attachments:

You must be logged in to view attached files.

This is because the excel spreadsheet is using code written in Visual Basic for Applications (VBA).

If you have extreme trust issues you can leave the macros disabled and go and read the Visual Basic code before enabling them. I would recommend using the Windows editions of excel. The VBA code works on both Mac OS X and windows but the editing environment for the VBA portion is a lot nicer under windows.

As far as the reporting goes, the Pivot tables etc. are almost as good under Mac OS X as they are in windows.

I went to some rather extreme lengths to write Visual Basic code which is cross platform. I leverage the curl command line tool under OS X to drive the HTTP calls whereas under windows I make use of a COM control that can do HTTP calls. Each environment has different built in resources. When in Rome… I had to use just the bare bones of the VBA environment to make the code work on Mac and Windows.

The data format used between Iguana and Excel is deliberately primitive. Just a very simple comma delimited format with some easy to implement escaping of the special characters. This avoided the need to parse anything like JSON in VBA. There is a JSON parser that exists for VBA – but it uses the WIN32 API which is not portable. Using this simple delimited format enabled me to write less VBA code.

The advantage of that is there is less use of features that are likely to break in future versions of excel.

I did take the time to try write the VBA code efficiently. Read the code for more details.

Programming like it’s 1999….

😉

I had a couple of of questions from someone who was trying to use this channel to analyze their actual logs. The first question they had was how to control which channel to pull the log data from.

You can control this from the Excel spreadsheet. I’ll show with a few screen shots what to look at. First take a look this screen shot of the “GetData” tab:

Attachments:

You must be logged in to view attached files.

Look carefully at how I have put a rectangle around the Param1Name and Param1Value section. I have edited the Param1Value to have the value “Send rich HTML email” which is the name of channel I have on my Iguana instance. This is used to make a “GET” variable called “channel” with the value “Send rich HTML email”.

If you look carefully you can see that there are up to 3 GET parameters supported by this excel spreadsheet – this gives a means of passing arbitrary GET parameters to Iguana which can be used to alter how and what data we serve up. I’m going to put some more screenshots to show you exactly how this translates into Iguana’s back end.

Also notice how I have set the Enabled column to have only TRUE for this query to get the log data – I have set the other dummy query to FALSE to stop from getting ExampleData.

This means that only first query will run. The Table column is used by the VBA excel code to determine:

• The name of the worksheet to populate – and create it if it doesn’t exist already
• The name of the excel table in that worksheet to populate – and create if it doesn’t exist already

After I press the button to get data this is what I see in my LogData sheet:

Attachments:

You must be logged in to view attached files.

So how does the VBA code in the excel spread sheet communicate to Iguana? We’ll it’s building up a simple HTTP GET request which goes into Iguana. I’m going to show you a couple of cool tricks that make this really easy to understand.

After I have run my query one time I go into the translator for the channel.

By clicking on the middle of the Sample data control I get to see this:

Attachments:

You must be logged in to view attached files.

Now I can click on the Add From HTTP Log button and this is what I will see:

Attachments:

You must be logged in to view attached files.

You can see the GET request right there with the GET parameter called “channel” with the value “Send+rich+HTML+email”. HTTP requires that space characters are replaced by + characters which is why it is like that. By clicking on that request we are able to import that request into the translator instance and run the channel code with that request and see *exactly* how it is processed.

If you click on the edit sample data button – see the screen show and open up Iguana 6’s data editor we can have a look at exactly what Excel is sending to Iguana in terms of the HTTP request:

Attachments:

You must be logged in to view attached files.

Now we can navigate through the code of the channel to the exact point where we make the query to log API and see how it’s handled. The action is happening in the getlogdata.lua module. Here’s a screen shot:.

Attachments:

You must be logged in to view attached files.

If you look carefully you can see we are making a call to the Log API, which I documented in another post.

http://help.interfaceware.com/kb/988

By click on the table which is being passed into the net.http.get method you can see what parameters are being passed in on my instance:

Attachments:

You must be logged in to view attached files.

This is the XML we get from the Log API:

Attachments:

You must be logged in to view attached files.

We manipulate that into this to send back to excel in terms of an HTTP response:

Attachments:

You must be logged in to view attached files.

That final format is much easy for Excel to consume using the VBA code which is the excel spreadsheet.

Hopefully that gives some good in depth pointers as to how the plumbing of this example works – it’s intended to be a starting point for customers to pick up and run with. You can should be able to see how you could modify this example to do something closer to what you need.

Enjoy!

If you are curious about the VBA code but don’t have Iguana you can grab a copy of the spread sheet here:

https://github.com/interfaceware/iguana-excel/tree/master/Live_excel_feed-FromHTTP-tw4eiZPqDyii3H

Alrighty. Second version is always better 🙂

There are now two extra channels.

The first one Log Indexer creates a micro ‘data warehouse’ populating a SQLite database with just a single table.

The new Log Excel Server reports from that database running a SQL query against it.

The new version also has hyperlinks that you can see in one of the pivot tables to go back into the Iguana logs.

There are new buttons to clear the data and refresh the pivot tables – refreshing the pivot tables can be done from the standard Excel menus but it’s there is case people are not that familiar with excel.

Attachments:

You must be logged in to view attached files.

There are new buttons to clear the data and refresh the pivot tables – refreshing the pivot tables can be done from the standard Excel menus but it’s there is case people are not that familiar with excel.

Attachments:

You must be logged in to view attached files.

You can double click URLS to go to the actual message in the Iguana log:

Attachments:

You must be logged in to view attached files.

The back end code has been optimized to be faster and it handles boundary cases like zero rows of data and so on.

In the next step of the excel project I have implemented two new channels which show the ability to have an Excel spreadsheet which can send data *to* Iguana.

Why is this useful?

Well the the need we have is that we need to do some bulk updates to a few cloud based applications which lack convenient bulk editing facilities. They do however have good RESTful web based APIs that we can use to manipulate the data in those applications.

So for this general workflow…

Get data from apps from web apis –> put in SQL database –> Query into Excel

Edit data in excel –> upload to Iguana –> Iguana pushes data into web apis

To use it. Make sure you have Iguana 6.0.1 or above installed.

Add a channel and do an import from Iguana-Excel repo – you should see something like the attached screenshot:

Attachments:

You must be logged in to view attached files.

Get the Excel Importer and From Excel Importer channels. You’ll need to configure the channels so that the Excel Importer channel feeds to the From Excel Importer channel. Here’s a screenshot showing how to do that:

Attachments:

You must be logged in to view attached files.

Run the Excel Importer channel and connect to the webservice with your Iguana username and password. You will need to have administrator privilege on the Iguana server.

Then download the spreadsheet. Enable Macros and you should get a sheet like this:

Attachments:

You must be logged in to view attached files.

The Push Data to Iguana button will push the data contained in the “Accounts” table as shown here.

When Iguana receives the data it parses the uploaded data and turns it into JSON which is queued up.

I show screen shots of that table in Excel and the form of the HTTP request sent to Iguana.

Attachments:

You must be logged in to view attached files.

The code in the From Excel channel parses that comma delimited data in Iguana and then transformed each row into a JSON object and pushes then into the queue for the channel.

So in the logs we see the following screen shot:

The code in the From Excel channel parses that comma delimited data in Iguana and then transformed each row into a JSON object and pushes then into the queue for the channel.

So in the logs we see the following screen shot:

Attachments:

You must be logged in to view attached files.

The resulting JSON translations can then be parsed downstream by the translator code to do whatever transactional manipulations we need to do to them.

The beauty of this approach is we can keep the From Excel Importer channel switched off and edit the code in there till when we are ready to run through the transactions. Then just start the channel and off it can go.

Here’s a screenshot of processing one transaction:

Attachments:

You must be logged in to view attached files.

Hi Eliot,
The link you posted does not exist anymore. Do you have an updated link to the VBA code? Thanks!

It looks like all of the iFW-supplied repositories are off-line right now …

EDIT: Sorry, jumped the gun on this. Appears to be a local problem (i.e. mine, not iFW’s).

Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com