cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

importing excel sheet into a sharepoint list

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

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:GetRowsApplyToEachCreateItem.pngFor 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!

View solution in original post

5 REPLIES 5
Super User II
Super User II

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:GetRowsApplyToEachCreateItem.pngFor 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!

View solution in original post

Hi @Brad_Groux 

 

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

 

es1.JPGes2.JPGes3.JPGes4.JPGes5.JPG

 

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

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

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!

Community Support
Community Support

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.

1.PNG

 

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.

2.PNG

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (78,232)