Connecting Tableau to Web Data (part 1)

Tableau

I am starting to use Tableau more at work and find it to be generally very good.

We have a reasonably large code base built in C#. More and more we have been developing our in-house tools as Web apps within Asp.Net MVC and Asp.Net Web API. In general we use JSON for passing data from the server to client apps. This led onto the issue I wanted to solve, how best to get web data into Tableau.

It is very good at connecting to table based data (either in databases or files), I see from the keynote at the Tableau Customers Conference they are working on producing a way to allow developers to write their own ‘connectors’ (and this would achieve exactly what I wanted). I believe this should be released in v9 sometime next year.

In the mean time, they have a Data Extract API. This allows you to create your own Tableau Data Extract file (.tde). Unfortunately from my own perspective the API is not available in .Net, but they have a Python version. Time to learn a little Python then!

My first experiment was to download a data file from Yahoo Finance. They have a simple URL which allows you to request a CSV file. Tableau could directly import this file, but for the moment the goal is to create a TDE file directly.

Nothing very complicated in this:

  1. Create the TDE File and create:
    tdeFile = tde.Extract(fileName)
    tableDef = tde.TableDefinition()
    
  2. Add the columns to the tableDef and then create the table:
    for col in range(len(csvParts)):
        if "date" in csvParts[col].lower():
            tableDef.addColumn(csvParts[col], tde.Type.DATE)
        elif csvParts[col].lower() == "volume":
            tableDef.addColumn(csvParts[col], tde.Type.INTEGER)
        else:
            tableDef.addColumn(csvParts[col], tde.Type.DOUBLE)
    table = tdeFile.addTable('Extract', tableDef)
    
  3. Load up the data row by row:
    row = tde.Row(tableDef)
    for ii in range(tableDef.getColumnCount()):
        csvVal = csvParts[ii]
        type = tableDef.getColumnType(ii)
        if (type == tde.Type.CHAR_STRING):
            row.setCharString(ii, csvVal)
        elif (type == tde.Type.INTEGER):
            row.setInteger(ii, int(csvVal))
        elif (type == tde.Type.DOUBLE):
            row.setDouble(ii, float(csvVal))
        elif (type == tde.Type.DATE):
            row.setDate(ii, int(csvVal[0:4]), int(csvVal[5:7]), int(csvVal[8:10]))
    table.insert(row)
    
  4. Finally close the TDE File:
    tdeFile.close()
    

My python script can be downloaded here

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s