Insert Data into sharepoint List

[Anonymous]

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.

[AIjel]

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]

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

 

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