Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Insert Data into sharepoint List

All,

 

I have an excel sheet which is generated through Talend job, i would like to write the data from excel sheet into Sharepoint list.

 

I have seen many forums, but i didn't get any useful information. Is there any whitepaper published by Talend to handle this kind of webservice calls.

Labels (2)
2 Replies
AIjel
Contributor
Contributor

Hi,

Unfortunately Talend doesn’t offer connector for reading/writing data from/to SharePoint lists! That’s a petty.

 

Most answers to this question are about using (the horrible) REST API via TrestClient Thttprequest or via tSOAP. But i never succeeded ! Some people did (without sharing enough details) but I am quite sure it wasn't with SharePoint online, where the main issue is about authentication!

 

AIjel
Contributor
Contributor

I have been struggling for a while with this issue! And good news I have a workaround!

Let me share : the idea is to not try to write the flow on SharePoint directly, but rather do it on a macro enabled Excel file!

 

Step1: Find the LISTNAME. To do so:

1.      Go to SharePoint and export the list to Excel

2.      You will get a query.iqy excel file

3.      Open the excel file

4.      Open the connection properties Data->Connections; select the connection; click on properties; choose the Definition tab.

5.      Under Command text field you can see a code similar to the following

<LIST>

<VIEWGUID>627A4BFC-E5EC-48FD-824E-B50177207663</VIEWGUID>

<LISTNAME>{A5DD8F4A-7940-4F29-9212-37A341190000}</LISTNAME>

<LISTWEB>https://xxx.sharepoint.com/teams/yyy/_vti_bin</LISTWEB>

<LISTSUBWEB></LISTSUBWEB>

<ROOTFOLDER></ROOTFOLDER>

</LIST>

6.      Copy the LISTNAME ‘A5DD8F4A-7940-4F29-9212-37A341190000’ and keep it

 

0695b00000UxtjYAAR.png 

 

 

 

Step 2: Create a macro enable Excel file let’s call it MyList.xlsm

1.      Create a worksheet called ‘MyList’

2.      Add the following 2 macros to VBA module

3.      Set in src(1) the LISTNAME you saved from Step1

                                                                                                                   

Sub ImportSharePointList()

   Dim ws As Worksheet

   Set ws = ThisWorkbook.Worksheets("MyList")

   Dim src(1) As Variant

   src(0) = https://hpe.sharepoint.com/teams/cmsdata/_vti_bin

 

   'DONT FORGET TO REPLACE YOU LISTNAME IN THE FOLLOWING LINE

 

   src(1) = "A5DD8F4A-7940-4F29-9212-37A341190000"

   ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")

End Sub

 

Sub UpdateSharePointList()

  Dim ws As Worksheet

  Dim objListObj As ListObject

  Set ws = ActiveWorkbook.Worksheets("MyList")

  Set objListObj = ws.ListObjects(1)

  objListObj.UpdateChanges xlListConflictDialog

End Sub

 

4.      Save the file on the hard disk of Talend machine

 

 

Step 3: test

Run the ImportSharePointList macro to get list data from SharePoint to Excel

And

Run the UpdateSharePointList macro to get list data from Excel to SharePoint

 

 

Step 4: automate in ETL

You can do that now automatically by calling the macros from Talend

1.      Create 2 VBS files on your file system. Let’s assume you put them under the location defined by the context variable ‘context.MacroPath’

a)     ImportSharePointList.vbs

   Dim App, args, wb, fpath

   Set wb = Nothing

   Set App = CreateObject("Excel.Application")

   Set args = WScript.Arguments

   fpath = args(0)

      if fpath <> "" then

             Set wb = App.Workbooks.Open(fpath)

             wb.ImportSharePointList

             wb.save

             wb.Close

      end if

   Set wb = Nothing

   Set App = Nothing

And

b)     UpdateSharePointList.vbs

   Dim App, args, wb, fpath

   Set wb = Nothing

   Set App = CreateObject("Excel.Application")

   Set args = WScript.Arguments

   fpath = args(0)

      if fpath <> "" then

             Set wb = App.Workbooks.Open(fpath)

             wb. UpdateSharePointList

             wb.save

             wb.Close

      end if

   Set wb = Nothing

   Set App = Nothing

 

2.      In your ETL process, add (where you want to call each macro) a tJava with the following code by replacing THEMACRO.vbs

by ImportSharePointList.vbs

or UpdateSharePointList.vbs

we assume that context.filepath contains the path to the macro enabled Excel MyList.xlsm

 

try{   

   Process p = Runtime.getRuntime().exec("cmd /c start /wait " + context.MacroPath.replace("/","\\\\") + "\\THEMACRO.vbs " + "\""+ context.filepath + "\"");

   p.waitFor();

 

}catch( IOException ex ){

   //Validate the case the file can't be accesed (not enought permissions)

 

}catch( InterruptedException ex ){

      System.out.print(context.filepath + " Refreshed\n");

   //Validate the case the process is being stopped by some external situation    

 

}

 

that's it!

I hope it helps

 

Ammar Ijel