6

How can I import data from Excel into an existing Task List (or other list) for the various column types using the Datasheet View?

Improve this question

1 Answer 1

Reset to default
4

The key points are recognizing the column types and formatting the data appropriately in Excel before the attempting the import. Certain column types are not able to be imported in this way, such as the Multi-line Enhanced Text. Let me break it down for you. :)

Column Types

Must be formatted as follows:

-COLUMN-                | -FORMAT / COMMENT-
Single line of text     | Any Unicode string.
Multi-line, plain       | Any Unicode string; multiple lines can be wonky.
Multi-line, rich text   | Does not import or export well from/to Excel.
Multi-line, enhanced RT | READ-ONLY.
Choice                  | Must be valid choice.
Number                  | Numeric only.
Currency                | Numeric only; symbols ignored.
Date and Time           | ISO-8601 or per Column Settings (e.g. `2017-01-31` or `1/31/2017` (M/D/YYYY); must be delimited by either '-' or '/').
Lookup                  | Additional fields are READ-ONLY.
Yes/No (check box)      | 1/0, Yes/No, or TRUE/FALSE.
Person or Group         | <Name>;#<ID>;# (e.g. `Surname, Given;#111;#`); Importing a single user does not need the ID (i.e. `Surname, Given`).
Hyperlink or Picture    | Excel '=HYPERLINK' formula or Linked Text.
Calculated              | READ-ONLY.
External Data           | Unknown.
Managed Metadata        | Unknown.

Here are the Valid HTML Tags for the Rich Text fields.

Procedure

  1. Create a Datasheet View containing the columns you want to populate; excluding READ-ONLY fields (as indicated above). enter image description here
  2. Prepare and format your data in Excel as specified above; preferably using Excel Tables. enter image description here
  3. Open your list in Internet Explorer and change to Datasheet View (a.k.a. Quick Edit on SP2013+).
  4. Copy/Paste from Excel into the view you prepared in Step 1. enter image description here Final Result! enter image description here Notice the resulting indentation on lines 2 & 3 of the MLineRT column using Alt+Enter in the Excel cell for adding lines.

Important! Make sure that your Excel columns line up with the appropriate list columns and that you have validated all of the values beforehand.

Partial Updates

You can slice an dice your Datasheet Views to contain any columns you like and filter appropriately when working with subsets of the list. The trick is to sort by a unique column such as the row ID, then be sure that you have a direct correlation between the data showing in your view and what you have in Excel. You have to be extremely careful not to write data intended for one row to a different row. Sorting and ID mapping are key. Do this:

  1. Setup your view (with sort and filtering).
  2. Export your list to Excel and make the desired updates.
  3. Confirm everything.
  4. Copy/Paste.

PowerShell

I used the following script (must be run on the frontend server) for gathering data for Excel:

# Get User IDs (see Gotcha) needed for formatting the People or Groups columns.
Get-SPUser -Web "https://sharepoint.net" -Limit All
    | Select-Object @{Name="Import Object"; Expression={$_.name+";#"+$_.id+";#"}}
    | Export-Csv ("{0}\SPUserIDs.csv" -f [Environment]::GetFolderPath("Desktop")) -NoTypeInformation -Encoding Unicode

Here's the full list of SPUser Properties.

Gotchas

  1. Empty cells will overwrite existing data. For example, if you have an existing row that you wish to update only certain cells, you must copy only those cells.
  2. Person or Group column only allows for a single selection, but you're trying to import multiple.
  3. Person or Group column User IDs are unique to each Site Collection.
  4. Date is formatted incorrectly (see above).
  5. Lookup columns must have dependent rows loaded first. For example, if you have a Row 2 that has a lookup to Row 1, Row 1 must be loaded first, then refresh the page, and load Row 2. If you load them at the same time, the Lookup field on Row 2 will be empty.

Note: I am opting to not make this a wiki as I have personally invested numerous hours testing this info and would like to be rewarded for my efforts. Please feel free to add your own thoroughly tested answers. If I can verify them, I'll merge your answers (with [username] citation) to mine and give you credit... If someone could also confirm this answer in the Quick Edit view of SharePoint , , and , that would be most helpful. :)

Improve this answer
5
  • 1
    A note for SharePoint 2013+ is that you use the "Default" view style when using "quick edit/datasheet" views. as other styles don't always work as expected. :)
    – Chris G
    Sep 7, 2017 at 18:25
  • I tried in Sharepoint 2016/O365 and got nothing but I was using Edge as the browser. Switched to IE but now I get formatting error but can't pin point the issue. It is more than likely due to some hidden columns that show up when you export but not on the screen. I was just trying it since I'm trying to consolidate a bunch of list into one.
    – Siemoore
    Dec 14, 2017 at 20:47
  • @Siemoore You basically have to do three things: 1) Ensure your export and import columns line up exactly. 2) Ensure your values are valid and data is formatted correctly as outlined in my answer. 3) Ensure you are not missing some required fields in your import... If you cover those bases, and use IE, you should largely experience success; though keep in mind that this functionality is not well-supported by SharePoint, so it can definitely be finicky. Still, mine is the best (essentially) code-free solution I was able to find. Cheers! ;)
    – Chiramisu
    Dec 15, 2017 at 17:48
  • I'm going to venture to say that this does NOT work for Microsoft365 and SPO. I cannot paste a single line from a Form's back-end Excel workbook into a SPO list. I know all the columns are set up just fine, since the Flow that was originally set up worked for more than a year before someone in IT helpfully removed the Flow's owner's access to the SPO list causing the flow to fail. Now I need to backfill and cannot do so in bulk. It seems the only option may be copy/paste cell-by-cell :(.
    – FreeMan
    Aug 2 at 13:21
  • OK, an update to that comment. Sometimes I can paste in a single row, sometimes it pastes the entire row into the first cell in SPO. I do not yet understand the difference of why it does which when. I have not been able to paste in multiple rows in one shot...
    – FreeMan
    Aug 2 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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