17 Replies Latest reply: Apr 18, 2013 7:47 AM by Karthikeyan Subramaniam RSS

Consume Spreadsheet from Sharepoint

chris cammers

I have to be missing something and it is probably a very simple question.

I have a spreadsheet stored in sharepoint. I want my script to load data from that sheet. What is the syntax to get the sheet? Do I have to write a macro to download the file locally then consume the data from there?

 

Thanks

Chris

  • Consume Spreadsheet from Sharepoint
    chris cammers

    Figured it out, you have to use the UNC path. To find the UNC path go into sharepoint and from the actions menu select open in Windows explorer.

    • Consume Spreadsheet from Sharepoint
      chris cammers

      One more note,

      In addition to using the unc path you also have to install the webclient service on your server or the server will not be able to reload the document. In windows server 2008 this service is not installed by default. You have to add the User experience feature to the OS since it is not installed by default.

      Happy Qliking

       

      Chris

      • Consume Spreadsheet from Sharepoint

        i was under the impression that the files in sharepoint are stored in sql server. I saw there's an option to pick "web files..." but have not been successful pointing to the http url of the xlsx file i have on sharepoint. Have you been able to use this feature?

        • Consume Spreadsheet from Sharepoint
          chris cammers

          So the sheet that I am using has this URL

          http://servername/sites/thefolder/thesheet.xlsx

          you can find it by doing a properties in the list where you find it.

          you need the UNC path for qlikview to open it

          from your sharepoint in the actions menu click "open in windows explorer"

          now from the Windows Explorer screen right click the file you want and select properties the location is the unc path.

          When you are building your qvw you will want a table file and then enter the unc path as the path for the sheet, don't forget to add the file name if you copy the path from the properties dialog.

          The most critical piece of this is the user experience feature on your server and the service account running your Qlikview servers needs to have permission to read the file. If the service account does not have permission you will find that your qvw will reload perfectly on your desktop but when the server tries to reload it will fail.

           

          Good luck I hope this helps

           

          Chris

           

           

          • Consume Spreadsheet from Sharepoint
            chris cammers

            Ok another wrinkle in this

             

            I recently upgraded to Windows 7

            When I try to load my spreadsheet from sharepoint Qlikview cannot open it until I have opened it. Once done I can load the sheet even if is no longer open. I don't get it.

            Is there something about my new OS that is weird? Anybody seen this problem?

            • Consume Spreadsheet from Sharepoint
              chris cammers

              I am not sure if this is a Windows 7 thing or if it is a thing with our company's image that was loaded on my machine,

               

              Once again the WebClient service needs to be running, just like the server. In my case it was installed but set to run manually so I could not load the spreadsheet from qlikview. I think this will also improve the speed of opening the sheet from my browser too, but I have to test that.

              • Consume Spreadsheet from Sharepoint
                dkusek01

                This thread is a little stale but possibly someone who is following can help.

                I would like to read data from an xls file stored on our shareponit site. I am able to accomplish when developing but when run the job through publisher it fails. I am guessing it is a permissions issue. I granted the account running the QVS and QVP services all rights to the sharepoint it would need but I still have a failure.

                We are running 8.5 on a windows 2003 64 bit server, webclient service is installed and started.

                 

                Any ideas on why this is failing, is this a permissions issue?

                 

                Thank you

              • Consume Spreadsheet from Sharepoint
                Steve Rimar

                 

                Chris - We have the same exact problem in our environment and all QlikView services run under a system account which has full permissions to the SharePoint location. We tried this with both SharePoint 2007 and 2010. We also did a trace on the QlikView server and when the Publisher tries to reload the document it gets a 401 when trying to execute an HTTP OPTIONS command to our SharePoint server.

                Manually logging on to the server as the service account that runs publisher and navigating to the SharePoint UNC path provides an authentication prompt. After authenticating with the service account and password we're able to open the source file we're trying to reload from.

                Any other ideas? DId you do anything else to make this work? Our server is Windows 2003 and webclient is running.



              • Re: Consume Spreadsheet from Sharepoint
                qlikviewforum

                Hi All

                 

                The particular sharepoint where I am going to fetch the data needs authentication. Could you please let me how that can be done please.

                 

                Created the new thread for the same below.

                http://community.qlik.com/message/249859#249859

                 

                Thanks,

                qlikview forum

      • Re: Consume Spreadsheet from Sharepoint
        qlikviewforum

        Hi chriscammers,

        I have checked in my server but WebClient is not been installed in the server. May I know what is the next step once the WebClient has been installed? So that I can do the same once the WebClient has been installed.

         

        Thanks,

        Qlikview Forum

    • Re: Consume Spreadsheet from Sharepoint
      qlikviewforum

      Hi chriscammers,

      I am not able to find "open in Windows explorer" in actions. But I have managed to get the path after exporting to spread sheet. So could you please let me know what is the next step once I get the path?

       

      Thanks,

      Qlikview Forum

    • Re: Consume Spreadsheet from Sharepoint
      qlikviewforum

      Hi Chris

       

      We are waiting for your reply. Please help!

       

      Thanks,

      qlikviewforum

  • Re: Consume Spreadsheet from Sharepoint

    Hi,

     

    Using UNC paths fails for exampel in 64 bit Windows Server 2003 (and possibly other 64 bit clients)

     

    Why don't you download the fiel from SharePoint ( wich is a glorified WebDAV ) using the open-source cURL client ? Mind the %% escape needed in batch files

     

     

    rem download

    curl --ntlm -u "DOMAIN\USER:PASSWORD" --url "https://workspaces.company.com/site/Shared%%20Documents/file.txt" -o file.txt

     

     

    -Alex

  • Re: Consume Spreadsheet from Sharepoint
    Karthikeyan Subramaniam

    All,

     

    Where i can get web client service installation.

     

    Regards,

    Karthi