08-16-2018 07:36 PM
08-16-2018 07:36 PM
I'm working on two major projects right now, both of which will require that users import data from Excel spreadsheets into SharePoint lists. The people doing this are NOT IT professionals, so the simplest method is to use Quick Edit in the list. This works, but creates duplicate items in the list (at least two, sometimes more), which is an absolute show-stopper. It also takes forever (~10 minutes to paste about 290 rows - which is not a lot - although, in reality, it's pasting 580 rows because of the dupes).
This is not progress! Datasheet mode in SP2010 - while limited to Internet Explorer, was much more reliable and NEVER duplicated data on me. FWIW, I've tried this is Google Chrome, Firefox and Edge - same issue in all.
I know it's possible to import list items using PowerShell, but how many business users do you know that are going to tolerate a command-line solution (let alone that can be trained to use such a solution). This means that instead of handing these projects over to the end users, I (or someone like me) will pretty much always have a hand in the maintenance of the applications. I don't know about all of you, but my time is better spent helping to solve new problems than continually hand-holding users through something they should be able to do (if Quick Edit actually worked like it's supposed to).
08-17-2018 01:17 AM
08-17-2018 07:32 AM
08-17-2018 09:23 AM
Bearing in mind this fix is not going to happen soon: Have you consider to use other alternatives more friendly for your users? One that come to my mind is to use Flow to insert excel data into the list..you could create the Flow and instruct the users to use it.
The Excel actions in flow ("Get tables" and "get rows") are potentially useful, except they, too, have limits (255 rows, I think, which is NOT a lot of data!) Also, they only work with Excel files stored locally or on OneDrive for Business. I could store a sample file on my OneDrive, set up the flow, but then for someone else to use it, I need to also share that file with them, or walk them through re-building the Flow themselves. There really needs to be a set of similar actions that run against Excel files stored in SharePoint.
08-17-2018 09:31 AM
@Chris Gullicksen wrote:
I usually recommend pasting in about 100 rows of data at a time and then teaching the users to watch the little pre-loader validate and save each row after their paste. They also need to know that the order of the columns, formatting and required columns in the SharePoint list should be something they should be aware of to reduce any errors doing this work.
The testing I did yesterday with half that data (~50 rows) resulted in duplicates. And, yes, I was watching the pre-loader. In reality, though, how many end users do you support that will actually chunk their data like that and be patient. Those types of people are few and far between in the Higher Ed market (we're a University).
As it turns out, I did find a workaround: go to Classic Mode. The Quick Edit function in SP2013/Classic mode - in the 10 tests I did - did NOT create duplicates. It still took a long time to import the items, but it did so cleanly. Maybe in a couple years, Microsoft will have all of the bugs worked out of "Modern" lists, libraries and pages, but until then, we need to keep telling people to use Classic, which is frankly embarrassing. "Hey, check out this new SharePoint...doesn't it look great? Oh, but for this, this and that other thing, you need to switch to Classic mode." I'm glad we get to pay Microsoft for the experience of beta testing their products.
08-17-2018 06:46 PM
Create a PowerShell script that runs every 5 minutes (or a time frame that's tolerable) as a scheduled task that looks for new spreadsheets in a library that users put the spreadsheets. The PS Script will import the data and then mark it as "Imported" by setting a flag in the library so it does not get imported again.
08-22-2018 10:16 AM
08-22-2018 10:29 AM
Actually, @Chris Gullicksen, I spoke a little too soon. The same problem appeared in Classic Mode as well. Some of my colleagues are seeing the same behavior in some form or another (not all items are duplicated; different thresholds for how many can be imported with ANY dupes; etc.).
So, Quick Edit still does need some attention in order for it to work as reliably as Datasheet Mode in SP2010. I'm shocked that I'm reporting that ANYTHING in SP2010 is more reliable than SPO.