14 Replies Latest reply on May 31, 2016 7:09 AM by Russell Christopher

    Batch download all Tableau workbooks from the server

    Eric McDonald

      I want to be able to try out the TWBX Auditor tool (http://community.tableau.com/thread/118450) by Andy Cotgreave  . To do this I want to download all Tableau workbooks from the server. Is there any way to do this?

       

       

      I've tried opening a Tableau backup file using WinZip but I can't see the Tableau TWB files in there. I can see lots of TDEs. I know the backups have them as I can restore the backup to a clean server and they are there.

        • 1. Re: Batch download all Tableau workbooks from the server
          Russell Christopher

          Hey Eric -

           

          TWB files are not stored on the file system of Tableau Server like extracts are - that's why you can't see them. They are stored as binary objects inside the PostgreSQL database. You won't be able to get at them via the tsbak file.

           

          There isn't an easy way to do what you want. You'd need to get a list of the workbooks (from one of the system views or by grabbing the workbooks.xml file from Server) and then loop through each one in a script, calling tabcmd get against each one.

           

          Not for the faint of heart

          • 2. Re: Batch download all Tableau workbooks from the server
            Kelly Princehouse

            Maybe this will help. Here's some Powershell that will download a copy of all workbooks published to a server (provided the permissions are set up to allow this). It requires tabcmd and Powershell, and downloads the files to the current working directory.

             

            You may need to add tabcmd to your path to get this example to work. You can do that with this line:

            Add-PathVariable "C:\Program Files (x86)\Tabcmd\Command Line Utility"

            (With adjustments as appropriate for where tabcmd lives on your system. Also, I think Add-PathVariable is part of the Powershell community extensions, so you'll need to install those.)

             

            ## Login to the Tableau Server

            tabcmd login -s http://YourTableauServer -u YourUsername -p YourPassword

             

            ## Tableau Server stores a list of all workbooks in the root directory; get that first.

            tabcmd get "workbooks.xml"

             

            ## Read the contents of the file into $xml

            $xml = [xml] (Get-Content "workbooks.xml")

             

            ## Iterate through list of workbooks and grab them all; this gets .twb and .twbx.

            Foreach ($workbook in $xml.workbooks.workbook)

            {

              tabcmd get "$($workbook.path)/$($workbook.'repository-url').twb"

            }

            1 of 1 people found this helpful
            • 3. Re: Batch download all Tableau workbooks from the server
              Eric Peterson

              This works for me and is something that Tableau should include - the ability to download individual workbooks in batch as a precautionary backup.

               

              One caveat - since we have SSL encryption I had to modify as such:

               

              tabcmd login -s https://data/  -u username -p password --no-certcheck

               

              ## Tableau Server stores a list of all workbooks in the root directory; get that first.

              tabcmd get "workbooks.xml"  --no-certcheck

               

              ## Read the contents of the file into $xml

              $xml = [xml] (Get-Content "workbooks.xml")

               

              ## Iterate through list of workbooks and grab them all; this gets .twb and .twbx.

              Foreach ($workbook in $xml.workbooks.workbook)

              {

                tabcmd get "$($workbook.path)/$($workbook.'repository-url').twb"  --no-certcheck

              }

               

              Thanks again Kelly,

              Eric

              • 4. Re: Batch download all Tableau workbooks from the server
                Mark Stange-Tregear

                Hi,

                 

                This seems to work for me as well, but my session is expiring after every workbook is saved. A new session is automatically starting after each session expiry, but I am prompted for my password each time to start the new session. Any idea why my session would be expiring after each workbook?

                 

                Thx,

                 

                Mark

                • 5. Re: Batch download all Tableau workbooks from the server
                  Toby Erkson

                  Kelly Princehouse , Eric Peterson

                  I was successful in downloading the 'workbooks.xml' file but my PowerShell script doesn't seem to be working.  No workbooks are downloaded to my local computer.  What am I doing wrong?

                   

                  Here's the script output

                   

                  Here's my batch file:

                  tabcmd login -s "http://my.server.com" -u terkson -p MyPassword --no-certcheck
                  tabcmd get workbooks.xml -f C:\temp\workbooks.xml
                  powershell $xml = [xml] (Get-Content C:\temp\workbooks.XML)
                  powershell Foreach ($workbook in $xml.workbooks.workbook) {tabcmd get "$($workbook.path)/$($workbook.'repository-url').twb"}
                  tabcmd logout
                  
                  • 6. Re: Batch download all Tableau workbooks from the server
                    Eric Peterson

                    Hi Toby,

                     

                    I see a few things different:

                    1. In the PS window there is a rem tabcmd get workbooks.xml.  REM is usually used to comment REMARK.  That could cause this line to fail.

                     

                    2. I don't believe you need to add the x:\temp\workbooks.xml in both lines (I don't in mine).

                     

                    3. My Foreach line is:

                    Foreach ($workbook in $xml.workbooks.workbook)

                    {

                      tabcmd get "$($workbook.path)/$($workbook.'repository-url').twb" 

                    }

                     

                    My code works as such:

                    ## Tableau Server stores a list of all workbooks in the root directory; get that first.

                    tabcmd get "workbooks.xml"  --no-certcheck

                     

                    ## Read the contents of the file into $xml  - this creates a workbooks.xml in E:\Workbooks

                    $xml = [xml] (Get-Content "workbooks.xml")

                     

                    ## Iterate through list of workbooks and grab them all; this gets .twb and .twbx. 

                    ## Need to press enter twice to get it to run

                    Foreach ($workbook in $xml.workbooks.workbook)

                    {

                      tabcmd get "$($workbook.path)/$($workbook.'repository-url').twb"  --no-certcheck

                    }

                     

                     

                    tabcmd logout

                    • 7. Re: Batch download all Tableau workbooks from the server
                      Toby Erkson

                      Thanks for the reply Eric.

                      I've already downloaded the workbook.xml file which is why it's remarked out.  My bad, I didn't state that.

                      I made the path explicit to a TEMP folder...that shouldn't make a difference, right?

                      The FOREACH loop doesn't appear to be working.  Logging on and off the Tableau Server works.  Downloading the workbook.xml file works.

                       

                      Here's a screen shot of the code I'm currently running.  The file is saved in C:\temp and that is where the files dump to (at least the workbooks.xml)

                      I don't know PowerShell so I'm at a disadvantage here.  My PATH variable is set up to point to PowerShell so no errors there, the cmd script does recognize the "powershell" command, just doesn't appear to be executing the FOREACH loop

                      • 8. Re: Batch download all Tableau workbooks from the server
                        Toby Erkson

                        Progress but not quite there:

                        The answer at StackOverflow got me a little further.  Here's my modified code that almost works:

                        The problem appears to lay in the way single and double-quotes are used.  This is making it tricky to figure out how to use them.  Suggests on over-coming this appreciated.

                        • 9. Re: Batch download all Tableau workbooks from the server
                          Toby Erkson

                          I haven't solved the above issue but I found another two-step way to make it work.

                           

                          Create a batch file (runPS.bat).  It contains a single line that points to the PowerShell script:

                          powershell -command "&""C:\temp\dl_wkbks.ps1"""
                          

                           

                          Create the PowerShell file (dl_wkbks.ps1).  This is what pulls the workbooks:

                          tabcmd login -s "http://my.tableau.server.com" -u terkson -p "mypassword&" --no-certcheck
                          tabcmd get workbooks.xml -f workbooks.xml
                          $xml = [xml](Get-Content "C:\temp\workbooks.XML")
                          Foreach ($workbook in $xml.workbooks.workbook) {tabcmd get "$($workbook.path)/$($workbook.'repository-url').twb" --no-certcheck}
                          tabcmd logout
                          

                           

                          From the cmd prompt run the file runPS.bat.  That will then execute the dl_wkbks.ps1 PowerShell script which will download ALL the workbooks from the Server to the directory the scripts are running in.

                           

                          The reason I want a single-step approach instead of the above two-step approach is because I want to turn this procedure into VBA code without having to programmatically create scripts to execute outside of the VBA code.

                          • 10. Re: Batch download all Tableau workbooks from the server
                            Toby Erkson

                            Okay, figured it out, along with a road-block that Tableau Support (thanks Matt!) helped me with.  Here is what the code looks like for the PowerShell script:

                            tabcmd login -s "http://myserver.company.com" -u "terkson" -p "passwordGoesHere" --no-certcheck --timeOut 6000
                            tabcmd get workbooks.xml -f "C:\temp1\workbooks.xml"
                            $xml = [xml] (Get-Content "C:\temp1\workbooks.xml")
                            Foreach ($workbook in $xml.workbooks.workbook) {Write-host "Getting: " $workbook.name; tabcmd get /workbooks/"$($workbook.'repository-url').twbx" -f "C:\temp1\$($workbook.'repository-url').twbx"  --no-certcheck}
                            tabcmd logout
                            exit
                            
                            
                            

                            I used VBA code to build the script and save it as "dl_wkbks.ps1".  The command shell then executes the .ps1 script in the code.  I used this in the updated version of What's in your workbooks? The TWB Auditor v2.0.

                             

                            Okay, what I learned from Matt

                            When you log in to a specific Site you do NOT include the Site path!  For example, when I was logging in to our administration Site, named "area51", including the /t/area51/workbooks/ path was causing an error of File Not Found and then logging me off!  So you'll notice that the workbook path has been removed from my code:

                            Originally (would fail):

                            tabcmd get "$($workbook.path)/$($workbook.'repository-url').twb"  --no-certcheck
                            
                            
                            

                            Now (works):

                            tabcmd get /workbooks/"$($workbook.'repository-url').twbx" -f "C:\temp1\$($workbook.'repository-url').twbx"  --no-certcheck
                            
                            
                            

                            Notice that the ($workbook.path) has been removed and /workbooks/ has been inserted after the tabcmd get.

                            Note:  The above code is an output based upon user inputs saved in variables; that is why login credentials and file path appear hard-coded (you can change them for your own purposes).  You can view the code in the TWB Audito v2.0 application in the PullWorkbooks module.

                             

                            What else I learned...or...explaining the bald spots on my head where I pulled my hair out

                            Not every workbook can be downloaded as a .twb.  Turns out many of my workbooks I was trying to download were really .twbx!  If you noticed in my code, all workbooks downloaded are saved as .twbx.  If a workbook fails to open properly then change the file extension to .twb and all will be good.  I modified the code in the TWB Auditor to perform this when necessary.

                            • 11. Re: Batch download all Tableau workbooks from the server
                              Haralambos Kontrafouris

                              Hi Toby,

                               

                              I have a relevant but much simpler case (hopefully). I need to have an account, a list of all worksheets and dashboards that have been published on the server.

                              A "report of reports" so to speak.

                              An autogenerated  txt file can do.

                               

                              Could you please point out to a direction?

                               

                              Thank you

                               

                              H

                              • 12. Re: Batch download all Tableau workbooks from the server
                                Todd Schnack

                                This is a very helpful guide. FYI -- to get around the issue of all the workbooks being saved as a twb (even if they are actually a twbx), remove the "-f" option from the "get" step. Each workbook will then be saved with its correct extension.

                                2 of 2 people found this helpful
                                • 13. Re: Batch download all Tableau workbooks from the server
                                  Rafael Schaffer

                                  Now in Tableau Server 9.3 this stopped working, because XML endpoints is not available anymore.

                                  Has anyone been able to use the REST API to get the workbooks list, then download it with tabcmd?

                                  1 of 1 people found this helpful
                                  • 14. Re: Batch download all Tableau workbooks from the server
                                    Russell Christopher

                                    Perhaps you should just use TabMigrate? GitHub - tableau/TabMigrate

                                    3 of 3 people found this helpful