11 Replies Latest reply: Dec 21, 2011 6:01 AM by Brian Booden RSS

    Accessing network Excel files using Qlikview server

      I have a Qlikview doc that runs perfectly on a desktop version of Qlikview that is running on my laptop.  The file runs well on both.  But when i try to run it via a task on Qlikview Sever, it fails.

       

      The file is accessing a network folder (UNC path) and looping through all Excel files in the folder and reading the data out of a specific sheet in each Excel (xlsb) file.

       

      Attached is an example of the code.  The file seems to fail right away, so i am guessing it is something to do with the UNC location?  I know the permissions and files are set up correctly as the Qlikview file runs perfectly when run through the desktop version installed locally on the server.

       

      Am i missing something obvious that i need to do when running via a Qlikview server task?

        • Accessing network Excel files using Qlikview server
          Paul Kelly

          Hi Brian

           

          // Set the Extract Directory for 2011 data

          Directory \\servername\team\2011 Data; // Excel directory

           

          Notice that you have a space in your URL - could it be as simple as putting the URL in single quotes?

           

          I do the following...

           

          SET vDataPath='C:\QlikViewFiles\Timbmet_Live\QVDTables';

          DIRECTORY $(vDataPath);

           

           

           

          Regards

          Paul

          • Accessing network Excel files using Qlikview server
            Rob Wunderlich

            1. Do you have document logging on? If so, is there any error in the doc log or is it just that no files are found in the directory?

             

            2. Is the distribution service running with the same account as when you tested with desktop?

             

            3. Is the desktop you tested with 32bit or 64bit?

             

            -Rob

              • Accessing network Excel files using Qlikview server
                Daniel Rozental

                Most probable cause is that the user running the QlikView Distribution Service doesn't have access to the folder where the Excel files are, just make sure to add it with reading permissions.

                  • Re: Accessing network Excel files using Qlikview server

                    Daniel Rozental wrote:

                     

                    Most probable cause is that the user running the QlikView Distribution Service doesn't have access to the folder where the Excel files are, just make sure to add it with reading permissions.


                    There is one Qlikview network account that runs all Qlikview services, including this one.  I know for a fact that the account can see the folder already, as i can log on using the qlikview service account and see the directory listing in Windows Explorer.

                     

                    Is that what you meant?

                      • Re: Accessing network Excel files using Qlikview server
                        Daniel Rozental

                        You need to get the log files, the file that is in the same folder as the QVW, with almost the same name but with .log extension.

                        • Re: Accessing network Excel files using Qlikview server

                          I decided to simplify the test to load a text file from the network location, just to rule out the Excel drivers causing a problem.

                           

                          Same issue with a text file - the QEMC task runs when the directory path is relative, local to where the file is stored ie 'Directory ..\..\data;'

                           

                          QEMC task fails when trying to access via network location ie 'Directory \\\server\folder1\folder2\;'

                           

                          Conclusion - something on the Qlikview Server/QEMC/Publisher does not have the required access to the network location.  Which baffles me, as all Qlikview services are running with the same network account that has read and write access to the network location.

                           

                          I must be overlooking something simple but i cannot see what!

                            • Accessing network Excel files using Qlikview server
                              Rob Wunderlich

                              Brianl,

                               

                              Adding to what Daniel said about the log file, the document log is created when the "generate logfile" setting is on in Document Properties, General. You'll find more information there than from a distribution log.

                               

                              -Rob

                                • Re: Accessing network Excel files using Qlikview server

                                  Rob Wunderlich wrote:

                                   

                                  Brianl,

                                   

                                  Adding to what Daniel said about the log file, the document log is created when the "generate logfile" setting is on in Document Properties, General. You'll find more information there than from a distribution log.

                                   

                                  -Rob


                                  Thanks, Rob.

                                   

                                  That is super-helpful (i did not know about that setting) and does not help at the same time.  The output from the file is not very helpful, though:

                                   

                                  20/12/2011 13:41:27:       Error: Cannot open file '\\filepath\test.txt'

                                  20/12/2011 13:41:27:       General Script Error

                                  20/12/2011 13:41:27:       Execution Failed

                                  20/12/2011 13:41:27:      Execution finished.

                                   

                                  Still convinced this is a permissions issue, just can't put my finger on it.  The only thing i can think of is that the service account needs 'Full Control' permissions on the network folder instead of just read and write, but i can't think why that would make a difference.

                                   

                                  Firewall issue?  Clutching at straws, maybe?

                                    • Re: Accessing network Excel files using Qlikview server

                                      Same problem here. Running Server ver 10.0 64Bit. Runing in debug on the server in Client and we can access network files all day long. Same qvw as a scheduled task fails every time with the same error you are getting.

                                       

                                      What version Server are you running?

                                       

                                      -Mark

                                        • Re: Accessing network Excel files using Qlikview server

                                          Mark/everyone,

                                           

                                          I just solved this issue this morning.  It turns out that read and write access is not enough for the Qlikview service account on the network folder.  I applied 'Full Control' permissions to the user on the network folder and bam - my tasks started running in QEMC perfectly.

                                           

                                          To be honest, putting 'Full Control' on was just a bit of a punt, not sure why it fixed things.  But very relieved to have solved this problem!

                                           

                                          FYI, we are running Server 9.0.7646.9 here.

                                           

                                          Many thanks to everyone who helped out in here!

                              • Accessing network Excel files using Qlikview server

                                Rob Wunderlich wrote:

                                 

                                1. Do you have document logging on? If so, is there any error in the doc log or is it just that no files are found in the directory?

                                 

                                2. Is the distribution service running with the same account as when you tested with desktop?

                                 

                                3. Is the desktop you tested with 32bit or 64bit?

                                 

                                -Rob


                                1. I put Debug Logging on and ran the load again so it could fail and i could get the most information.  Not sure which files i need to look at though.  Is it the log files in the Qlikview Distribution Service installation folder?

                                 

                                2. The Distribution Service is running with the same network Qlikview service account as all the other services.  This account definitely has access to the folders containing the Excel files, as i am able to run the extract perfectly with Desktop version installed on the server, and logged in as the Qlikview service account.

                                 

                                3.  Laptop testing was 32bit.  But the test with Qlikview locally on the server was done with 64bit desktop.

                                 

                                It certainly seems like a permissions issue of some kind, but struggling a bit where to look to diagnose.