Sharepoint List Column Limitations: What Can And Cannot Be Imported From Excel

Limitations on SharePoint List Columns When Importing from Excel

When importing data from Excel into a SharePoint list, certain column types and configurations can cause issues. There are restrictions around complex data types, lookup columns, column validation, large columns, and more. Being aware of these limitations can help you design your Excel data correctly before importing it, avoiding errors or data loss.

Column Data Types that Cannot be Imported from Excel

Some SharePoint column data types either cannot be imported from Excel at all, or lose their specialized functionality when imported:

• Managed metadata columns lose their taxonomy structure and revert to plain text
• People and Group columns revert to text strings without user/group linking
• Hyperlink or Picture columns become simple text columns
• Calculated columns cannot be imported as their formulas do not transfer

Before importing data, check your Excel columns against SharePoint’s supported data types for import, converting them if needed to prevent issues.

Restrictions on Importing Lookup Columns from Excel

Lookup columns link values from the imported list to another list in SharePoint. However, importing these relationships has restrictions:

• The lookup target list must already exist in the site
• Excel columns must contain the correct referenced values for the relationship
• Consider ID values instead of text for complex or large target lists

Define the target lists before import and ensure your Excel lookup data is clean and consistent with those lists to correctly create the relationships.

Limitations on Column Validation Formulas When Importing from Excel

SharePoint allows complex column validation rules to control data entry, but these formulas do not always import properly from Excel:

• Excel validation is simpler than SharePoint – review all columns
• Data may import but now violate the column rules
• Managed rules around dates, numbers, text length often break

Simplify validation rules to basic data types before importing, then customize again in SharePoint after.

Handling Large Columns and Column Data When Importing from Excel

Large amounts of text or number data per cell can also create issues on import:

• Time delays if cells exceed SharePoint’s 8KB content limit
• Data truncation without warnings for very large entries
• Text parsing errors as formulas become too complex

Before importing from Excel, check for large cell sizes that may need to be split across multiple columns post-import for better handling.

Working Around SharePoint Column Import Restrictions from Excel

If standard importing fails due to column issues, workarounds exist:

• Import to a custom SharePoint list then transfer data
• Use Power Apps or Power Automate flows for complex parsing
• Handle lookup issues then correct orphaned relationships
• Third party migration tools bridge certain gaps better

Understand the root column import issue first before picking the right workaround that targets that specific problem during your data migration process.

Example Codes for Importing Complex Excel Columns into SharePoint

For common complex column import scenarios, you can reference these code samples below:

Importing Text Cells Over 8KB into Multiple SharePoint Columns

  // Splits large text blocks on delimiter into multiple columns 
  string[] parts = ExcelColumn.Split(‘;;;’);

  foreach(string p in parts){
     NewSPColumn.Add(p); 
  }

Re-Linking Orphaned Lookup Columns After Import

  // Translator table mapping excel IDs to SP column values  
  Dictionary  lookups;  

  // Set lookups dictionary for current list

  foreach(SPListItem item in ImportedList){
      
      string valToLookup = lookups[item.ExcelLookupID];

      UpdateRelatedItem(valToLookup, item);

  }

With proper data preparation and coding, even tricky import formats can be handled.

Frequently Asked Questions on SharePoint Column Import Issues

Why can’t I directly import users/groups columns from Excel?

The People/Group picker in SharePoint is complex with individual permissions that do not exist in Excel. Use simple text imports then manually re-assign users/groups.

What are my options if lookup columns fail to match on import?

Fix orphaned lookup data after import with Power Automate flows, re-import validating mappings in stages, or import to staging lists then move to production.

Why do my calves have a 8000 character limit when importing from Excel?

To ensure performance, SharePoint list columns have an 8,000 character threshold. For large text blocks, split across multiple columns before or after import using delimiter characters.

How can I tell if Excel column validation rules will import properly into SharePoint?

SharePoint supports more complex validation than Excel. Simplify rules to basic data types and ranges before importing, then customize again afterwards.