1

Currently, I have a custom list in SharePoint with ~500 Items (or rows) and ~40 fields (or columns). The list has been customized to include things like Conditional Formatting, custom dropdowns, shading, etc.

I'd like to take the same custom list, remove the data that is in it currently, and populate the list with new data (another ~500 Items).

See below for some things I've tried so far: 1. I tried saving the current Custom List as a Template and then copying and pasting in new data. Unfortunately, this did not work because several of the fields I needed to copy and paste in included special characters and line breaks. Because of this, SharePoint wouldn't let me copy and paste all of my data and instead I needed to enter the data a couple cells at a time.

  1. I tried opening my Custom List in Access and doing an import in Access from an Excel file, but Access kept disconnecting from the SharePoint site and then couldn't reconnect. Access was also very finicky with the Excel file I uploaded - I ended up needing to download an export file from SharePoint, make my edits to the Excel download from SharePoint, and then save that file and import through Access.

  2. Creating a new list with a bulk upload from an Excel list and then recreating my Custom List from scratch. This works, but is very time consuming.

Any ideas on how I can easily reuse the same Custom list month over month?

Thank you!

  • Do you have any PowerShell knowledge? This is the route I would go, personally. I can provide some guidance on that front but you'll need to be comfortable running PowerShell and probably have administrator access to your machine in order to install the necessary PowerShell modules for it to work. – Robin Jan 17 at 19:10
  • Thanks for your response Robin! Unfortunately I don't have any PowerShell knowledge and I doubt I'd be able to get administrator access. I appreciate the idea, though! – Mrazmerized Jan 17 at 19:41
1

Good day!

If I understand your requirements correctly, you wish to:

  • On a monthly basis, remove the list content (saving the existing data via a list template).
  • Re-populate the list with new data, approximately 500 rows worth with 40 columns of data.

From your requirements, I see two options.

Option 1: Custom Coded solution

Option 2: Third Party tool such as ShareGate.

For Option 1, perhaps something client side such as JavaScript that triggers REST web services can do the trick. The issue with a client side approach is you would need a way to read that the data that you will populate in. A back end .net solution which is more complicated would be better suited to read data from a file.

For Option 2, I would recommend a SharePoint migration product such as ShareGate. Our company uses ShareGate which is a migration tool that can mass import and export data from SharePoint sites. ShareGate supports migrating data from excel files as well. You can get a free trial and see for your self. Let me know if that helps.

  • Thanks Kyle! Ideally this would be a process that someone without much coding knowledge would be able to do it, so I think Option 1 is out of the question. Option 2 is interesting - you've used ShareGate to do similar things? I'm unfamiliar with ShareGate but I'll look into it more. Thanks! – Mrazmerized Jan 17 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.