How To Use XML and CSV Files in a #Tableau Data Blend

Introduction

I was recently using Alteryx (www.alteryx.com) to blend an XML and a CSV file when a buddy at work asked me if I could do the same thing in Tableau. I told him that the answer was yes but that I would have to transform the XML file into a CSV file before using it in Tableau because as far as I know, Tableau is currently not capable of directly reading XML files.

He asked me to show him how I would do that, so I did it in a couple of minutes and decided it might make a good topic for a blog post.  I like to document things like this because they allow me to continue to think of new and creative ways of using Tableau.

In addition to showing him how I converted the XML file, I decided to talk to him about data blending.  I have used data blending many times over the years (and I’m still learning) and the more I do it, the more I realize the power (and potential pitfalls) of the method.

Sometimes I choose to do a blend and sometimes I choose to add fields to a database to avoid doing a blend. You gain these insights the more you use the technique and experience the highs and lows of the methodology.  Also, since I like to write about a variety of techniques for new Tableau users, I felt like this would be a good topic.

When you are new to Tableau, doing your first data blend can be a bit mysterious. With this post, I hope that I can remove some of the black magic that happens when you try to do data blending for the first time or two.

That is how this post originated and so if you are interested in the topic, you will have to watch a couple of videos that I include below. I am not attempting to cover data blending in infinite detail, but I do talk about a few subtleties of blending files including: (1) blending files located in the same directory, (2) blending files located in different directories, (3) understanding how to use the linking icon that might show up when you don’t quite know how to complete the linkage of two files, (4) blending fields that are not named the same, and a few other thoughts about blending in general.


If you like this article and would like to see more of what I write, please subscribe to my blog by taking 5 seconds to enter your email address below. It is free and it motivates me to continue writing, so thanks!

Enter your email address to follow this blog and receive notifications of new posts by email.


Transforming an XML file into a CSV file

The first part of the Video 1 demonstrates how I transform an XML file into a CSV file for analysis in Tableau. The technique I use is a manual operation in my favorite editor (www.vedit.com) but it is easy to do.  

I could do this in fewer steps and I could use other tools to do this, but when my buddy asked me how to do this, this is just what I showed him. It isn’t an optimal procedure but it gets the job done just fine because the structure of this XML file is very simple.

For more complex XML files, I have additional techniques I could use but that is beyond the scope of this post.  The second part of the video shows how I do the blending of two csv files that are (1) in different directories, with different names for the linking fields, and (2) in the same directory, with different names for the linking field.


Additional Thoughts on Data Blending

Once I completed Video 1, I thought that I should simplify the approach for people just starting to learn how to use data blending in Tableau, so I moved both files into one directory and used the same name for the linking field.  These operations are shown in Video 2.  This is the simplest way to get a data blend complete using CSV files.


 

4 thoughts on “How To Use XML and CSV Files in a #Tableau Data Blend

  1. Hi
    I really like your posts, they are really helpful.
    You said ” I have used data blending many times over the years (and I’m still learning) and the more I do it, the more I realize the power (and potential pitfalls) of the method” I have just started to use Tableau, which are potential pitfalls of blending?

    • Hi Krete,

      Thanks for the comment and you have asked a very good question. I am going to write a short post in the next week to answer your question. Basically, if you don’t understand how a left-join, inner-join, and right-joins function, you can end up dropping data from your analysis without knowing that it is happening. I’ll give an example in the post.

      Thanks,

      Ken

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