cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Nintex Newbie

Data exchange with Excel?

Hi all,

 

I need to utilize a lot of data which is excel-list-based. Unfortunately within Forms the possibilities are limited. So I thought of doing an excel-list to sharepoint-list export with a workflow once a night to have the data sharepoint-list-based.

 

I tested it with an excel table with 10 colums x 500 rows.

Generally this is working:

start loop

Using Excel services to get data of first (second, third, etc) row, save it to a collection, loop through the collection, get data, create new item within sharepoint-list.

End loop

 

But workflow is quiet slow. The first item in the sharepoint-list is generated immediately, but the next rows need 1-2 minutes each. Is that because of the loop??

 

Taking this speed into acount, it is not possible to work with a lot of Excel data, right?!

 

Any advice?

 

Cheers

mai-kel

 

 

 

Labels: (2)
0 Kudos
Reply
2 Replies
Highlighted
Automation Master
Automation Master

Re: Data exchange with Excel?

@mai-kel .....Are you using while loop or for loop?

 

I haven't worked much with query excel service but based on what I know, how about the below approach?

 

  • Using excel service you get all 5000 cell values
  • Now running for loop on the collection and a counter (if selection is A1:B# then value stored is like A1, A2, A3, B1, B2, B3), create 10 different collections i.e. each collection for each column 
  • Now using first collection and index value, run another for loop and inside this for loop, using collection operations on remaining 9 collections and using index get the value and create an item.
  • Make sure you put commit pending changes action after your create item action.

 

 

 

0 Kudos
Reply
Highlighted
Nintex Newbie

Re: Data exchange with Excel?

Start Loop (every row)
Get xls data (10 colums) via excel service into collection
Start loop (every item within collection)
Write data into one of ten variables
Stop loop
Create item (10 columns = 10 variables)
Stop loop

And as I already said, the first item is created instantly, but all other items need 1-2 minutes each. So I really believe it has somethinig to do with the loop.

I will try to disable save looping, maybe this will help.

Cheers
mai-kel
0 Kudos
Reply