8 Replies Latest reply on Jan 21, 2019 6:09 AM by Michael Gillespie

    Local tables

    James Goodall

      Hi Guys,

       

      I've just started using Tableau desktop this afternoon and the Excel files i am using in there are now locked for other people to keep working on (and saving). I don't want this connection to the Excel file in Tableau to be live, but rather just a local copy that isn't connected in any way to the Excel file on the shared drives, as people need to use it still. I've tried clicking the Connection from 'Live' to 'Extract' and then saving/closing down Tableau but when i re-open it then the Excel file on the shared drive has the same problem again. How can I bring data into Tableau as stand-alone, local data and not a live connection please?

       

      Thanks,

      James

        • 1. Re: Local tables
          Michael Gillespie

          Are you actually creating the extract?  You should get a prompt asking where to store the extract file (with the .hyper extension).  From your description it doesn't sound like you are.

           

          Click the Extract radio button, then click on a worksheet tab (an existing one or a new, blank one).  That will force Tableau to create the extract.  THEN save your workbook.

          • 2. Re: Local tables
            James Goodall

            Hi Michael,

             

            Thanks for such a prompt reply! When I clicked on the ‘Extract’ radio button it did require me to save the extract – it does so in the ‘My Tableau Repository’ --> ‘Data Sources’ folder on my One Drive

             

             

            It is this bottom Tableau Extract here in this screenshot. But when colleagues want to go into ‘Power BI Input Template’ Excel file whilst I am using the Tableau job it still won’t let them save it, saying someone else is still using the file.

             

            What is it I’m doing wrong please?

             

            James

            • 3. Re: Local tables
              Michael Gillespie

              That is odd.  Once you've done the extract, Tableau should release the file lock on the source Excel file.

               

              Some basic "have you tried turning it off and on again" questions!

               

              1) You don't happen to have the file open in Excel yourself, do you?

              2) When you open Tableau does the data source icon look like the "Extract" icon - in other words, is Tableau actually using the extract or is it still stuck to the original Excel file?

              3) Can you try things in a different order - close your Tableau workbook, have your colleague try to open the Excel file, then open your workbook?

              • 4. Re: Local tables
                James Goodall

                Morning Michael,

                 

                Thank you for your help on this! I am back in the office this morning and have tried again and am still experiencing the same problems unfortunately!

                 

                As a test this morning I have opened my Tableau job, then opened the Excel file that the job extracted the data from. It lets me open the Excel (no ‘read-only’ type prompts or anything) but as soon as I go to save the file I am getting the following error message:

                 

                 

                 

                 

                In answer to the other question in your email below, Tableau should definitely be working off an extract not a live file (I’ve taken a screenshot of the top right-hand corner in Tableau below)

                 

                 

                 

                Do you have any other ideas as to what we can do about this? We are currently using Tableau on a trial period but this issue would cause big problems for us at NHS Digital as we will often have staff members working in the Excel files that Tableau has read in so we want to make sure Tableau is using a local extract and isn’t stopping anyone else from working.

                 

                 

                Thanks,

                James

                 

                James Goodall | Adult Social Care Statistics | NHS Digital

                1 Trevelyan Square | Boar Lane | Leeds | LS1 6AE

                james.goodall1@nhs.uk<mailto:james.goodall1@nhs.uk>

                General enquiries:

                0300 303 5678

                enquiries@nhsdigital.nhs.uk<mailto:enquiries@nhsdigital.nhs.uk>

                 

                 

                 

                 

                NHS Digital provides information and technology for better health and care.

                Find out more about us: www.digital.nhs.uk<http://www.digital.nhs.uk/> | @nhsdigital<https://twitter.com/nhsdigital>

                • 5. Re: Local tables
                  Michael Gillespie

                  Time zones are conspiring against us but I’ll respond as soon as I’m at my office.

                   

                  Michael Gillespie

                  651-329-9638

                  • 6. Re: Local tables
                    Michael Gillespie

                    What version of Excel are you using? 

                     

                    I'm honestly stumped by this: once the extract is complete, Tableau should release the lock on the file.

                     

                    Is it possible that a PowerBI process has the file locked?

                     

                    Go into a worksheet and look at the Data Source icon just below the Tableau logo.  It should look like this:

                    If it doesn't look like that, then the Extract process didn't work properly.

                     

                    At this point I'd suggest talking to Tableau support - go through your salesperson if you aren't already a customer.  They will help you figure this out.

                    • 7. Re: Local tables
                      James Goodall

                      Hi Michael,

                       

                      This is how it looks in the Worksheets of the Tableau job in question:

                       

                       

                       

                       

                      No one is in the Power BI job at the moment and haven’t been all day, so it can’t be Power BI that has locked it.

                       

                      Thanks,

                      James

                      • 8. Re: Local tables
                        Michael Gillespie

                        Officially stumped. 

                         

                        I have seen behavior like this in older versions of Tableau AND older versions of Excel (e.g., 2010).  Later versions of both tools have gotten much better at not keeping file locks around for no good reason.

                         

                        The major cause of this kind of problem comes when you're trying to refresh a published extract that lives on Tableau Server but connects to an Excel file on a network share somewhere.  But that's not what you're doing.

                         

                        Call support!