Skip to main content
Nintex Community Menu Bar

Data exchange with Excel?

  • November 26, 2019
  • 2 replies
  • 21 views
Nintex Partner
Forum|alt.badge.img+10

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

 

 

 

    2 replies

    Forum|alt.badge.img+12
    • kunalpatel
    • Scholar
    • November 26, 2019

    @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.

     

     

     

    Nintex Partner
    Forum|alt.badge.img+10
    • mai-kelNintex Partner
    • Author
    • Nintex Partner
    • November 26, 2019
    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
    Sidebar Buttons
    Nintex Community Home Contact Nintex Support Email Nintex Community Check Nintex Status
    Terms & ConditionsAccessibility statement