Getting WebTuna data into Excel
Many customers like to extract WebTuna data to further process it with Microsoft Excel. There are a number of different ways to get data out of WebTuna and into Excel which we will discuss in more detail in this article.
CSV Export
CSV or Comma Separated Values is a simple text-based file format where the values are separated by commas. From within WebTuna you can output data to a CSV file and Excel can import a CSV file just by double clicking on it. Inside WebTuna the CSV export option appears on the Activity screen, and you can also find a number of CSV data exports on the Reports screen.
JSON Export (REST API)
JSON or JavaScript Object Notation is a text-based file format commonly used by API’s, as it is easy for humans to read and write, as well as being easy for machines to parse and generate. WebTuna has an API which we have written about in the past which will output JSON formatted data. In last week’s article we talked about how it can be used to extract large datasets of more than 10,000 records. The full documentation for the API can be found at http://docs.webtuna.com/display/docs/REST+API. The advantage to using the API to get data into Excel is data can be pulled in directly and refreshed as it changes.
The steps to set this up are listed below. We also have a short video below which you can follow.
- The only requirement for using this method is that you have the latest Excel 2016 updates applied since April 2017 and are using the Windows version (no Mac support currently).
- First you need to work out the URL of the API call you want to make. This is best done in your web browser so you can simply copy/paste it into Excel. See the example here.
- Open Excel
- On the Data tab, choose New Query, then From Other Sources, and then From Web.
- With the Basic option selected, paste in your WebTuna API URL from step 2 and click OK.
- Select Basic as the authentication method, and enter your WebTuna username into the User namefield and WebTuna API Key (you can find this on the password screen in my.webtuna.com but it is NOT the same as your UI password) into the Password field and then click Connect.
- Once the API returns the data, you need to convert it to a table with the To Table button.
- Then you need to split out the columns by clicking the icon with 2 arrows next to the Column1 heading.
- Finally click Close & Load button to get the data into your worksheet.
And don’t forget about PowerBI?
As an alternative to Excel why not try Microsoft PowerBI. It can be used for FREE and has many features which go beyond what is possible within Excel. WebTuna also has a FREE Content Pack to get you started. The getting started guide can be found here.
