5 Replies Latest reply on Aug 30, 2017 8:41 AM by damodar.bhalerao

    Auto update data extraction files process

      Hi all community.

      I use many excel files, and its saved in our shared drive everyday.

      Now I can union them, but all the tabs must be in one file which is very tiresome,


      I have everyday files starting from 1st Jan 2016 and everyday a new files is stored.


      How can tableau autopick-up each new file and auto union to load our combined dashboard output for both summary and details worksheet?


      There is only one source which can be union of all files, but I know union can be done if all tabs in single excel file which is very frustrating to do.


      any thoughts?


      at this time I want a tableau solution, not considering to buy etl tool for now.

        • 1. Re: Auto update data extraction files process
          Ben Neville

          If you go to your data source and edit the union, you should see a Sheets section and a Workbooks section. If you leave the Sheet section blank, Tableau unions all sheets/tabs within the Excel workbook you have specified. However, if you also delete what is in the Workbooks selection (or include a partial format with a * for the parts that change), Tableau will union all sheets in all workbooks in the current folder. You can also check the boxes to go up a folder or down to sub-folders.


          I think the deletion of the workbook name is what you're after. Hope that helps.

          • 2. Re: Auto update data extraction files process

            No the reason I posted is we have multiple files,

            multi files -> multi tabs

            in this scenario I cannot manually put all tabs in one place, its too much of manual work

            • 3. Re: Auto update data extraction files process

              you mentioned

              Tableau will union all sheets in all workbooks

              can u provide a sample of attachment that unions all shetts in all files located in a folder?

              • 4. Re: Auto update data extraction files process
                Ben Neville

                You don't need to put all the tabs in one place. Just all the Excel files in one place.


                I can't provide a sample attachment as your directory won't be the same as mine, but the screenshots may help.


                First, connect to a new data source and select Excel as the file type. Then navigate to the directory where your Excel files are. I went to my Datasources file in My Tableau Repository, but use whichever location makes sense for your files:

                Datasources Directory.png


                Pick any file - this is irrelevant. We're just establishing a connection to this directory (Ben Neville >> My Documents >> My Tableau Repository >> Datasources). Next, grab the New Union part from the left side, and drag this to where it says Drag Sheets Here. Tableau will pop open a Union dialog. Make sure you select the Wildcard (automatic) tab/option (circled in red below). Delete what you see in the Workbooks section (highlighted below) that includes the file name. You should see something like this:

                Union all files and sheets.png


                Next, select OK. Tableau will create a single data source with all sheets/tabs/files unioned. You will find 2 fields - Path and Sheet. Path refers to the file name and path, while Sheet refers to the specific sheet within that Excel file/workbook. If you drag these into a worksheet in Tableau on their own, you should see a list of all files and sheets in that folder. I had 7 Excel files, each with 1 or more sheet, so I see something like the following:

                Path and Sheet.png


                You will want to make sure your files contain the same column structure. Mine don't as they're completely unrelated files, so if I started dragging columns in, I would find many Nulls as they do not line up properly.

                1 of 1 people found this helpful
                • 5. Re: Auto update data extraction files process

                  Thank you,

                  I was using 10.0

                  is it possible in 10.2 may be? or higher? will ask our admin to upgrade all my tableaus