3 Replies Latest reply on Jun 21, 2012 11:18 AM by Amit Dixit

    How do I go from web API in xml to table data that Tableau can use?

    Mullins Mcd

      Hi all.  This is a general question to all of those who are attempting to use web API data in order to make vizes.  I am trying to perform the following process

       

      1. Prepare a list of criteria , specifically a Zipcode for the areas I would like to obtain the data

      2. Plug these criteria into a URL based API query in an automated way,

      3. Obtain the XML based data from this query

      4. "Shred" this data into a table format that Tableau can use for a viz

       

      Any general answers or examples would help.  I am guessing I would need to use a database and programming language as an intermediary so if you could direct me to resources I would be happy to look it up from there.

       

      Thanks,

      Brandon

        • 1. Re: How do I go from web API in xml to table data that Tableau can use?
          Logan Riggs

          Brandon,

           

          Just off the top of my head a general approach could be:

          1. Get your list of zipcodes in a text file.  Are these manually determined?

          2. Write a script (Python for example) to read the list of zipcodes and then:

            a. For each zip code build the URL, for example you might be able to take a base URL like "http://service/getData?zipCode=", and append your zipcode to get the valid URL.

            b. Query the API using the URL and get the XML returned.  Your scripting language should have a library and examples of how to do this.

            c.  Parse the XML and write the data you want to an output file (csv for example).  This will vary by each scripting language, but they should all have the capability to do this.

          3.  Build a viz in Tableau by connecting to the csv output by your script.

           

          Then each time you want new data you would update the zipcode file, rerun the script, and refresh the data in Tableau.

           

          The trickiest part would be learning how to send an HTTP request and parse the XML response.  You should be able to find plenty of examples for each individual step (reading a text file, sending and HTTP request, parsing XML etc..) and then build the entire script from that.

          • 2. Re: How do I go from web API in xml to table data that Tableau can use?
            Dan Cory

            This is also a use for OData. You can write a proxy service that transforms the XML you get into XML structured for OData. Then anyone in your organization could connect to Tableau through this.

            • 3. Re: How do I go from web API in xml to table data that Tableau can use?
              Amit Dixit

              Dan/Forum,

               

              This would be a newbie question. How do I use OData to extract data from google analytics? What software/coding skills do I need to accomplish this?

               

              I see export to csv file combined with connecting to csv file as an option, but would love to hear if there are scripts that I can customize and embed in Tableau to extract data from google analytics.

               

              Thanks,

               

              Amit