6 Replies Latest reply on Aug 30, 2018 6:08 AM by Tom Hier

    Quirks in Connecting to Excel Datasets

    Tom Hier

      I typically connect Excel databases to Tableau and have noticed an annoying and sporadic quirk with the connection feature.  Here's what happens:

       

      1.  I open the Excel file and prepare my dataset for Tableau.  Typically, I format the worksheet with nothing but field names and data -- no extraneous cells with random data, etc.

      2.  I open Tableau and connect to the Excel dataset -- live connection.  I don't typically need to use the data interpreter when connecting to Excel, but the problem I have happens whether the data interpreter is used or not.

      3.  Once the dataset is in Tableau, and I've done some visualizations, I sometimes need to adjust data in the Excel file.  When I do that, I save the Excel file so that the changes will register in Tableau, and that's when the problem occurs.

              ...If the Tableau file is open when I try to save the Excel file, I get an error message in Excel and I either have to save the Excel file with a new name, or I lose the work done in Excel.

              ...If I save Tableau and close the Tableau program before saving the Excel file, then I can save the Excel file with no problem.  But this becomes a hassle if I want to make a series of changes in Excel and see the results in Tableau.

       

      I could create an extract in Tableau and update the data periodically, but that doesn't address the problem of trying to save the Excel file while Tableau is open.

       

      Is there a solution to this problem, other than closing the Tableau file each time I need to save an updated Excel file?

       

      Thanks.

        • 1. Re: Quirks in Connecting to Excel Datasets
          Ken Flerlage

          I've found it quite painful working with Excel data sources as well. Sometimes Tableau locks the file which is why the first issue occurs. My general approach is to save each new edit of the Excel file as with a new file name--just add a prefix, 01, 02, 03, etc. Then you just have to update the connection on the data source. Creates a lot of extra Excel files, but eliminates the issues.

          • 2. Re: Quirks in Connecting to Excel Datasets
            Mahfooj Khan

            Hi Tom,

             

            I can understand your situation. I've experienced this several times with my current project and found a way to deal with this situation. Once you connect the source in tableau then post fixing the metadata choose extract as connection mode instead of live. While analyzing data in tableau If you found some anomalies in the excel then go back to excel fix the data issues and save it. Now, at tableau end edit the data source by just right click and go to data source like this.

            then you may have such view. Just  click on refresh options and check. Data will be refreshed. In this way you can be get rid of such errors.

             

            Let us know if this works for you.

             

            Mahfooj

            • 3. Re: Quirks in Connecting to Excel Datasets
              Peter Fakan

              Yes thats an issue, but its related to live connections to excel sheets and then trying to make changes to the product while its got a live Tableau connection - I've been burned too many times with live connections so I pretty much always do extracts just so I can control the data update time.

               

              HTH

               

              Peter

              • 4. Re: Quirks in Connecting to Excel Datasets
                Tom Hier

                Thanks, Ken.  Your approach is a useful workaround, but I sure hope Tableau does something to fix the problem soon.  Having multiple Excel files is frustrating and confusing.

                 

                Tom

                • 5. Re: Quirks in Connecting to Excel Datasets
                  Tom Hier

                  Thanks, Mahfooj.  I was trying to avoid using an extract, but it sounds as though that may be the only way to solve the problem at this point.  Given how many people use Excel as a gateway to Tableau, this is a problem that really should be addressed soon and substantively.

                   

                  Tom

                  • 6. Re: Quirks in Connecting to Excel Datasets
                    Tom Hier

                    Peter,

                     

                    The extract seems to be the way to go.  Thanks so much for your comments.  Nice to know that I’m not the only one who has been frustrated by this problem.

                     

                    Tom