4 Replies Latest reply on Feb 14, 2012 12:13 PM by Ian Waring

    UNION of two sheets in the same Excel workbook

    Ian Waring

      I have an Excel spreadsheet with data from our TASK management system (a sorta multiuser to-do list and SLA tracker system used by several hundred people) on board. The data for 2011 occupied 1,040,0000 rows, so I have an identical structure 2012 in a newer sheet in the same Excel spreadsheet. My mission is to do a Custom SQL UNION to effectively daisy chain the data in these together, so my extract will be the whole lot in one local relational database. When I try, I point the data open query at the first workbook/sheet, then select "Custom SQL", and edit the SQL to UNION ALL SELECT exact same fields, but change the sheet name from TASK2011 to TASK2012 throughout:

       

      SELECT [TASK2011$].[Account Code] AS [Account Code],

        [TASK2011$].[Account Name] AS [Account Name],

        [TASK2011$].[AM] AS [AM],

        [TASK2011$].[Assignment Type] AS [Assignment Type],

        [TASK2011$].[CD] AS [CD],

        [TASK2011$].[Date] AS [Date],

        [TASK2011$].[Date1] AS [Date1],

        [TASK2011$].[Description] AS [Description],

        [TASK2011$].[Full Name] AS [Full Name],

        [TASK2011$].[Full Name1] AS [Full Name1],

        [TASK2011$].[Minutes] AS [Minutes],

        [TASK2011$].[PULSE Customer] AS [PULSE Customer],

        [TASK2011$].[PULSEID] AS [PULSEID],

        [TASK2011$].[Secondary Desk Ref] AS [Secondary Desk Ref],

        [TASK2011$].[Sector] AS [Sector],

        [TASK2011$].[Sequence] AS [Sequence],

        [TASK2011$].[SLA Met] AS [SLA Met],

        [TASK2011$].[Status Desc] AS [Status Desc],

        [TASK2011$].[Task Id] AS [Task Id],

        [TASK2011$].[Task User Name] AS [Task User Name],

        [TASK2011$].[Type Desc] AS [Type Desc]

      FROM [TASK2011$]

       

      to which (in the knowledge the other sheet is called "TASK2012") I append:

       

      UNION ALL

      SELECT [TASK2012$].[Account Code] AS [Account Code],

        [TASK2012$].[Account Name] AS [Account Name],

        [TASK2012$].[AM] AS [AM],

        [TASK2012$].[Assignment Type] AS [Assignment Type],

        [TASK2012$].[CD] AS [CD],

        [TASK2012$].[Date] AS [Date],

        [TASK2012$].[Date1] AS [Date1],

        [TASK2012$].[Description] AS [Description],

        [TASK2012$].[Full Name] AS [Full Name],

        [TASK2012$].[Full Name1] AS [Full Name1],

        [TASK2012$].[Minutes] AS [Minutes],

        [TASK2012$].[PULSE Customer] AS [PULSE Customer],

        [TASK2012$].[PULSEID] AS [PULSEID],

        [TASK2012$].[Secondary Desk Ref] AS [Secondary Desk Ref],

        [TASK2012$].[Sector] AS [Sector],

        [TASK2012$].[Sequence] AS [Sequence],

        [TASK2012$].[SLA Met] AS [SLA Met],

        [TASK2012$].[Status Desc] AS [Status Desc],

        [TASK2012$].[Task Id] AS [Task Id],

        [TASK2012$].[Task User Name] AS [Task User Name],

        [TASK2012$].[Type Desc] AS [Type Desc]

      FROM [TASK2012$]

       

      When I try this (I admit, i'm an amateur on SQL), I get:

       

      Database error 0x80040E10: No value given for one or more required parameters.

       

      Unable to connect to the Microsoft Excel file "X:\CET Reporting - Datasources Combined.xlsx". Check that you have access privileges for the requested file and that it is not open in another application.

       

      so I guess I need some syntax in form of reference to the second sheet in order to tell Tableau it's in the same workbook. Any ideas on how to achieve this? Sorry for the noddy question... but this is before I think of other ways of seeing if I can daisy chain Excel name ranges containing well over a million rows combined, and pulling it in that way! Any ideas?