Custom SQL in Tableau 8.2

Data

Custom SQL in Tableau 8.2

by Katie Wagner

The improvements of the Visual Data Window in Tableau 8.2 gave a breath of fresh air to the Tableau community. So far, the new design has been intuitive and user friendly. However, when I went searching for the Custom SQL option when connecting to an Excel file in 8.2, I was stumped. I searched frantically through Main Menu, looking in “File” and “Data” with no such luck. Surely Tableau hadn’t REMOVED functionality from the product, right?

Where is Custom SQL?

 

Fear Not!

 

Custom SQL in 8.2 is hidden in Tableau’s legacy connector. The process is painless and I’ve stepped through it for you in the following views:

  1. Starting in Tableau’s Home button in the workspace controls, select Connect to Data.
    Connect to Data

     

  2. Choose a file format and navigate to the file’s saved location.
    Excel
     
  3. Open the file with Tableau’s legacy connection by selecting the drop-down arrow and then Open with Legacy Connection.

    Open with Legacy Connection
     

  4. The “New Custom SQL” option becomes available. Simply double-click on New Custom SQL to open the Edit Custom SQL dialog box.

    New Custom SQL
     

  5. Type or copy + paste your query into the Edit Custom SQL dialog box and select “OK”.

    Custom SQL Query
     

  6. Choose whether or not the first row has field names in it from the Info icon.
    Edit Custom SQL
     
  7. If you need to reopen the Edit Custom SQL dialog box to make changes to the query, simply choose the Edit icon that resembles a pen.
    Edit

A Few Things to Note

Just a few things to note about Custom SQL and Tableau’s legacy connector:

  • When using Tableau’s legacy connection, you should consider Microsoft Jet Database Engine’s limitations.
    • Does not work with password-protected files (Ex. Microsoft SharePoint)
    • COUNTD(), MEDIAN(), and REPLACE() are not available
    • Limits table width to 255 columns
    • PC only – does not work with Mac
  • When determining data types for columns in Excel, Tableau’s legacy connector only looks at the first 8 rows and determines an unchangeable data type. The default connection in version 8.2 makes its decision from 95% of the top 10,000 rows. For text files in the default 8.2 connector, the data type of a column is determined by the first 1024 rows instead of the legacy connection which only looks at 25. For more information visit Tableau’s Knowledge Base.
  • Custom SQL impacts the performance of some workbooks because the query is issued to the database inside of a subquery. Read more here.

Troubleshooting data connections can sometimes be complex and time consuming, or as simple as a quick Google search. For any additional questions, feel free to reach out to our team here at InterWorks. We wrote the book on Tableau and are always up for a challenge!

More About the Author

Katie Wagner

Analytics Consultant | Training Lead
Preppin’ Data Project: Week 1 Note: A big thank you to Carl Allchin and Jonathan Allenby for initiating the Preppin’ Data project for our community. Hunker down, ...
Split and Pivot Your Data with Tableau Prep With the release of Tableau Prep in April 2018, analysts have been given an incredibly valuable tool for their analytical kit. ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072