4 Replies Latest reply on Dec 13, 2013 12:36 PM by Brian Smith

    What is the max number of sheets that can be in an Excel-spreadsheet data source?

    Brian Smith

      I'm building a workbook around a series of Medicare data about hospitals. The Medicare-provided data is in a large number of .csv files, dozens in all -- and the .csv files do not have the same format. So I have pulled a number of the .csv files into an Excel spreadsheet, each .csv as its own sheet in the Excel workbook. I then used Custom SQL to pull in the appropriate metric from each sheet and used UNION commands to concatenate all the results together as a single dataset. So it ends up looking like this:

       

      SELECT
        [HVBP_Patient_Exp$].[Provider Number] AS [Provider Number],
        'Overall Rating of Hospital Dimension Score' AS [Dimension Name],
        'Patient Experience' AS [Score Type],
        [HVBP_Patient_Exp$].[Overall Rating of Hospital Dimension Score] AS [Dimension Score]
      FROM [HVBP_Patient_Exp$]

      UNION

      SELECT
        [HVBP_AMI$].[Provider Number] AS [Provider Number],
        'AMI-7a Measure Score' AS [Dimension Name],
        'AMI' AS [Score Type],
        [HVBP_AMI$].[AMI-7a Measure Score] AS [Dimension Score]
      FROM [HVBP_AMI$]

      UNION

      . . .

       

      and so on. (Dimension Name is the specific metric being measured, and Score Type is used as a way to categorize the metrics into groups.)

       

      This has worked well with up to 9 tabs in the Excel spreadsheet, but when I try to include a tenth, I receive an error that the name of the tab is invalid. This happens regardless of what 10th sheet I try to add -- I have tested with several, and get the same result each time. Changing the name of the tab does not help either.

       

      Has anyone else run into this?

        • 1. Re: What is the max number of sheets that can be in an Excel-spreadsheet data source?
          Jim Wahl

          Yes, I ran into this issue today trying to use Custom SQL and union statements to "unpiovot" crosstab data in a handful of spreadsheet tabs. When I clicked on the Detail button for the error, it said query too complex. As I whittled down the number of UNION statements it would work.

           

          I'm sure this is driven by limitations in the Microsoft JET engine, but I didn't bother debugging it.


          I resorted to writing a short python script to do the merge and produce the TDE. You could also use Alteryx, which has a free project edition. There's a learning curve, but it's easier than other ETL tools. 

           

          Jim

          • 2. Re: What is the max number of sheets that can be in an Excel-spreadsheet data source?
            Charles Hooper

            Brian,

            I just combined 12 tabs in the same manner with no issue (Desktop version 8.0,5).

            In the 12th tab, I started with a number, had multiple spaces and an embedded $ character - still works OK.

            The only time I've seen that error is when I have mistyped the tab name in the custom SQL.

            I tend to NOT use special characters, spaces, numbers in tabs, so there may be issues I haven't run into.

            Can you share a screenshot of the custom SQL and of the bottom of the Excel file, showing the tabs?

             

            Jim Wahl may have hit on something - Jet has a limit it to the number a characters in a SQL statement - could you have hit that limit?  Quick test - take out the front sql for the first few UNION processes - - if you then get past the one that was causing an error, you are hitting the character limit. 

             

            When I've has that issue, I end up either pulling the data into a database OR I do the work in Excel and combine the tabs, there.

            1 of 1 people found this helpful
            • 3. Re: What is the max number of sheets that can be in an Excel-spreadsheet data source?
              Dan Huff

              Jim is likely pointing our the issue here Brian. Custom SQL in Tableau through jet can be tricky becuase of the way Custom SQL queries work. The Custom SQL statement is sent as part of the query each time a query is executed. When you have long and complex queries like yours above, the result is often that the query is too complex for the JET engine to handle.

               

              For example, using Superstore Subset I add Order Priority into the view. I used Custom SQL upon connection. For this simple view, this is the query Tableau has to generate:

               

              SELECT [TableauSQL].[Order Priority] AS [none:Order Priority:nk],

                SUM([TableauSQL].[Discount]) AS [sum:Discount:ok]

              FROM (

                SELECT [Orders$].[City] AS [City],

                  [Orders$].[Customer ID] AS [Customer ID],

                  [Orders$].[Customer Name] AS [Customer Name],

                  [Orders$].[Customer Segment] AS [Customer Segment],

                  [Orders$].[Discount] AS [Discount],

                  [Orders$].[Order Date] AS [Order Date],

                  [Orders$].[Order ID] AS [Order ID],

                  [Orders$].[Order Priority] AS [Order Priority]

                FROM [Orders$]

              ) [TableauSQL]

              GROUP BY [TableauSQL].[Order Priority]

               

              Everything within the FROM statement is my Custom SQL. You can see how these queries can become cumbersome quickly.

               

              Your best bet is to combine the data before bringing it into Tableau.

               

              I hope this helps,


              Dan

              1 of 1 people found this helpful
              • 4. Re: What is the max number of sheets that can be in an Excel-spreadsheet data source?
                Brian Smith

                Looks like the "consensus wisdom" was on the mark. I tried Chuck Hooper's test of removing the first several subqueries, and the query completed with no trouble. Looks like I will have to do some data preparation on this one. Thanks, everybody, and happy holidays.