Forum Discussion
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.
2 Replies
Your overall plan is pretty sound, and Rob's point about pagination is the one I'd watch most closely if you go the Power Automate route — that 256-row default catches a lot of people out on a first import.
One thing worth trying first, though: if this is a one-off load rather than something that will keep repeating, you may not need the flow at all. Lists can create a list straight from an Excel file, columns and all, and then you can tidy the column names and types in List settings afterwards. That saves building a flow just to move rows once, and it also gives you a quick way to see how SharePoint reads your headers and date columns before you commit to anything.
If the file will keep coming in, or you need the cleaning steps to run the same way every time, then your flow makes sense. Just make sure the Excel data is in a proper table with a single header row, no blank rows at the top and the same kind of value all the way down each column — mixed text and numbers in one column is usually what trips an import up.
On the time zone bit, I'd sort the site's regional settings out before you judge any of the imported dates, because a wrong setting there can make perfectly good dates look shifted. Once that's right, run a small batch first, check a few date and choice columns by eye, and then do the full run. Your step 4, repeating until it's clean, is exactly the right instinct — it's much easier to fix the source file and re-run than to clean up rows after they've landed in the list.
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)
