Sharepoint Upload Excel to Add List Items

three means to import Excel to SharePoint

Posted on June 27, 2018 | Lists

Custom List Excel

A while dorsum, I published a post where I explained how to consign SharePoint lists from SharePoint to Excel. Today, I am going to comprehend the opposite of this – how to import Excel to SharePoint. Say, you have an Excel list/table and want to bring it to SharePoint (because SharePoint custom list kicks ass!). How do y'all reach this? Well, let me explain. You accept a few options to choose from!

July 2020 Update – READ THIS FIRST!

Below techniques describe the "onetime way" of importing Excel to SharePoint. If you are in SharePoint on-premises, below tips are the but options you have. However, if you lot are in SharePoint Online (Function 365), we now take a new and easier mode to import Excel data, which would be the easiest and preferred pick to use. Please reference this most contempo post for instructions.

Selection one: Import using Quick Edit

The start pick assumes that you already have a SharePoint listing with all the corresponding columns, and but need to copy/paste the content from Excel to SharePoint.  Hither is what you need to practice to achieve this:

1. Build out all the columns exactly as they appear in Excel
2. Brand sure columns in a view are in the same guild every bit in Excel (otherwise when y'all do copy/paste, you will go a mismatch)
3. If you lot are taking advantage of metadata (drib-downs), make sure to populate all the possible choices into respective columns (otherwise, when y'all paste, and pasted text does non match the driblet-down pick, the row will be rejected)
4. Open up your list in Quick Edit (Datasheet view for those in SharePoint 2010)
5. Copy the rows you want to copy from Excel
6. Paste into Quick Edit View
7. Yous will notice the rows existence pasted one past one. You might demand to requite it some time if you take lots of rows. In instance y'all go a mismatch in data beingness imported, you lot volition get a scarlet alert just like in the image below
8. When all data is successfully pasted/mismatches resolved, just click on Get out Quick Edit. You are all set!

Pros

• Allows to accept advantage of metadata and different types of columns in the existing list (i.due east., Text, Choice, Engagement, Currency, etc.)

Cons

• It takes time to prepare the list and view earlier you lot can import/paste
• Might not be a good technique for importing large data sets
• Error handling capabilities are limited (if you have a row that fails, might take time to effigy out the issue/field causing the issue and you might need to redo the whole import)
• Import volition not work if you gear up metadata drib-downward using Term Store (managed metadata). Click here to read more near this issue

Pick 2: Import Spreadsheet Web role

Another pick that is available to import Excel to SharePoint is to apply a Spider web Function chosen "Import Spreadsheet." These are the steps to follow:

1. Gear Icon > Add an App
2. Scroll down to Import Spreadsheet App. Click on it.
3. On the next screen, give your new app/list a name, then choose an Excel file. Click Import
4. Y'all will now notice an Excel file open up with a pop-up window where you lot need to select a range of cells to import. In one case you choose the range of cells in the popular-upwardly, click Import.
5. The table will now be imported to SharePoint. It will go ahead and create a custom list with proper cavalcade headers and values.

Pros

• Relatively easy to utilize
• Does not require much prep time like Option 1

Cons

Choice iii: Import straight from Excel

Some other great option is to import information right from inside Excel. This is what you have to practice:

1. In Excel spreadsheet, select the data range and Format it as a Tabular array
2. While still in Excel, highlight the table, then cull Export from the pinnacle ribbon, then Export Table to SharePoint list
3. From the pop-upward that appears, specify the URL of the site you are importing to, then give the list a name, then striking Next. You lot can also (optionally) check the box next to Create a read-just connection to the new SharePoint list. What it does is make your Excel physically linked to SharePoint listing. Any changes in the SharePoint list volition propagate down to Excel when yous refresh the data there.
4. On the next screen, y'all volition run across all the columns with corresponding recognized column types (Currency, Date, etc.). Just click End
5. You lot should be getting a success bulletin/pop-up.
6. You should now see your list created in SharePoint. It will default to Datasheet view, just click Stop to view it as a normal list.

Pros

Aforementioned as Choice 2

Cons

• Same as with Pick 2, some drop-downwardly choices are recognized equally Text.
• The listing created is a archetype list and not a mod one (for those who are in SharePoint Online)
• By default, the listing opens upwards in Datasheet view. You e'er have to click Finish Editing to see a "normal" listing

Need SharePoint Help?

Hourly consulting, training and configuration services are available

Learn More

carrollsaineve1990.blogspot.com

Source: https://sharepointmaven.com/3-ways-import-excel-sharepoint/

Share this post