3 Replies Latest reply: Nov 28, 2014 7:32 AM by jagan mohan rao appala RSS

    How to extract data from sharepoint?

    Dhananjay Jadhav

      Please suggest me how to import data through the sharepoint in qlikview

        • Re: How to extract data from sharepoint?
          alexander korsikov

          load script in qlik sense  the same as in QlikView

          I find for you some matrial in community

          Integrating Sharepoint with Qlikview

          and other info

          http://community.qlik.com/search.jspa?q=sharepoint

          • Re: How to extract data from sharepoint?
            Mark O'Donovan

            Hi Dhananjay,

             

            Here is a powershell script I used to get data from a document library in office 365 sharepoint into a CSV file.

             

            I run this script from a qlikview loadscript using the execute command, then I read the csv file into my qlikview document.

             

            If you are not using Office 365 this link might help:

             

            sharepointrelated.com/2014/11/11/download-all-content-in-a-site-collection

             

            Hope it helps.

             

            Mark

             

            www.techstuffybooks.com

             

             

             

             

             

             

            $loadInfo1 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")

            $loadInfo2 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")

             

            $destfolder=$args[0]

            if($destfolder.length -gt 0){

                $destfolder = $destfolder.Trim()

            }

             

             

            if (Test-Path lastreload.txt)

            {

                $PreviousLastReload = Get-Content lastreload.txt

            }

             

            $loadInfo1 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")

            $loadInfo2 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")

             

             

            $webUrl = "url of sharepoint"

            $username = "email addresss"

            $plainpassword = "password"

            $password = "password" | ConvertTo-SecureString -AsPlainText -Force

            $sourceListName = "hireman_documents"

            $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($webUrl)

            $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)

             

            $web = $ctx.Web

            $list = $web.Lists.GetByTitle('hireman_documents')

             

            $query = New-Object Microsoft.SharePoint.Client.CamlQuery

             

            #Type=''DateTime'' IncludeTimeValue=''TRUE'' 2014-11-11T10:30:00

            #2014-11-1110:30:00

            #2014-11-10 11:33:40

             

            Write-Host PREVIOUS $PreviousLastReload

             

            if(!$PreviousLastReload){

             

                Write-Host NO DATE SAVED

                $PreviousLastReload = "2014-01-17T09:50:31"

                $query.ViewXml = "<View><Query><Where><Geq><FieldRef Name='Modified' IncludeTimeValue='TRUE' Type='DateTime' /><Value IncludeTimeValue='TRUE' Type='DateTime' >"+$PreviousLastReload+"</Value></Geq></Where></Query></View>"

             

            }

            else

            {

                Write-Host Use saved date $PreviousLastReload

                $query.ViewXml = "<View><Query><Where><Geq><FieldRef Name='Modified' IncludeTimeValue='TRUE' Type='DateTime' /><Value IncludeTimeValue='TRUE' Type='DateTime' >"+$PreviousLastReload+"</Value></Geq></Where></Query></View>"

            }

             

             

            $LastReload = [datetime]::Now.DateTime

             

            $query.ViewXml | Out-File querylog.txt

                         

            $items = $list.GetItems($query)

             

            $ctx.Load($list)

            $ctx.Load($items);

            $ctx.ExecuteQuery()

             

             

            #Write-Host $list.Title $list.ItemCount

            #Write-Host *************************

            #$items |% {          Write-host "$($_["Job_x0020_Number"]), $($_["FileLeafRef"]) "}

             

            ## Save the details of the changed documents to a CSV file

            $items |% {

                $obj = New-Object PSObject -Property @{

                    job_number = $($_["Job_x0020_Number"])

                    document_name =  $($_["FileLeafRef"])       

                    modified_date =  $($_["Modified"])       

                    created_date =  $($_["Created"])       

                    modified_by =  $($_["Editor"]).LookupValue.ToString();       

                    created_by =  $($_["Author"]).LookupValue.ToString();       

                    document_path = $($_["FileRef"])       

                    file_url = $weburl + $($_["FileRef"])

                    document_uncpath = $serveruncpath + $($_["FileLeafRef"])       

            }   

                Write-Output $obj

            } | Export-CSV doclist.csv -Force

            • Re: How to extract data from sharepoint?
              jagan mohan rao appala

              Hi,

               

              Check this link hope it helps you.

               

              Reading Data from Sharepoint lists into QlikView

               

              Regards,

              Jagan.