1 2 Previous Next 28 Replies Latest reply on Nov 1, 2018 12:19 PM by Jameel Shihadeh Go to original post
      • 15. Re: Data refresh is not including new columns from Excel spreadsheet
        Nathan Schwinnen

        Thanks, Diego.  I have created a support case.  I may have also found a workaround, which was to put this new data on a separate tab in Excel and then add a new data source in Tableau.  I don't want to do this because I believe it means I will have to upload this data to Tableau Online and then refresh it separately.  It will solve the problem I think, just not in the most efficient way.

        • 16. Re: Data refresh is not including new columns from Excel spreadsheet
          Hari Krishna Dasari

          Hello Nathan,

           

          Not sure if this has been addressed already. However, I am facing the same issue.

          I have a published data source. I open the data source in a new workbook, create a local copy of the published data source so as to not touch any of the custom calculated fields and then refreshed the data source. I was able to see the new columns.

           

          Surprisingly, a couple of our team members(all with similar access) were able to see the changes on the server while some were not able to. I found this kb article on Tableau which says the issue is still under investigation. Was this the same reply you received for your support case??

           

          Newly Added Column Does Not Appear After Refreshing Published Data Source | Tableau Software

          • 17. Re: Data refresh is not including new columns from Excel spreadsheet
            Jameel Shihadeh

            Using Tableau 10.4.2.

             

            It's a little late but I'm having the exact same issue with both the live and extracted excel file (sitting on sharepoint...relevant?). The interesting behavior I noticed is that Tableau was taking the first n columns from the excel table - whatever the original number of columns was in the data source. If I inserted a new column at the end, I wouldn't see it at all. If I inserted a new column in the middle, however, the new column showed up but whatever column was at the end would disappear from tableau. For example, the original data pull only had 4 columns, added a 5th in the middle, but I would only ever see the first 4 columns in Tableau. This occurred no matter how many times I refreshed, added a new connection, tried changing the mapping, removing/re-adding extracts, etc.

             

            Maybe someone from Tableau can shed some light on what i was seeing - that Tableau seemed to be programmed to 'pull the first n columns from the excel table'. Maybe the fact I had an extract sets this behavior, though the extract was deleted and yet the behavior continued.

             

            The only thing that worked was manually clearing the cache.

            Clearing the Tableau Desktop Query Cache | Tableau Software

             

            So, there's that to try.

            1 of 1 people found this helpful
            • 18. Re: Data refresh is not including new columns from Excel spreadsheet
              Erik Wempa

              Just stumbling across this thread now, but here's a work-around I recently discovered...

               

              Had the same situation.  A live connection to an Excel data source.  I updated an entry in the last row of my XLS, then saved the XLS and closed it.  Opened my workbook (live connection to Excel), and that last entry didn't have the updated value.  It still had the old value.

               

              I tried manual refreshes, tried changing the live connection to extract and then refreshing, checked to make sure the DFS path was right...nothing worked.

               

              So what fixed my data?

               

              I checked USE DATA INTERPRETER on the data connection page.  Once I did that, the data automatically updated to show the correct value in the field.  I saved the workbook, then went back and conducted several re-tests (making updates to the XLS, then re-opening the workbook in Desktop), and the data updated correctly every time.

               

              I was shocked, to be honest.  I thought DATA INTERPRETER just cleaned up data and removed headers/footers, but this did the trick.

               

              Just figured I'd throw this out there.  Cheers!

              3 of 3 people found this helpful
              • 19. Re: Data refresh is not including new columns from Excel spreadsheet
                Simon Runc

                Thanks Erik...good to know! (if a little odd )

                • 20. Re: Data refresh is not including new columns from Excel spreadsheet
                  Jameel Shihadeh

                  Glad you found a trick to get this working. I too thought the data interpreter was more for messier data.

                  • 21. Re: Data refresh is not including new columns from Excel spreadsheet
                    Jameel Shihadeh

                    Hi Erik,

                     

                    Unfortunately I hit the issue again and Data Interpreter did not help at all.

                     

                    I continue to suspect it's a long standing but intermittent bug with how Tableau updates it's temporary cache, as clearly all sorts of approaches that should work (F5, refresh, re-add via edit connection) do not.  I believe the issue is tied to how the data pulls are 'defined' based on a fixed number of rows and columns in the data source and that these values are not updated properly during a refresh. As I mentioned in a higher post, I can, for example, add 3 new data rows to the top of my data table and these new rows will suddenly appear in the tableau viz after refreshing; however, I will lose an equal number of rows from the bottom of the table. Same with columns - I can insert a new column near the front of the table and it will suddenly appear in the viz, but a previously viewable column will then be lost. So the cache/temporary extract is being updated only with a fixed n rows x m columns from the data source but those parameters are not being updated properly during refresh or when the data source is updated.

                     

                    I know the cache is the issue because I navigate to the cache, I find newly generated ttde files that, when renamed as tde and opened in tableau, show the exact non-updated data I cant seem to get rid of.  And deleting the ttde files seems to work mostly consistently. I have had 100% success when I completely remove the data source and then re-add it, which is no solution either.

                    2 of 2 people found this helpful
                    • 22. Re: Data refresh is not including new columns from Excel spreadsheet
                      Toby Erkson

                      Jameel Shihadeh , that's some good investigative work   I would recommend submitting a support ticket with Support Case | Tableau Software , explaining your discoveries.  It would help the Tableau development team towards working to fix this issue.

                      • 23. Re: Data refresh is not including new columns from Excel spreadsheet
                        donna.gill

                        I experienced this same issue today and found this thread, and I am writing this in case it helps  another newbie. I had added columns and could not get them to display in Tableau, and finally discovered that Tableau was pointing to an earlier copy of the Excel file it had stashed in a temporary folder, which is likely the caching issue that was described earlier in this thread by JAMEEL SHIHADEH

                         

                        I had switched from an extract to a live connection during my initial troubleshooting, but no attempts at refreshing were working.

                         

                        Using the suggestion to Edit Connection (Data Source > Connections > drop-down arrow beside the data source), I saw that Tableau pointed to a copy it had made of the Excel file at C:\Users\myname\AppData\Local\Temp\TableauTemp\2665480029\Data\myfoldername\myExcelfile  which had a timestamp of the last save prior to my adding the columns.  When I redirected to the correct folder, of course it pulled in the new columns.

                        1 of 1 people found this helpful
                        • 24. Re: Data refresh is not including new columns from Excel spreadsheet
                          Jameel Shihadeh

                          I finally got around to looking at this issue again (because it wont go away). I have not yet opened a support case but i plan to. Importantly, I made some progress by developing a different (terrible but effective) way of 'refreshing' the tableau file that will pull in newly added columns in excel and doesn't require destroying the schema or remembering where the cache is located.  Please let me know if it works for you.

                           

                          Warning: this process involves poking at the twb xml code. Also, you must close and re-open Tableau after making changes to the xml.

                           

                          If you open the xml in a text editor, I noticed a gridOrigin parameter for each excel connection starting after "<relation connection=...." (search for the term "gridOrigin" to find it) The value is formatted, for example, as B6:K383:no:A1:K383:0.  This seems to break down into two excel ranges:

                          • An "Active Range" where relevant columns are pulled from, in this case B6:K383
                          • An "Overall Range" that I think represents the limits of all possible data on that sheet, in this case A1:K383

                           

                          I suspected that this value or something related to this value wasn't getting updated properly (or its getting pulled from an old cache unexpectedly, which is probably why clearing the cache works as well).

                           

                          My original excel table ranged from B6:K383. Notice the gridOrigin value and list of columns.  GridOrigin does show up in one other spot but that turned out not relevant for the below tests.

                          I added a column called "Test" (column L) to the end of the excel table, saved, closed, and then refreshed in Tableau (F5 on data source tab). I confirmed "Test" did not show up in Tableau.  I looked at the xml again. gridOrigin was changed: Overall Range now covered my new column but Active Range did not update, showing B6:K383:no:A1:L383 (see below).  Perhaps this is why Tableau doesn't see the new columns - or why inserting a new column in the middle of an existing table does show up but pops out the last column.

                          I edited the Active Range to B6:L383 (the 'correct' range that included my Test column), so the gridOrigin value was B6:L383:no:A1:L383:0.

                          Save and re-open Tableau.  Voila, my Test column appears automatically:

                          The above was tested as a live connection (Extract fully removed/deleted).  Next, I tested with the data source as an extract. I created the extract, added another column to my table in excel (column M, called "test 2"), refreshed the extract and confirmed that "Test 2" didn't show up among my columns.  I saved and closed Tableau and opened the twb in my text editor again. Similar to refreshing the data source, the xml shows that gridOrigin does change: Overall Range is updated but not the Active Range.

                          Changed the L to an M, save the twb, reopen in Tableau, and refresh the extract.  Voila, my new column appears.

                           

                          Hope this helps someone and gives more clues to why Tableau is behaving this way.

                          • 25. Re: Data refresh is not including new columns from Excel spreadsheet
                            Jameel Shihadeh

                            I've submitted a support case, so here's to hoping.

                             

                            Oct 1, 2018 update:

                            Tableau support has been wonderfully active on the support issue. After a good back and forth, I received this encouraging message: "I am just writing to briefly update you on the investigation. We were able to reproduce the issue in our test environment but we are not sure yet of the exact cause or fix so we are still investigating and will follow up again once we have an update for you."

                            I am just writing to briefly update you on the investigation. We were able to reproduce the issue in our test environment but we are not sure yet of the exact cause or fix so we are still investigating and will follow up again once we have an uodate for you.

                            • 26. Re: Data refresh is not including new columns from Excel spreadsheet
                              Jameel Shihadeh

                              In some good news, my support case was resolved via a workaround.

                               

                              My issue (and maybe some of yours) was caused by connecting to a Named Range (like an Excel Table created via Insert -> Table) which was not getting updated in Tableau during any refresh.  In the words of Leah (the Wise, the All-Knowing, the Patient) Tableau support tech:

                               

                              "When connecting to Excel data, Tableau Desktop loads the data from Excel into a "shadow extract". From that time forward, Tableau Desktop reads from the shadow extract, rather than from Excel. By default, Tableau Desktop does not send a fresh query to Excel to update the range of the Named Range. This default behavior is currently by-design but could change in a future release. For now there are a couple of potential workarounds."

                               

                              Emphasis mine. This is why clearing the tableau cache works every time - Tableau then rebuilds the shadow extracts and pulls in the new values from the Named Ranges.

                               

                              Workaround 1: Avoid connecting to Named Ranges / Excel Tables in Tableau Desktop if columns are going to be updated/added to the Named Range in the Excel file.

                              The issue is caused by Tableau not pulling in new range values for Named Ranges. One solution? Stop using named ranges or Excel Tables.  When an Excel file loads in Tableau, both the sheet and any named ranges within that sheet will show like this, in this example, Sheet1 is the sheet with its little table icon and Table 1 with the green label is a Named Range in Sheet1.

                              If you have a lot of non-data stuff around the target area (instructions, etc) like I do, you'll see the columns are often labeled F1, F2, F3.  Activating the Data Interpreter as Erik Wempa suggested helps, but sometimes did a pretty bad job of finding my header rows and data.  So I continued to Workaround 2. (I wonder if there's a way to actively adjust the recommendations of the Data Interpreter...)


                              Workaround 2: Use our optional feature flag to change the default behavior to refresh the "shadow extract"

                              When running the Tableau executable, we can type some parameters to the end that change the way Tableau behaves.  There are two places to make changes, so ensure you do both to get the smoothest operation.

                               

                              Add the parameter to a Tableau Shortcut

                              1. Create a shortcut to Tableau Desktop
                              2. Right-click it and choose "Properties" and select Shortcut
                              3. At the end of the "Target" field, put in a space, then add these characters: 
                                -DRefreshRelationAttributes
                              4. Click OK

                               

                              Now, each time you launch an instance of Tableau with that shortcut, it will have the feature flag turned on. Note: The data source may need to be refreshed for the workaround to be applied.  The workaround will only work when launching Tableau first (via shortcut) rather than double-clicking on a workbook. To set it up so that double-clicking a workbook also triggers the flag, do the following:

                               

                              Add the parameter when opening any tableau file

                              1. Download Default Programs Editor: http://defaultprogramseditor.com/ then double click on the .exe and click Run.
                              2. Click File Type Settings
                              3. Click Context Menu
                              4. Search for .twb
                              5. Select .twb and click Next.
                              6. Select the Open command (top row) and click Edit Selected Command
                              7. Add the flag to the file path. Path should now appear similar to the following: "C:\Program Files\Tableau\Tableau 2018.1\bin\tableau.exe" -DRefreshRelationAttributes "%1"
                              8. Click Next.
                              9. Click Save Context Menu
                              10. Click OK.
                              11. Repeat for the .twbx file extension

                               

                              You can confirm that Workaround 2 is active by going to Task Manager -> View -> Select columns -> Add a check next to Command Line


                               

                              This workaround worked great! And even better, I don't have to worry about this refresh issue happening with future excel data sources.

                               

                              Some additional observations when playing with this functionality:

                              • Opening the tableau file and hitting refresh generally worked. Sometimes it did not work right away and i had to hit refresh 3-4 times.  I suspect this might have to do with where we often save our files (a mapped network drive linked to sharepoint). I don't have this issue when the file is saved to my desktop.
                              • While this flag seems to do a good job with new columns in the excel file, apparently deleting or adding rows above the Table (like adding additional instructions for people) can cause new problems similar to the F1, F2, F3 point above. I was able to resolve this by turning the Data Interpreter on then off. For whatever reason, going through this step pulls the new named range values properly.

                               

                              Goodluck!

                              4 of 4 people found this helpful
                              • 27. Re: Data refresh is not including new columns from Excel spreadsheet
                                Adrian Townsend

                                Building on Jameel's issue with named ranges, it also applies to the Excel tables that are being used as lookup tables.  I had added 11,000 rows of data to my Excel transactions sheet, and Tableau just wasn't updating with the extra lines (no matter what I did, including clearing cache etc.).  So as a test, I copied a line of existing data from the transactions sheet and pasted it at the bottom (essentially adding a row), and Tableau was able to update it.

                                 

                                It turned out that because the 11,000 rows of data were associated with brand new budget accounts, I had also updated the accompanying Excel lookup table with extra rows of information.  The lookup table was a named range.  When I changed the lookup table format to just being an Excel worksheet, Tableau updated everything properly.  Thank you very much for this post Jameel.

                                • 28. Re: Data refresh is not including new columns from Excel spreadsheet
                                  Jameel Shihadeh

                                  Hi Adrian Townsend , glad this was helpful. Yes, all formal Excel Tables are "registered" as named ranges (makes sense in a lot of ways). And the named ranges are what show up in Tableau in addition to the worksheets. Very unexpected that Tableau does not recheck the named range values.

                                   

                                  A "regular" named range and a Table:

                                   

                                  The entries under Name Manager

                                   

                                  The list of objects from Tableau

                                  1 2 Previous Next