Forum Discussion
Bud4321
Oct 09, 2026Tin Contributor
Workflow for importing Excel to List
I'm a novice developing a workflow for importing an Excel spreadsheet to a SharePoint list. Have I missed anything?
Workflow:
- Thoroughly clean up the data in Excel before importing to a list.
- Caution: Excel filters (and list filters) are case-insensitive and ignore superfluous spaces, etc. So we can't rely solely on filter functionality to find messy values. Instead, consider using the EXACT function in Excel like this: =OR(EXACT(A2,"PRIMARY"),EXACT(A2,"SECONDARY")).
See this Stack Exchange post for more options: https://sharepoint.stackexchange.com/questions/317576/find-list-values-that-arent-in-the-picklist-choices
Or, as a last resort, we could consider importing to an Oracle table and use a GROUP BY query (SQL) to find incorrect values, since that's dead easy in databases. I don't use Access for this because it has the same filtering limitations that Excel and lists have.
- Caution: Excel filters (and list filters) are case-insensitive and ignore superfluous spaces, etc. So we can't rely solely on filter functionality to find messy values. Instead, consider using the EXACT function in Excel like this: =OR(EXACT(A2,"PRIMARY"),EXACT(A2,"SECONDARY")).
- Manually create an empty list. Or import a blank list from Excel and modify it.
- Caution: When importing from Excel to a list, the list won't honor the Excel column names for the internal list names, even if the Excel names are clean. The list will generate column names like "field_0" through to "field_30" (or whatever applies).
Google suggests importing from Access is an alternative -- the column names should be honored. https://i.sstatic.net/3KoAsrMl.png - Caution: The time zone for my SharePoint sites and personal MS list environment is wrong. It's set to Pacific Time, when it should be Eastern Time. Keep an eye on any list date values to ensure they are correct and haven't been automatically changed to a different day.
- Caution: When importing from Excel to a list, the list won't honor the Excel column names for the internal list names, even if the Excel names are clean. The list will generate column names like "field_0" through to "field_30" (or whatever applies).
- Import the Excel rows/data using Power Automate.
- For what it's worth, any dates that get imported via PA will be correct, despite the time zone, unlike when importing data from Excel using the list UI.
- Inspect the imported data, correct the list columns/settings, correct the Excel data, and repeat #3.
- Do this multiple times -- as many times as it takes.
1 Reply
- Rob_ElliottSilver Contributor
On your #3, if using Power Automate to import the rows (rather than just copying/pasting) you need to change the settings of the List rows present in a table action. By default it will only bring back a maximum 256 rows, so you need to go to settings, turn on the pagination toggle and change the threshold to a number that is more than the number of rows in your excel table.
Rob
Los Gallardos, Spain
Microsoft Power Platform Community Super User
Principal Consultant, Power Platform, WSP Global (and classic 1967 Morris Traveller driver)