Import Excel data into an existing SharePoint List using Power Automate

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

82 thoughts on “Import Excel data into an existing SharePoint List using Power Automate”

  1. 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.

    Like

    Reply

      1. 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

        Like

      1. 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

        Like

      2. 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

        Like

    2. 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.

      Like

      Reply

      1. 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.

        Like

    1. 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

      Like

      Reply

  4. 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

    Like

    Reply

  5. 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.’.

    Like

    Reply

    1. 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

      Like

      Reply

  6. 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

    Like

    Reply

    1. 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

      Like

      Reply

      1. I am having the same trouble. my regional setting is in european format. how to get rid of this error?

        Like

      2. 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

        Like

  7. 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?

    Like

    Reply

    1. 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

      Like

      Reply

  8. 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’.

    Like

    Reply

    1. 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

      Like

      Reply

      1. 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!

        Like

      2. 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’)

        Like

  9. 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!

    Like

    Reply

  10. 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

    Like

    Reply

    1. 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

      Like

      Reply

  11. 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.

    Liked by 1 person

    Reply

  12. 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

    Like

    Reply

    1. 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

      Like

      Reply

  13. 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.

    Like

    Reply

  14. 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

    Like

    Reply

    1. 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

      Like

      Reply

      2. 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’)

        Liked by 1 person

      3. 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

        Like

  15. 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…

    Like

    Reply

    1. Hi Eamonn,

      Check the data for non-date values. The error indicates a non-date value.

      Thanks for reading!

      NY

      Like

      Reply

      1. 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.”

        Like

      2. Hi Agnes,

        What is the expression? Sounds like you will need to plan for blank dates in the spreadsheet.

        Thanks for reading!

        NY

        Like

      3. 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.’.

        Like

      4. 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

        Like

      5. 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.

        Like

      7. 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!

        Like

  16. 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!

    Like

    Reply

    1. 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

      Like

      Reply

  17. 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?

    Like

    Reply

  18. 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?

    Like

    Reply

  19. 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.’.

    Like

    Reply

    1. 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

      Like

      Reply

      1. 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

        Like

  20. 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

    Like

    Reply

    1. 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

      Like

      Reply

  21. 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

    Like

    Reply

    1. 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

      Like

      Reply

      1. 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?

        Like

      2. Hi Rubén,

        I believe that this is possible using a VLOOKUP command. This stuff is outside of my area of expertise.

        Thanks.

        NY

        Like

  22. 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?

    Like

    Reply

    1. 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

      Like

      Reply

  23. 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?

    Like

    Reply

  24. 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

    Like

    Reply

  25. 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.

    Like

    Reply

  27. 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

    Like

    Reply

    1. 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

      Like

      Reply

  28. 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″.

    Like

    Reply

    1. 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

      Like

      Reply
  29. Pingback: Export Microsoft List data to Excel – Norm Young

  30. 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?

    Like

    Reply

Leave a comment