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

58 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

      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

    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

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

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

    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

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

    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

      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

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

    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

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

    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

      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

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

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

    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

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

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

    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

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

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

    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

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

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

    1. Hi Eamonn,

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

      Thanks for reading!

      NY

      Like

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

    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

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

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

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

    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

      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

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

    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

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

    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

      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

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

    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

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

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

Leave a Reply to Sal Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s