Skip to main content

To import Excel sheet into SharePoint list , We can use Query Excel Services Action with Collection operation . 

Requirements : 

1. Excel file has "No" Null columns.

2. SharePoint List

3. Controls : Set Variable, Query Excel Services, For Each, Collection operation, Create item.

 

Step 1 :

Set Variable of Max Row needs to be imported.

 step 2: 

Use Query Excel Services Control as below 

 

Use credentials to login to site , Workbook path should be an absolute path with sheet name 

Cells to retrieve is from and to rows  store in collection variable.

 

step 3: For each loop with run if and collection operation and create item 

Foreach :

Target collection is Unique Column , Get the items and store in item till max number of rows (Index)

 

RUN IF:

Run  the section if it is not empty

 

COLLECTION OPERATION:

 

Get the column collection and get the items and store in variable 

 

At last create an Item using Create Item operation :

 

 

 

Finally upload the Excel with out any empty columns ( in case null fill " -" or something) and run the workflow . 

you will get the result in the list as follows

I am having troubles with the username and password. I used my personal windows account and pw and it worked for a couple of tries and then I started to get "The request failed with HTTP status 401: Unauthorized". 

 

Can you please help me with this? Which type of user must I add? All I am trying to do is to query an Excel workbook that was uploaded to a document library that is located on the same site as the workflow.

 

Thanks,

EDIT: Figured it out. In my case, turns out that I needed to use my username with the domain I am in.  You can get it using the "set user" in cmd, then you use the USERDOMAINUSERNAME and done.

hi Yogesh,



 



Needing your help.



 



I have a spreadsheet with 11 columns importing into a list using query excel services but only the first column is getting updated in the sharepoint list. 



 



Can you please confirm:



1. if the Create Item needs to be inside or outside the Run If loop?



2. if the Create Item needs to be inside or outside the For Each loop?



3. Does Each Collection Operation require a separate Update Item after the first Collection Operation is followed by Create Item?



 



Anything wrong you can spot with my structure below? 



 



The Query Excel Services are working ok, I've validated this by testing. The data types also match the SharePoint list column. 



 





 



Thank you !



 



John



 



 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings