Handling Diverse Data Types: Import Excel to SharePoint

How do you import data from Excel into a SharePoint list with different data type columns?

Importing data from Excel into a SharePoint list can be a powerful way to manage and share information within your organization. This guide will walk you through the steps to import data from an Excel spreadsheet into a SharePoint list, ensuring that different data types are correctly handled.

Step 1. Prepare Your Excel Spreadsheet

  1. Format as a Table: Ensure your Excel data is formatted as a table. This is crucial for the import process.
  2. Check Data Types: Make sure your columns have consistent data types (e.g., text, date, number).

Note. In Excel, the “General” format is the default data type applied to cells. It is quite flexible and can accommodate different types of data, including: (e.g., text, date, number, Boolean)

Step 2. Create a Custom List in SharePoint

Below is my SharePoint list structure

Custom list in SharePoint

Step 3. Create a Power Automate Flow

  1. Manual Trigger a Flow
    • Open Power Automate and create a new flow.
    • Select Instant Cloud flow and choose Manually Trigger a Flow.
  2. List Rows Present in a Table
    • Add a new step and search for Excel Online (Business).
    • Select List rows present in a table.
    • Configure the action by selecting the location, document library, file, and table.
      List row
  3. Compose: Value
    • Add a Compose action.
    • In the Inputs field, select the Value from the previous List rows present in a table action.
  4. Select Output from Compose
    • Add a Select action.
    • In the From field, select the output from the Compose action.
    • Map the columns from the Excel table to the corresponding fields in the SharePoint list.
    • Expression example for Title field:
      if(empty(item()?['Title']), null, item()?['Title'])
      Select
  5. Compose 2: Body (Select)
    • Add another Compose action.
    • In the Inputs field, select the Body from the Select action.
  6. Apply to Each (Compose 2)
    • Add an Apply to each action.
    • In the Select, an output from previous steps field, select the output from the second Compose action.
  7. Create an Item - SharePoint
    • Site Address: Your SharePoint Site
    • List Name: Your SharePoint List
    • Map fields: ex Title, FORM_STATUS Note(there is no Dynamic Content generation you have. Use below expression as is )
      • Title:
        {items('Apply_to_each')?[' Title ']}
      • FORM_STATUS:
         {items('Apply_to_each')?[' FORM_STATUS ']}

        Apply to each

Conclusion

By following these steps, you can efficiently import data from an Excel spreadsheet into a SharePoint list, ensuring that different data types are correctly handled,

Thanks for reading.

Ebook Download
View all
Learn
View all