Forum Discussion
Import excel into existing list (with data already in it)
RRMX01 if you go to Edit in grid view on your list you can paste in data from an excel spreadsheet.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
nestorph3010 Are you in Internet Explorer? I couldn't get it to work in Edge, but was able to paste a few hundred lines by opening it in Internet Explorer. Good luck!
I figured it out!! I was struggling with this and really needed to make it work.
It depends on how you click on the cell. If you have the curser flashing in the cell to enter text then it will just paste whatever you copy from Excel as text. However, if you want to copy several rows or items from Excel, you need to click on the cell in grid mode. To do that, if you have the curser flashing, click on an area outside the list.
Step by step:
1. Click on "Add new item"
2. Select the cell you want the rows to go to, or the cell in column A to enter all columns
3. The cell will select and your curser will flash in the first column
4. Click below that row and under the end of the list
5. Your selection will become still the same cell but without the flashing cursor
6. Paste
7. If you had several rows whether just one column or several it will paste all the rows as new rows into your list.
Tested and verified. Works like a charm. See a few screenshots below.
Text entry: (not what you want)
which leads to the following:
No cursor selection should look like so:
And leads to the following:
Notice the "wrong" pasted option at the top and the correct pasting underneath that.
Browser: Chrome
View used: SharePoint Modern View
OS: MacOS 14
- Wow great answer for the question, i had the same problem with this topic. After reading your solution, i had tried, and it work very well for me. Thank you for help. You are master, man.
yyarin104 OMG you saved the day, thank you for the detailed walk through, I was losing my mind on this one!
- It's great to see that you can paste multiple rows in without using IE/IE mode, esp. since SharePoint is going to stop accepting input from IE in early 2023. Here's a quirk I ran into with a list that has content management enabled and has two different content types: Initially I was getting an error for required fields from content type A, even when I had set the default new content type to B. As a workaround, I created a view that corresponded to all of the editable columns of content type B, but was getting an error that my view was missing required fields. As a test, I identified the required columns in content type A that did not also exist in B, and I set those to not required, and after a ctrl-F5 force refresh of the new view, I was able to import multiple rows with no issue. My plan is to import all rows of content type B, then switch the default content type back to A, then import all type A rows. I will also need to disable any required columns that only exist in type B for the import of the type A rows. This is a crude workaround to being unable to choose a content type when adding items in grid view. PowerAutomate would probably be the more elegant approach but sometimes quick and dirty > elegant for one-offs. For PowerAutomate, you could specify the ContentTypeID column, and get the right ID of the lists's sub-version of the site content type ID, found in the URL when in the "Edit All" mode of editing an item of the content type you want the ID for. For example: https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-pass-content-type-Id-from-PowerApps-to-SharePoint-List/td-p/437498.
You, yyarin104, are a gentleperson and a scholar. Thank you!
- I'm facing the same issue with moving data from excel to an existing list. The options presented above do not work for me in Edge or in Edge with IE enabled. I haven't tried Chrome. The list I need to update gives me an incompatible browser error when I try run Edge in IE mode. Any advice? This seems like it should be out of the box functionality. I don't understand why there just isn't an upload data from excel option. Very frustrating!
- Rebar, it's quirkier than I think it should be, but I've used it within the last month. I'm just using Edge (SharePoint stopped accepting connections from IE or Edge/IE-mode earlier this year), but Chrome should work as well. After switching to edit in grid mode, what I usually do is click in the add new item area to make that cell active, but if you see the "|" cursor, you then need to click anywhere else in blank space in the list, leaving the cell selected, but without the cursor actively displayed. Then paste the multi-row results from Excel (or similar) and it should work. Sometimes I have to click in and out a couple of times before it works.
- Pasting from Excel works for a few dozen rows, but gets very cumbersome with larger amounts of data. I have been able to insert larger data using Access, which can connect to the SharePoint list and allows you to write SQL-like insert queries.
When you import from MS Access, can you import to an existing SP list? I have imported from MS Access to create a new SP list but I want to import into an existing SP list so that I can define the columns before importing.
B-Jayne Leuterman Yes, that's the beauty of using Access. Create a blank Access database. From the External Data menu, choose New Data Source - Online -> SharePoint list. Paste the URL of the base of your SP site and select "Link to the data source". Select one or more lists. If any of your lists contain Person column types, Access may bring in an additional table called UserInfo that contains user profile data of what are called "site users". These are the users that have accessed your SP site at some point in time.
If your SP list contains lookup columns, Access should also bring in the source lists of those lookup columns. For example, if your SP site has a list called Products and another list called Warehouses, and Products has a lookup column "Warehouse" that points to the Warehouses list, then linking to the Products list from Access should also bring in a link to the Warehouses list into Access.
You can copy and paste data into these lists, similar to what people in this forum are describing about the SP web-based list interface. You can also write SQL-like queries in Access that insert data into the SP list from another Access table or other data source.
Note that these SP lists appear in Access like tables, but are actually directly connected to the SP list. Therefore, changes/insertions you make in Access will immediately update the SP list online.
BenHoffmann Have you tried with Power Automate? it should also be possible to do a for each loop
- I've heard that it can be done in Power Automate. It may just depend whether you're more comfortable using PA's GUI to construct the transfer of data, or writing SQL in Access. I prefer the latter.
