Complete records are not getting moved from database to sharepoint

[swapsingh2712]

Hello All,

I'm trying to move the data from database table to SharePoint list. But Alteryx is not moving the entire records.

So for now my DB table has roughly 900 records, but it's only moving 420 records and it's throwing attached error.

Could you please advise any solution?

I've tables where we've more then 1000 records and by the use of alteryx we want move those tables to SharePoint list

Please help

[DanielG]

@swapsingh2712 -- I have had issues similar to this, what I think COULD be occuring is that either SharePoint is "timing out on you" or there is one row with a bad piece of data in it that the SP list can not handle so it is shutting down.  Check row 421 to see if there is a bad character or bad data type.

I am not expert here, but that is what I'd try first.

Hope this helps.  :)

[danilang]

Hi @swapsingh2712 

We've had timeout issues before writing to SharePoint lists and we've had to use an iterative macro to write the records in batches.  

The D Input is your data and the S input is the batch size.  The filter select the first batch of records and sends them the browse tool.  The rest of the records are returned to the D input.  On the next iteration, the second batch of records are sent.  This continues until there are no records left in the input or the iteration limit is reached.  the limit is set to 100 so that should be enough with a reasonable batch size

You have to play with the batch size to make it work.  To err on the side of caution, I'd start with 200 records per batch in your case.  Even with your largest set it will still complete in ~6 iterations

Dan

[VojtechT]

Hi @swapsingh2712 ,

there is a new version of the Sharepoint List Output tool currently in Beta program. You can give it a try to see if that solves the issue or the behaviour is the same, in which case I would assume there are some prohibited characters. For example I had similar experience when List Input gave me records including html tag, but when I tried to write the same into List Output it failed due to the presence of html code. 

[swapsingh2712]

Hello @danilang 

Thank you so much for your help. Actually I'm not very much familiar with iterative workflow. so Just wanted to ask, How can add the record size in the batch, as my input is coming from the database which contains around 850 records

[danilang]

hi @swapsingh2712 

The record size is passed into the macro in the S input.  In the main workflow this set in the Batch Size input tool.  Change this number to 200 and see if this works.  If not decrease to to 100, then 50, then 25, etc to find the largest batch size that Sharepoint will accept.  

Dan

[swapsingh2712]

@danilang I've tried using the different batch sizes like 200,185,150,100,50,25. But none of them copied the entire records to the sharepoint. Here's the list of batches and iterations I tried

batch                        size records

100                       2 iteration run (128 items added to sp list)
25                         5 iteration run (53 items added to the list)
50                         3 iterations run (128 items moved)
150                       1 iterations run (278 items moved)
185                       1 iterations run (243 items moved)
200                       1 iterations run (228 items moved)

Not able to move entire records (830 records). Please advise.

I've attached the screenshot of the macro which I've created