2

I just imported an Excel spreadsheet into a SharePoint list, and all of my data was imported as single or multiple lines of text. While appropriate for some instances, in other cases, like a name field, I'd prefer to have a data type of Person or Group instead of free-form text strings.

I'm assuming that using Excel's field definintions to define numeric vs. date will carry over OK, but how can I force a particular data type that's not on Excel's list of standard data types? Alternatelty, is there any place in SP that I can edit field types?

Thanks

Improve this question
1
  • Hi @dwwilson66 I have a very thorough and tested solution for exactly what you're looking for on a related question. The only caveat is that you'll need access to the server (or someone who does) to run the PowerShell script for getting the SP User IDs for you. Answer: Import/Copy/Paste Data from Excel into Existing SharePoint List.
    – Chiramisu
    Nov 14, 2017 at 18:13

2 Answers 2

Reset to default
3

You can't foced special data types when importing or change type of existing columns.

Your best option is probably to:

  1. Create a new column for the name with the right type
  2. Create a Datasheet View for the list
  3. Select all the values in the old name column
  4. Copy
  5. Click the new name column in the first item
  6. Paste

This requires that all the names are in the right format DOMAIN\UserName

Improve this answer
2
  • Woohoo! One less step than ~my~ workaround. Fearful that the Excel datatype would follow the data, I used notepad as an intermediate paste destiantion to strip out formatting. Time consuming, but it worked. I'll have to try that natively with the Datasheet view next time!
    – dwwilson66
    Jul 19, 2012 at 12:45
  • This answer is good, but I have a much more thorough answer for SP2010 here for any who are interested.
    – Chiramisu
    Aug 29, 2017 at 5:12
2

I do not know how to define non-excel datatypes in Excel. And it is not possible to change a datatype in SharePoint after column is created, but this workaround may help you:

Create a new column with the desired data type in the SharePoint list. Position it next to your wrongly typed data column. Open the list with the Edit in DataSheet option (located in the Actions menu) or create a dataheet view. Copy and Paste the whole set of data from the 'wrongly typed' column in to new column. Then delete the old column.

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.