Copy - Update excel data from Excel Online to a Share Point List

(0) Share

Report

Posted on by Sajarac

3,273

Hello community.

For days and days I have been trying to get this thing to work. But this still a pain in the neck.

Basically I am following this video to do it. and it is partially working. I am going to explain what is working and what is not working at all.

1. The rows limitation of 256. I had this error but was corrected using the pagination and increasing the threshold number up to 50000. BTW my excel file has 8,000 rows.

2. In my excel file I have a couple of Date columns in the following format "7/30/2015 15:56 PM". I am using this formula to convert those strings into a sharepoint date values but I am getting errors and the flow doesn't work

addDays('1899-12-30',int(tems('Apply_to_each')?['regularTime']),'yyyy-MM-dd').

In my Sharepoint point column I have the field "regularTime" as a single line of text. I have tried as a date format and didn't work. Also I have removed the Time portion and in Sharepoint it shows a day before or a day after. Weird and strange.

Could someone PLEASE point me in the right direction?

Thanks in advance.

Categories:

General topics

I have the same question (0)

All responses (13)

Answers (1)

Verified answer

eric-cheng 5,171 on at

Like (1)

Report
Copy link

Link copied!

Hi @sajarac ,

Tried the below.
1) Set ISO 8601 in your Excel List Rows action
2) Recommend using the Convert time zone action so you don't have to play around with expressions. Change the source to your local region.

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Was this reply helpful? Yes No
Sajarac 3,273 on at

Like (0)

Report
Copy link

Link copied!

Hey, @eric-cheng . Thank you very much for your prompt response. Your solution works FANTASTIC!!!!. Done at the first try.
Silly question. In my excel table I have 4 columns with the same problem. Should I add 4 "Convert Time actions" or in the Base time I can add those 4 columns?

Thanks again for your help.

Regards

Was this reply helpful? Yes No
eric-cheng 5,171 on at

Like (1)

Report
Copy link

Link copied!

Hi @sajarac ,

Will need 4 actions. Its simpler but less flexible.

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Was this reply helpful? Yes No
Sajarac 3,273 on at

Like (0)

Report
Copy link

Link copied!

Thank you very much. I will change it accordingly.

Was this reply helpful? Yes No
Sajarac 3,273 on at

Like (0)

Report
Copy link

Link copied!

Hi @eric-cheng , sorry to bother you again. I have been trying to include the rest of the dates columns but for some reason the next column is causing error:

Unable to process template language expressions for action 'Convert_time_zone_lastDirSyncTime' at line '1' and column '26998': 'In function 'converttimezone', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

That column has empty values but the first date column also has some empty values but it is working fine. I have checked that column to see if has a different value from date but no.

Any idea?

Thanks in advance

Was this reply helpful? Yes No
eric-cheng 5,171 on at

Like (0)

Report
Copy link

Link copied!

Hi @sajarac ,

Can you provide a snippet of the date valuesnin the working and non working column?

Is it formatted as a date in the Excel?

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Was this reply helpful? Yes No
Sajarac 3,273 on at

Like (0)

Report
Copy link

Link copied!

Hi. thank you very much for your prompt response.

Here is the columns:

All of the columns are formatted as a custom as you can see above.

Was this reply helpful? Yes No
eric-cheng 5,171 on at

Like (0)

Report
Copy link

Link copied!

Hi @sajarac ,

Can you screenshot your 2 DateTime actions as well.

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Was this reply helpful? Yes No
Sajarac 3,273 on at

Like (0)

Report
Copy link

Link copied!

You mean this?

Was this reply helpful? Yes No
eric-cheng 5,171 on at

Like (0)

Report
Copy link

Link copied!

Hi @sajarac ,

Thanks

Can you add a Compose action where I have added a red line and pass lastdirsynctime and share the output

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Was this reply helpful? Yes No