9

I am trying to copy a column of data from an Excel spreadsheet, exported from a proprietary Lotus Notes database application (groan), to a SharePoint list presented in Datasheet view. The Notes app exports this column of data, created by a user making multiple selections via a checkbox control, to Excel as a comma-delimited string of values, i.e., a,b,c. Copying and pasting the comma-delimited string to a checkbox column in the Datasheet results in an error: "Cannot paste the copied data due to data type mismatches or invalid data."

How can I get this column of data into my checklist column without having to select each row's values manually?

Improve this question

4 Answers 4

Reset to default
9

If you're trying to get cell data from Excel (or any other table source) into a SharePoint list's Checkbox column in Datasheet view, the values in each source field must be delimited with the semicolon and hash characters (;#). Each cell's data must also have a leading and trailing semicolon/hash pair. This last bit is counter any other delimiting style I've encountered, but a copy or cut-&-paste action will not succeed without the surrounding characters.

For example: If your source cell contains the values a, b, & c delimited by commas (a,b,c), replace those values with ;#a;#b;#c;#.

I've been able to quickly reformat large collections of values using an application's Find-and-Replace action, but I haven't yet found a way to get Excel (2007) to use Regular Expressions and wildcards without a lot of fiddling. I've gotten around this by copying the source values to a RexEx-capable text editor (Microsoft Visual Studio Express 2012 for Web, in my case). The ^ and $ characters denote the beginning and end of a line, respectively.

Excluding the RegEx find-and-replace details, I found the answer to this question in a (now) four-year-old post to the microsoft.public.sharepoint.windowsservices Google Group. It is the only answer I've been able to find online, so I want to increase its visibility and findability by sharing it here. I know asking and answering your own question is a valid action on StackExchange sites, but this isn't my answer, so I'd like the community's input on the propriety of this post. If I receive feedback that this post is permissible, I'll remove all this long explanatory content :-)

Improve this answer
2
  • 2
    As of September 2018 this method wasn't working with SharePoint 2013 - the semicolons were disappearing and leaving the hash symbols as plain text. I did some additional research and found this workaround: Change the column type to plain text (single- and multi-line both seem to work), then import exactly as Pete outlined above. Once the data is in the list, change the column type back to Choice/Checkboxes and SharePoint properly converts it. HOWEVER: Changing from Choice back to Text does not re-encode the data, so you can't switch back and forth. #ThanksMSFT
    – Brendan W
    Commented Sep 11, 2018 at 20:48
  • ;# worked for SP 2010. For SP 2016 and SP 2019 datasheet view just users semi-colon + space as value separator only, no start or end, so just a; b; c but must be a data sheet view. In each case I found the format by creating some sample data in the list first, then copying from data sheet view to excel and using the format that appeared.
    – Swanny
    Commented Nov 7, 2019 at 0:31
0

I stumbled across this thread while having this same problem. However, the original solution shown in this original thread did not work for me using a Sharepoint 2013.

After a lot of trial and error, I was able to make it work by:
1. Making the multiselect column a lookup column and make a separate list for all the selections
2. Create one entry in the entire list that has all the possible fields selected for the multiselect columns. The other columns don't matter.
3. You can then copy excel data with ";" as delimiters and a space in between to make it work with Ctrl+C & Ctrl+V in Internet Explorer using Sharepoint's standard Active-X plugins.

I have not tried this with a metadata column, but my hunch is that it will not work. If you are implementing this on metadata, you probably will have to know all the individual IDs referring to each item within your termset dictionary somehow to make it work as I think the connection is done via ID in termsets behind the scenes (I am not a programmer so forgive me for these guesses and being vague). If other people have other better solutions for Managed Metadata, it wouldn't hurt to post it here.

Hope this will help out others that have trouble getting this.

Improve this answer
0

Using Sharepoint online as of Nov 2017 it is not possible to paste data to multichoice field in Quick Edit (former Datasheet View) - the fields are greyed and inactive in Quick Edit.

Instead I discovered this procedure to upload multiple rows with multichoice field values to a sharepoint list:

  1. create a plain text field in your Sharepoint list
  2. create linked table in MS Access to the SP list
  3. in excel create rows with multichoice values in cells separated by ;# sequence (e.g.: ;#a;#b;#c;# for a,b,c multichoice value - see other answer in this question)
  4. copy paste rows from XLS to linked MS access table (correct rows order is necessary)
  5. Change the type of your plain text field (in the SP list) to multiple choice type - fill possible options (e.g.: a,b,c).
  6. the sharepoint will recognize separated values and start behave as proper values in multichoice field

Hope it works for you too.

Improve this answer
0

FROM: https://social.technet.microsoft.com/Forums/en-US/03e88e62-56a1-41a8-b4fb-e88629a64cb2/can-i-quick-edit-multiple-items-for-a-column-that-has-data-type-as-choice-of-multiple-choice-with?forum=sharepointgeneral&prof=required

...Based on my testing, if I created multiple checkboxes with Fill-in option, I could not edit the column in Quick Edit mode.

In Quick Edit mode, to edit choice column, I need to use one of the following options:

Checkboxes (allow multiple selections) without Allow 'Fill-in' choices Drop-Down Menu with Allow 'Fill-in' choices

Improve this answer

Your Answer

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

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