Insert Data into sharepoint List

Anonymous — Thu, 23 Jan 2020 22:58:50 GMT

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.

Re: Insert Data into sharepoint List

AIjel — Sat, 27 Aug 2022 20:39:25 GMT

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!

Re: Insert Data into sharepoint List

AIjel — Sat, 27 Aug 2022 20:43:01 GMT

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>

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

</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

topic Re: Insert Data into sharepoint List in Talend Studio

Insert Data into sharepoint List

Re: Insert Data into sharepoint List

Re: Insert Data into sharepoint List