Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
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