March 13, 2014

The Greatest Tableau Tip EVER: Exporting CSV made simple!

We’ve all heard this question before: How can I export a CSV in Tableau?  To be honest, it’s quite the pain and way more difficult than it should be.  There have always been a few options.

  1. Users can click on a specific sheet on a dashboard and then export that via the tiny button on the toolbar, but that has a few of its own problems: (1) You may not want to show the toolbar therefore making the export impossible, (2) People have to be trained to know exactly where to click to get it just right, and (3) You have no control over the output of the CSV.
  2. You can export a CSV using Tabcmd, but that’s not useful for the average dashboard consumer.
  3. You can add .csv to the end of the URL like http://[Tableau Server Location]/views/[Workbook Name]/[View Name].csv.  But again, you never know what that output is going to look like.

Yesterday I learned an incredibly valuable trick that would make option 3 (adding .csv to the URL) export exactly the CSV you want.  Let’s look at an example.

 

On this dashboard, notice how I added an Image object (I used an Excel icon) on the dashboard.  I’ve floated it to the upper right and made it small.  All the user has to do is click on that icon and they get a nice CSV.  Go ahead, try it!

Did you try it?  If you did, and you opened the CSV, you may have noticed this looks remarkably like the data that you would hope to get if you exported the data for the line chart.  But I didn’t export the line chart at all.  Here’s the trick.

  1. Add the image icon on the dashboard and place it wherever you like.
  2. Add a URL link to the image.  In the dashboard above, the URL is http://public.tableausoftware.com/views/ExporttoCSV/Dashboard.csv.

    This is where the magic sauce happens. When you add .csv to the end of a Tableau URL, Tableau will export the first sheet on the dashboard alphabetically.  Yes, it’s that simple!  And it’s totally undocumented.  Special thanks to the one and only Tableau Jedi Mark Rueter for this tip!  But note that Tableau orders upper case before lower case. 
  3. What I did was float a sheet named AExport onto the dashboard.  I changed the height to 1 and made everything white and transparent and chose Fit Entire View so that it would be inconspicuous.  I had to name it with a capital A so that it would be the first sheet alphabetically on the dashboard.

    image

    The AExport worksheet started off like this:

    image

    Basically, you can put anything you want on this sheet.  I then changed the transparency to 0% on the Color shelf, changed the default worksheet color to white, removed the row banding and removed the row and column dividers.  The worksheet now looks like this:

    image

That’s all there is to it!  To recap:

  1. Create a worksheet that you want to export.
  2. Remove all of the formatting to make it look invisible.
  3. Be sure to give it a name that makes it first alphabetically on the dashboard.
  4. Place the worksheet on the dashboard, float it, make it fit the entire view, make it really small, move it somewhere inconspicuous.
  5. Add an image onto the dashboard, float it and add a URL to it that is the URL for the dashboard with .csv on the end.

This is a game changer!!  Download the sample workbook here.

42 comments:

  1. Well that is rather neat isnt it

    ReplyDelete
  2. That is a game changer - fantastic! Thanks Andy.

    ReplyDelete
  3. Beautifull solutions are always simpel! Thanks for posting

    ReplyDelete
  4. Replies
    1. I just tried it with javascript and it works well. In IE and Firefox, I was able to call upon the download window and in Chrome a popup window which downloads the worksheet. Here's the javascript I used:

      function downloadToExcel() {
      var popupWindow = window.open('http://URLofTableauServer/views/WorkbookNameOnServer/DashboardName.csv','','height=100, width = 425, location=no');
      popupWindow.document.title = 'Once the Excel file has been downloaded, close this window.';
      if (window.focus) {newwindow.focus()
      return false;
      }
      }

      On the HTML side, I simply added a 'button' to a list that called the downloadToExcel() function.

      Delete
    2. Amazing Andy! Thanks for sharing!!

      Delete
  5. Where has this tip been all my life?

    Thanks!
    @paulbanoub

    ReplyDelete
  6. Hi Andy! This is awesome - I've been looking for a way to make a "user friendly" export while still hiding the source view. One question though - it doesn't look like any filters the user applies are automatically passed. Do you know how I can set filters dynamically on an image url? Or am I better off using the URL action in the dashboard using a sheet that has the image? Thanks in advance!

    Sarah

    ReplyDelete
    Replies
    1. Sarah, you can add filters to the URL. Here's how - http://vizwiz.blogspot.com/2012/11/tableau-tip-passing-filters-in-url-to.html

      Delete
    2. Hi!

      Did you find a way to pass user applied selections dynamically to the link? IE there was possible to filter on states, and the user selected Califorina, then the export should contain only information from California.

      Sigmund

      Delete
    3. Sigmund, I have not looked into this case explicitly, but you should be able to add the filters to the URL.

      Delete
    4. Hey Andy, similar to Sigmund, I wanted to add dynamic filtering to the export. In other words, I have multiple users accessing the dashboard, and some want to filter based on their respective location, group, etc... It wouldn't be efficent to imbed the filters into the url for several hundred people as they will have multiple possibilities. Is it possible to pass quick filter selections into the URL so that the export shows the fitered options?

      Thanks,

      Rod

      Delete
    5. Rod,

      I don't see any reason why this wouldn't work, but I haven't tested it either. Look at Tableau's documentation for how to pass options to the URL and see if it works.

      Delete
  7. Hey Andy, this is fantastic! Do you know, if you could adapt the URL exporting to excel? ".xlsx." didnt work for me...

    ReplyDelete
    Replies
    1. Excel format is not supported, but that shouldn't be a hurdle. The cvs will open in Excel.

      Delete
  8. This is great. However is the columns are not coming out in the order that I had hoped. Is there a way of enforcing the order that the columns are output?

    ReplyDelete
  9. I do not know what happens under the covers when you export. How are they coming out for you? Alphabetical?

    ReplyDelete
  10. Order seems a little arbitrary.
    Here are the first few columns:
    Dept, 1st Opened, Item, Resolved,Queue,Region,....
    I re-ordered the columns in the worksheet, but the columns come out in the same order :-(

    ReplyDelete
    Replies
    1. Hi - I am having the same issue. Any advice?

      Delete
  11. Great idea, but doesn't work for me due to URL hashtag numbering. (http://kb.tableausoftware.com/articles/issue/view-url-hash-fragment) Unfortunately, this forces the data export (via the Excel icon URL, ie, the "base" URL without the hashtag) to remain exactly as it was published. User changes are not applied to the base URL, but to the hashtag URL, if that makes sense. Any ideas how to work around this limitation? (Note: I am using Tableau server to render my workbooks.)

    ReplyDelete
  12. Has anyone been able to export "filtered" CSV data by this method? (That is the CSV export dataset reflects changes the user has made to the hidden view via actions and quickfilters.) Try as I might, I cannot. I am using Tableau Server.

    ReplyDelete
    Replies
    1. If you pass the filters into the URL, then that would get you what you're looking for.

      Delete
  13. I need this. Quite badly. Jumping right into it I ran into the same problem as Shawn - the #n was interfering.
    Fortunately, this isn't really a problem - the #n was implemented in Tableau Server 8.1 as a session multi-view feature, and documented here: http://kb.tableausoftware.com/articles/issue/view-url-hash-fragment
    and here: http://kb.tableausoftware.com/articles/issue/adding-parameters-to-url-ineffective-after-upgrade
    Drop the #n from the UR, append ".csv" and everything's peachy.

    Now if I can only figure out how to get the crosstab downloaded through a similar route - I'm running into numeric precision problems and would like to see if a sideways approach would work.

    ReplyDelete
    Replies
    1. Thanks for the clarifications and feedback Chris!!

      Delete
    2. Not exactly this post's topic, but related: when copying or exporting a crosstab Tableau always honors the view-specific number formatting.

      According to Tableau this is by design - and thanks to them for responding right away to my asking about this.

      I understand the "by design" position, and in this case, like many others e.g. copying/pasting data from HTML tables, the only real response is: "well, the design is not good and a redesign is called for".
      If nothing else copying and exporting data and crosstabs should be symmetrical in how the the data is rendered at the destination.

      Delete
  14. Do you guys know if there is a URL to get the Crosstab csv?

    ReplyDelete
    Replies
    1. Not that I'm aware of. However, you could design the worksheet you want to export to look the way you want it to export and that should work I believe.

      Delete
  15. Great stuff! Are there any known ways to select which worksheet would be exported? Ie: for cases where each sheet on a dashboard has different data fields, this might not suffice for an end user.

    ReplyDelete
    Replies
    1. This technique only works for a single worksheet. If you want to export a specific worksheet on the dashboard, you need to first click on the sheet, then click on the export button on the Tableau toolbar. Here's a blog post with an example - http://vizwiz.blogspot.com/2012/09/tableau-tip-exporting-data-from.html

      Delete
  16. This does not seem to export the results of applying quick filters. How do you add quick filters to URL dynamically?

    ReplyDelete
    Replies
    1. Ravindra you can add filters to the URL. Here's how - http://vizwiz.blogspot.com/2012/11/tableau-tip-passing-filters-in-url-to.html

      Delete
    2. Hi Andy,

      My reports have a date filter on dashboard, so user can select a data range for the data. When i export the dashboard using .csv extension it does not reflect the date selection. How do i add this dynamic filter to the export url?

      Delete
    3. Ravindra, if you have a range date filter, you could create a calculated field that captures the min date and another that captures the max date, and then pass those fields in the URL.

      Delete
  17. As far as I'm aware, you don't have any control over the order of the columns on export. But hey, at least you know how to export it now.

    ReplyDelete
  18. Hi Andy,

    thanks for this interesting post - but am I right that this method don't export the “true crosstab” format that you build up in you AExport worksheet? Because there you have measures as columns - the export will end up as "raw data" format where all measures end up in one column (good for being further used in excel pivot but bad for senior management ;-)).
    This is probably the same issue as with exporting a csv while using tabcmd (for example see http://community.tableausoftware.com/ideas/1987). Do you now any way to get the same view as in the underlying sheet?

    Regards

    ReplyDelete
    Replies
    1. Roberto, the CSV can easily be imported into Excel and formatted nicely, but you are correct in that Tableau doesn't export it exactly as you design it. The CSV export via this method will give you the same result as the tabcmd option. There's currently no way to get the same view as the underlying sheet that I know of.

      Delete
  19. I followed all the steps indicated, I was able to download the csv file, but I am facing a weird issue.
    My csv download works correctly if the data to be downloaded has approx 900 records, if it's more than that, all I get is a blank csv file.
    Has anyone else faced this, or knows any workaround.

    ReplyDelete
    Replies
    1. I've never seen that issue before. I always gets all of the data.

      Delete
  20. We can export any sheet into CSV file format. http://localhost/views/ followed by Workbook name, dashboard name & Worksheet name.
    http://localhost/views/workbookname/dashboardname/worksheetname.csv

    Example:
    http://localhost/views/Sales/GrowthofWalmart/WMMap.csv

    ReplyDelete