importing excel sheet into a sharepoint list

[MTG]

Hi,

I have been trying to get every row of an excel sheet imported into a sharepoint list through flow. I do have successfully inserted a single row into the sharepoint. However, I have two problems that I couldn't get them sorted. One when I have a column that its type is date will always return an error ' String was not recognized as a valid DateTime'. No matter what type of date format I have it changed to different one will always return the same error. The second is the flow will only get the first row and duplicate it over and over to the list

Any help would be appreciated 

[Brad_Groux · Accepted solution]

For your Flow, you would do a List rows present in a table Excel action and then Create item SharePoint action inside of an Apply to each step. Example:For the Date/Time error, you're receiving this because the format of the Date/Time Excel column you're importing does not match the format of the Date/Time column in SharePoint List.  

If you could provide an expanded screenshot of your Flow and steps, and of any detailed error messages you're receiving we could likely better assist you.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[MTG]

Hi @Brad_Groux 

Here is my flow set up and a performed test to show the error gets thrown at me.

Will try your approach and let you know, thank you so much

[Brad_Groux]

The date format passing into SharePoint is not a proper date format (see red box):

As I said in my earlier reply:

---

For the Date/Time error, you're receiving this because the format of the Date/Time Excel column you're importing does not match the format of the Date/Time column in SharePoint List.  

---

You need to insure that both of the date columns in the Excel spreadsheet and the SharePoint List are formatted in the same Date/Time format. 

Or, you can use the formatDateTime function to change the date and time strings however you wish. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-yamao-msft]

Hi @MTG ,

It is a known issue that Excel online connector converts Date and time into a string, which is not available in SharePoint list date and time column.

A workaround for this issue is to format the date column in the Excel as Text first, then input the date manually.

About the flow, to get all the items from the Excel table to SharePoint list, you need the action List rows present in a table, then an Apply to each to save each item into SharePoint list.

Best regards,

Mabel

[MTG]

Hi @Brad_Groux 

I've managed to custom format the date to match both Excel Online and Sharpoint. However, my spreadsheet have two entries one with a date and another without a date but it seems flow copies the date from the first entry and apply it to the second entry. I used the flow you have suggested above.

Hi @v-yamao-msft 

Your workaround worked beautifully. However, its tedious if I have a 100+ excel sheet entries to manually adding the date as I want.  On that matter to get all rows added to sharepoint list using the action list rows present in a table seems to fill any empty date column with first date's entry in sharepoint using CUSTOM expression provided by @Brad_Groux  Yet your workaround didn't but as I said before If I have a lot of entries in excel sheet will take tremendous time manually adjusting dates. 

Thank you both very much for your inputs.