Have you ever needed to import Excel data into an existing SharePoint List? Creating a new list and importing data is easy enough using the Create a list based on a spreadsheet feature but adding data to an existing List is not that straight forward.
This blog post will show how I used Power Automate to import Excel data into an existing SharePoint List.
To start, ensure that your Excel data is formatted as a table and the column names and data types align to the destination List. Make sure your Excel file is stored online (i.e. OneDrive or SharePoint).
Create a new Flow using the “Manually trigger a flow” trigger.
Add an “Excel Online (Business)” – “List rows present in a table” action. Set the “Location”, “Documents”, “File” and “Table” values to the location and names that you set in the previous section.
This next section is targeted for date data. Excel dates don’t carry over the way you would expect. We’ll use a variable to store the date from Excel and then apply an expression to do the conversion. You will need a variable per date column for import. Add a “Initialize variable” action, set the name to “varDate1” and type to “String”. I am importing three dates into my existing list.
Add an “Apply to each” action using the “value” output from the Excel “List rows present in a table” action.
Add “Set variable” actions for each of your date columns and set the value to the Excel date columns.
Add a “SharePoint” – “Create item” task, set it your target site and list name. Set the columns to align with your Excel data. For the date columns add the following expression:
- addDays(‘1899-12-30’,int(variables(‘varDate1′)),’yyyy-MM-dd’)
Important, if your date data has the time included it must be removed. Add “First” and “Split” functions to keep the date only portion. Example expression:
- addDays(‘1899-12-30’,int(first(split(variables(‘varDate1’), ‘.’))),’yyyy-MM-dd’)
My complete Flow looks like this:
Save and test your Flow. The data in my Excel listing successfully imports into my existing List.
I hope this helps and thanks for stopping by.
Norm
Norm Young 
Great post Norm. Having done this a number of times in the past I’ve run into a constant issue that comes up. Power Automate / SharePoint do not like to share a file with others and, flows will often fail. I generally end up as a first step taking a copy of the workbook and placing it somewhere that I know has no collaboration issues. At end of run when successfully, delete the workbook ready for the next run.
LikeLike
Great point and excellent workaround. Thanks for reading!
Norm
LikeLike
Works great. unfortunately it loads exactly 256 lines in sharepoint. but I would have to do more than 5000 lines with one upload.
LikeLike
Hey Mr. T,
Try changing the Excel “List rows present in a table” action so that “Pagination” is set to on and let me know how you make out.
Thanks for reading!
NY
LikeLike
I am following this technique but seem to be getting the same (incorrect) date for every item. Any suggestions?
LikeLike
Check Site Settings > Regional Settings and confirm that the Timezone matches your local timezone.
Thanks for reading.
NY
LikeLike
I tried same. but it is not copying the date to my list. My excel column type is date and sharepoint column data type is text. please advise, Thank you
LikeLike
Hi Seema,
What action is causing the error and what is the error message?
Excel date to List text should work as-is.
Thanks for reading.
NY
LikeLike
Hello from Austria 🙂
Thanks for your help!!!
The excel rows are in a table 🤔
I used a nother file but it doesn’t works.
The flow runs roundabout 5 till 6 minutes. Perhaps you have a nother idea.
LikeLike
Check the data, there could be blanks or mismatched values that can cause it to fail. Assuming it loaded some of the Excel file, you should be to cross reference the last List item with the failed Excel row.
LikeLike
How do you target the list you want the items to be listed in?
LikeLike
Hi Jordan,
You need an existing List to import to and the definition must align with the spreadsheet. When you add a “SharePoint” – “Create item” task you specify a site and a list. They are your destination.
Does this make sense?
Thanks for reading.
Norm
LikeLike
Hello Norm,
I tested the solution without the date conversion. I can save the flow and test/run it. But no data was added on the list only blank items was added. Is there something i missing on the instruction
LikeLike
Hi Hardy,
Review your spreadsheet table and ensure that it is properly formatted. Also, ensure that the Excel column to List column mappings is correct.
If you are stuck, book some time with me: https://calendly.com/norm-young/sharepoint-power-automate-help?month=2021-02
Thanks for reading.
NY
LikeLike
I get this error message: Unable to process template language expressions in action ‘Create_item’ inputs at line ‘1’ and column ‘19101’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.
LikeLike
Hi Corissa,
A couple things to check. Is the destination column type DateTime? Check the formatting of the expression. WordPress changes single and double-quotes. The expression should look like:
addDays(‘1899-12-30’,int(variables(‘varDate1′)),’yyyy-MM-dd’)
Let me know how you make out.
Thanks for reading!
NY
LikeLike
I am getting this error when i tried to change it in European format dd-mm-yyyy
Unable to process template language expressions in action ‘Create_item’ inputs at line ‘1’ and column ‘17706’: ‘In function ‘addDays’, the value provided for date time string ’30-12-1899′ was not valid. The datetime string must match ISO 8601 format.’.
Any suggestion for this format dd-mm-yyyy
LikeLike
Hi Sal,
Are the regional settings of your list in European format? I’ll look closer at this one and get back to you.
Thanks for reading!
NY
LikeLike
I am having the same trouble. my regional setting is in european format. how to get rid of this error?
LikeLike
Hi Anand,
Check the formatting of the addDays(‘1899-12-30’,int(variables(‘varDate1′)),’yyyy-MM-dd’) command. Try changing ‘yyyy-MM-dd’ to match your regional settings.
Thanks for reading.
NY
LikeLike
I’m having trouble importing more than 5000 rows at a time. I have an initial worksheet with around 100,000 rows, and once this is completed I’ll be adding up to 10,000 every few days so need a way to add a large number of rows regularly. I’ve tried a few solutions but nothing seems to work as its laid out in the articles, with different error messages each time. If I set it to only import a maximum of 5000 then it works just fine, but I’d need it to do this then automatically move onto the next 5000, I really want to avoid having to have multiple tables with 5000 rows in each, run it, then point it at the next table and run it again and again. Are there any easier solutions that will work through a larger table?
LikeLike
Hi kevl17,
Try changing the Excel “List rows present in a table” action so that “Pagination” is set to on and let me know how you make out.
Thanks for reading!
NY
LikeLike
I have been battling for days now…my formula is returning a result of “00” for the month (the actual date in my Excel table is 09/07/2020. Any thoughts or suggestions? Thanks so much to anyone who can help!
FORMULA
addDays(‘1899-12-30’,int(items(‘Apply_to_each_-_Create_New_List_for_Processing’)?[‘Course Start Date’]),’yyyy-mm-dd’)
ERROR
The ‘inputs.parameters’ of workflow operation ‘Create_item_-_Create_Processing_Q’ of type ‘OpenApiConnection’ is not valid. Error details: Input parameter ‘item/Processed_x0020_Date’ is required to be of type ‘String/date’. The runtime value ‘”2020-00-07″‘ to be converted doesn’t have the expected format ‘String/date’.
LikeLike
Hi JB,
On quick glance, I see that the single quotes ‘ need to be updated to ‘. Let me know how you make out.
Thanks for reading!
NY
LikeLike
Norm,
Thanks so much for you quick reply! I was really careful with the single quotes and the Flow interface even auto-filled for me. I got the same results as what I a pasted above that way. I tried copying and pasting the the formula from your original post which did result in different single quotes from mine but Flow rejected them and I had to revert back to the single quotes I had. In all fairness, I don’t understand what you are recommending that they be updated too (my original and your suggestion appear the same to the naked eye). Short story, I am still ending up with the same results. While I am an advanced user of Excel, Flow is new to me. I so very much your generosity with your time!
LikeLike
There are two Excel Connectors within Power Automate Excel Online (Business) and Excel Online (OneDrive). There was a time and I’m not sure if this is still the case whereby Dates within an Excel Table using the Business Connector were converted to a Serial No where the OneDrive Connector left them as a date. The formula may be failing due to the connector.
On a failed flow run, look at the extracted output from the line as, that will give you a hint to which result you are getting.
I have had mixed results with converting Excel Data using the INT function referring to the Excel Field directly. I normally add a Compose Statement prior to an Int or Float conversion then would do addDays(‘1899-12-30’,int(compose(output)),’yyyy-mm-dd’)
LikeLike
I just figured it out!!!! The month formatting designation in my formula was not capitalized. I had “mm” it should have been “MM”. Thanks again for sharing your expertise with we neophytes!
LikeLike
Awesome JB glad to hear it!
NY
LikeLike
I appreciate all the good info here. I am new to the site as well as power automate. I am using this example and continue to receive the following error upon save. “Enable concurrency control for the apply to each loop and set its degree of parallelization to 1 when using a Set Variable action inside an apply to each loop”. This is despite the fact that I have done just that. Do you have any insight on this error message?
Thanks in advance.
JLC
LikeLike
Hi J. Clifford,
Can you confirm that your variables have been declared as strings? Also, confirm that the “Apply to each” action is using the “value” output from the Excel “List rows present in a table” action.
Thanks for reading!
NY
LikeLike
You can alternatively use a Compose Action as Compose is not affected by concurrency.
Create a Compose Action before each Variable action. Copy the expression from the variable to the Compose Action then set the variable to the output of the Compose Action.
LikeLiked by 1 person
Hi there
i followed your instructions above. Using dates from excel to sharepoint. I get this error
The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘v@{outputs(‘List_rows_present_in_a_table’)?[‘body/value’]}’ is of type ‘String’. The result must be a valid array.
I cannot seem to send snips / jpegs. The excel table column for date is “date and time” and so too is the sharepoint list. I am in the UK and have changed the excel date data to uk format.
any help? i only discovered automate about a week ago
h
LikeLike
Hi Heshan,
This is an odd message. Are you using the Excel Online (Business) “List rows present in a table” action with a valid Excel file that has been formatted as a table?
Thanks for reading!
NY
LikeLike
Hi Norm, thanks !!!! You save me !!!
To solve the problem of the repeated date, the last “SharePoint – “Create item” task must be inside the “Apply to each”.
I made the mistake of putting it as an extra step.
LikeLike
Hi Rick,
Glad it helped and thanks for the kind words!
Thanks for reading!
NY
LikeLike
Hi norm,
Great post. Do you know if there is at all a solution that allows you to also keep the time values? 1/1/2021 9:30 AM from excel online to the share point list.
Thank you
LikeLike
Hi Akane,
I’m sure there is a way but I haven’t figured out or found anything just yet. This is on my list to look into.
Thanks for reading!
NY
LikeLike
You can use the following expression
addDays(‘1900-01-01’,add(int(items(‘Apply_to_each’)?[‘Excel Column’]),-2))
LikeLiked by 1 person
You can use the following expression. It will work
addseconds(‘1899-12-30’, int(formatNumber(mul(float(items(‘Apply_to_each’)?[‘Created’]),86400),’0′,’en-us’)),’yyyy-MM-dd HH:mm:ss’)
LikeLiked by 1 person
I got your Date formula to work but hav been struggling to figure out how to convert Time only Excel column to Sharepoint time column.
I sset up variable as strin as you would for the Date column and then used
formatDateTime(variables(‘varStartTime’),’HH:mm tt’)
which gives me a 12:30 AM type result
LikeLike
Hi Steve,
No experience with this one but this thread looks promising: https://powerusers.microsoft.com/t5/Building-Flows/excel-to-sharepoint-list-time-and-datetime/td-p/642092.
Thanks for reading!
NY
LikeLike
I am using the below formula to convert the date
addDays(‘1899-12-30’,int(variables(‘ETDVar’)),’yyyy-MM-dd’)
it works fine but i need to factor in when the date field is blank and i tried below
if(equals(items(‘Apply_to_each’)?[‘ETDVar’],”),”,addDays(‘1899-12-30’,int(variables(‘ETDVar’)),’yyyy-MM-dd’))
It fails because of the ETD field being blank for some entries so the above formula must be incorrect.
ERROR MSG
Unable to process template language expressions in action ‘Create_item’ inputs at line ‘1’ and column ‘25038’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.
Could you take the time to advise?
Thanks for the blog, i’ll buy you a beer…
LikeLike
Hi Eamonn,
Check the data for non-date values. The error indicates a non-date value.
Thanks for reading!
NY
LikeLike
I am experiencing a similar issue. Some of my data will be blank for the date. How do I add this into the formula? I tried something similar to Eamonn Glynn post, but I keep getting an error message stating “The expression is invalid.”
LikeLike
Hi Agnes,
What is the expression? Sounds like you will need to plan for blank dates in the spreadsheet.
Thanks for reading!
NY
LikeLike
I have tried formulas below and included the error messages associated with 2 of them.
if(empty(items(‘Apply_to_each’)?[variables(‘varDate’)]), null, addDays(‘1899-12-30’,int(items(‘Apply_to_each’)?[variables(‘varDate’)]),’yyyy-MM-dd’))
if(empty(items(‘Apply_to_each’)?[‘Date’]), null, addDays(‘1899-12-30’,int(items(‘Apply_to_each’)?[‘Date’]),’yyyy-MM-dd’))
Unable to process template language expressions in action ‘Create_item’ inputs at line ‘0’ and column ‘0’:
‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.
if(equals(item()?[‘Date’], null), ”, formatDateTime(item()?[‘Date’],’yyyy-MM-dd’))
Unable to process template language expressions in action ‘Create_item’ inputs at
line ‘0’ and column ‘0’: ‘In function ‘formatDateTime’, the value provided for date time string ”
was not valid. The datetime string must match ISO 8601 format.’.
LikeLike
Hi Agnes,
You can use this expression:
if(equals(variables(‘varDateReported’), ”), null, addDays(‘1899-12-30’,int(variables(‘varDateReported’)),’yyyy-MM-dd’))
Replace with your variable name. This expression should be used for each date column in the create item action.
I hope this helps.
NY
LikeLike
When I use the express you provided I receive this error at the top of the page in a black box:
us.flow.microsoft.com says
The expression is invalid.
LikeLike
Check that your single quotes are straight.
LikeLike
I was finally able to get the formula to take with a few work arounds, but I’m back to the same error Eamonn has above.
Unable to process template language expressions in action ‘Create_item’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.
Any ideas?
Thank you for all your help!
LikeLike
Aloha NY,
I was wondering if you have been successful updating a Sharepoint List from an excel file that add dates and MERGES the data as well. For example if row Apple is new, it adds the new row with Apple and corresponding columns. If Banana has a column with a $ value that changes, it would merge the data with the new value.
Appreciate the insight!
LikeLike
Hi Bri,
I have not done this before but it sounds like adding a Power Automate expression during the add a row action should meet your needs.
Happy to discuss this further if you provide more details.
Thanks for reading!
NY
LikeLike
Hey Norm,
Thanks for all of this. When I do this step by step and all goes well until I try to add the expressions you have listed: addDays(‘1899-12-30’,int(variables(‘varDate1′)),’yyyy-MM-dd’), I keep getting “The expression is invalid” I’ve checked over and over and just can’t seem to figure out why it’s an invalid expression. Any ideas?
LikeLike
Hi Collin,
Looks like incorrect single quotes.
Thanks for reading!
NY
LikeLike
Hey Norm, sorry I did get this to work in the end. I did have a quick question; for dates in my columns that are missing, how to I bypass this to allow them to be added to the list?
LikeLike
Hello again Collin,
Great question and one requires some validation on my part. I’ll follow up again.
NY
LikeLike
Hi, I am really keen to get this to work as have so many rows to upload.
addDays(‘1899-12-30’,int(variables(‘varDate’)),’yyyy-MM-dd’)
I really can’t see where I am going wrong.. my SP list column is of Date only (not Date/Time). My regional settings are English (UK) and dates are of format dd/mm/yyyy.
Error message: Unable to process template language expressions in action ‘Create_item_2’ inputs at line ‘1’ and column ‘25455’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.
LikeLike
Hi Rachel,
In the sample you provided the single quotes are ‘ and should be ‘.
addDays(‘1899-12-30’,int(variables(‘varDate’)),’yyyy-MM-dd’)
addDays(‘1899-12-30’,int(variables(‘varDate’)),’yyyy-MM-dd’)
Let me know how you make out. Thanks for reading!
NY
LikeLike
Hi Norm, I still cant see the difference in the 2 lines of code you have there.. are those 2 lines not the same?
single quotes are ‘ and should be ‘ – this looks like the same?
Maybe it would be easier to know exaclty what to type in keywboard to get the correct single quote
LikeLike
Sorry, this is a WordPress issue.
Power Automate expressions need a straight single quote.
LikeLike
Nice article man! thank you.
Is there a way to import xlsx per project into a fixed list?
For exemple: we have 50 xlsx that each one is a project, with a fields that we need to import on that list. that list has a field “projUid” too.
thank you
LikeLike
Hi Rodrigo,
You can definitely import multiple spreadsheets into a single list but this will require that the Excel files all have the same layout. You could probably add Flow actions that would gather and process all of the files in a single Flow.
Thanks for reading!
NY
LikeLike
Hi Norm,
In this moment I´m using excel from the web (365) and I need to have 2 spreadsheets (A and B), where B reads/takes a list created in A, the thing is I don´t know how to integrate them. In B tried to create a List from Data Validation but I don´t know how to specify that the list is in A spreadsheet. Also I wonder how this can be done when having the list by using Microsoft lists. Any help will be more than welcome! BR //Rubén
LikeLike
Hi Rubén,
Are you trying to create List B from List A? Sorry if I am not following the question.
Thanks for reading!
NY
LikeLike
Hi Norm, thank you for you reply!, I need to access a down drop list from B spreadsheet based on a list created in spreadsheet A. Is it possible to have this by using Excel for web?
LikeLike
Hi Rubén,
I believe that this is possible using a VLOOKUP command. This stuff is outside of my area of expertise.
Thanks.
NY
LikeLike
Norm, I enjoyed your writing. I have an Excel sheet with date and time formatted as 9/29/2021 2:29:17 PM in the field. I have used the expression addDays(‘1899-12-30’,int(variables(‘varForecastedInServiceDate’)),’yyyy-MM-dd’) but get an error InvalidTemplate. Unable to process template language expressions in action ‘Create_item’ inputs at line ‘1’ and column ‘29093’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’. Would you have suggestions?
LikeLike
Hi Dave,
Thanks for those kind words. You are getting that error because of the time portion of the date. Assuming the time portion is not important use the split function to parse it out. I.e. addDays(‘1899-12-30’,int(first(split(variables(‘varDate1’), ‘.’))), ‘yyyy-MM-dd’).
I hope that helps and thanks for reading.
NY
LikeLike
Hello Norm,
Thank your post. I followed your flow instructions but I am encountering the following error:
Flow save failed with code ‘InvalidVariableOperation’ and message ‘The inputs of workflow run action ‘Create_item_2’ of type ‘OpenApiConnection’ are not valid. The variable ‘Start_time’ must be initialized before it can be used inside action ‘Create_item_2′.’.
Recurrence
Any insight?
LikeLike
Please can you assist. I am getting an error about a Number/double field. How would i import this type of field into my SP List
LikeLike
Hi Kaleen,
Can you provide the exact error message?
Thanks for reading!
NY
LikeLike
Hi Norm,
Appreciate this setup and I think I’m almost there. I’m running into an issue where:
Unable to process template language expressions in action ‘Create_item’ inputs at line ‘1’ and column ‘18341’: ‘The template language function ‘int’ expects one parameter: the value that is to be converted to an integer. The function was invoked with ‘2’ parameters. Please see https://aka.ms/logicexpressions#int for usage details.’.
Best,
John M.
LikeLike
I actually read into this a little more and I am silly. Apologies and thank you for the help!
LikeLiked by 1 person
Hi John, I’m glad it helped and thanks for reading!
NY
LikeLike
Hi Norm,
First, thanks a lot for all the screenshots and the detailed step-by-step presentation. This is very helpful for a newbie like me!
I followed your instructions, initialize variable(s), then set variable(s) into an “Apply to each” and finally enter the following expression(s) into the right fields in “create/update Sharepoint item” :
if(empty(items(‘Apply_to_each’)?[variables(‘varDatePm’)]), null, addDays(‘1899-12-30’,int(items(‘Apply_to_each’)?[variables(‘varDatePm’)]),’yyyy-MM-dd’)) with varDatePm as the name of the variable I initialized and then set.
I write it that way in order to have empty values in Sharepoint for the cells in Excel that are empty.
The flow “run successfully” but the dates don’t appear in the Sharepoint list.
Do you see something wrong up here?
Many thanks!
Vincent
LikeLike
Hi Vincent, thanks for the feedback, I appreciate it!
No quick answer on this one except to say that you should add variables that can store debug information for you to review. I would start with capturing the varDatePM variable to ensure that it is getting a value that you expect and then continue by adding the elements of your expression to further assess the code.
Thanks for reading!
NY
LikeLike
Hi Norm. I would highly appreciate your thoughts on this. I am using the expression below in PowerAutomate to write excel values to a SharePoint List
if(empty(items(‘Apply_to_each’)?[‘date’]),null,addDays(‘1899-12-30’,int(items(‘Apply_to_each’)?[‘date’]),’yyyy-MM-dd’))
This expression writes null values but errors out when it tries to write actual date values. The error message is
InvalidTemplate. Unable to process template language expressions in action ‘Create_item’ inputs at line ‘1’ and column ‘28160’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.
The data in the excel format is formatted as Custom : “yyyy-mm-dd”
The output from [List Rows Present in table]. is as follows : “date”:”2021-12-23″.
LikeLike
Hi Vince,
The error indicates that a non-numeric value was passed into the INT function. If you put the date value into a variable it should help debug further.
Let me know how you make out and thanks for reading!
NY
LikeLike
Hello, I’m a beginner, i’m also having the same problem. in my case, i’m trying to process a line without a date. Any suggestion?
LikeLike
Thanks! Works like a charm!
LikeLike
Glad to hear it Dev and thanks!
LikeLike