Despite the error, Tableau is not having trouble connecting to the file. Rather, the syntax of your SQL is invalid, causing it to fail. For instance, if you try a simple SQL such as the following, it will work fine:
IF logic like that is not part of SQL. Generally, you'd use a CASE statement, but I tried that and it doesn't appear to work with Excel either. I'm not entirely sure how Tableau is allowing custom SQL against Excel, but my guess is that it does not support the full array of SQL functionality.
I should also note the following, according to Tableau (Connect to a Custom SQL Query - Tableau):
IMPORTANT: Starting in Tableau 2019.1, you will no longer be able to create a new Legacy Excel or Text connection. Existing workbooks created with these legacy connections will continue to work until 2019.2, when all support for legacy connections will be removed. See the Legacy Connection Alternatives document in Tableau Community for alternatives to using the legacy connection.
So, you should really consider not trying to use custom SQL with Excel. Something like what you're trying to do could be easily done within a calculated field instead.
1 of 1 people found this helpful
I didn't realize this Naveen! Good to know.
Still probably best not to use legacy connections to Excel considering the upcoming de-support.