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?
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:
- Starting in Tableau’s Home button in the workspace controls, select Connect to Data.
- Choose a file format and navigate to the file’s saved location.
-
Open the file with Tableau’s legacy connection by selecting the drop-down arrow and then Open with Legacy Connection.
-
The “New Custom SQL” option becomes available. Simply double-click on New Custom SQL to open the Edit Custom SQL dialog box.
-
Type or copy + paste your query into the Edit Custom SQL dialog box and select “OK”.
- Choose whether or not the first row has field names in it from the Info icon.
- 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.
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!
