Looking for simple & basic | Import date from excel to sharepoint list - with no conditions & delete all Sharepoint items in under six hours

(0) Share

Report

Posted on by DublinOH_User

150

Hello,
I have tried sooooo many suggestions that I at the end of my rope.

First

=============================

I have both the date column & a second column converting the date into text

=IF(ISBLANK(R2),"",(TEXT(R2,"yyyy/mm/dd hh:mm:ss")))

I have:
used the create variable - set variable - If(empty(int add days expression to create item

used the create variable - set variable - If(empty(formatdate add days expression to create item

the create variable - set variable - compose output- place compose variable output with expression to create item (both addDays & formatdate)

the create variable - set variable - compose output- place set variable output value to create item

the create variable - set variable - place set variable value to create item

Why is there not a plain and simple way to add the date into a list date field from Excel???

Second

===============================
Any great methods without using HTTP to delete all items from a SharePoint list of more than 10000 rows

- no premium!

- no HTTP using API !

- something that runs in under 6-8 hours??

I can run the delete all the basic ways with no parallel filtered actions and it takes forever

I can run parallel actions filtered on a number (integer) column I added in excel but it times out before all the actions are complete

I tried different combinations of the parallel actions adjusting the filters for more or fewer rows - still times out

I tried using Solutions with child flows filtered on a number (integer) column I added in excel - still times out

Please share how many parallel actions (child flows) versus the number of rows will run with facing TIMED OUT

Thank you,
Dublin,OH User

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (0)

Paulie78 8,422 Moderator on at

Like (0)

Report
Copy link

Link copied!

Why do you not want to use HTTP API. I have a great solution on my blog for deleting items from an SP list very quickly. It does not require premium, but does use a HTTP action.

Was this reply helpful? Yes No
DublinOH_User 150 on at

Like (0)

Report
Copy link

Link copied!

I was not aware that non-premium licenses could use the API requests. As I am new to power automate I thought all HTTP actions were premium license only.

I am currently using Parent Child Flows, where the child flows are running parallel and using the excel "top" and "skip" to divide the large amount of excel rows into smaller sections.
We need to delete all and reload new lines every month. So this would be nice if it were as efficient as possible.
Excel file A - more than 5000 rows
Excel file B - more than 15,000 rows
Each have the same 24 columns
( 28 total after I added 4 just so we could load them into SP/ 2 for date columns and 2 for creating collections in Power Apps)

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
Copy link

Link copied!
Check out my blog:

How to batch delete items from SharePoint

How to batch create SharePoint Items

These are somewhat complicated requirements for a new user, but you're going to need to use the methods described in my blog because:

You will get terrible performance the normal way.

You will blow the API limit doing it in a standard apply to each loop.

See how you get on, if you need more help, reach out.

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

Was this reply helpful? Yes No
DublinOH_User 150 on at

Like (0)

Report
Copy link

Link copied!
Thank you for your replies and valued time.
Quick questions
BATCH UPLOAD
Am I correct in thinking that when I create the table columns that date values need expressions to get correct formatting?
Both upload & delete
Can these flows be triggered using PowerApps instead of manual?
Can they be strung together in a Solution as a Parent-Child? (Delete all - Upload all)

List names have underscores
I saw this post on the link you shared.
GG says
September 9, 2021 at 4:07 pm
Works great, but underscores in SP list names should be replaced as well else you get the type resolution error from comments above. This worked for me:
json(concat(‘{“type”:”SP.Data.’, replace(replace(outputs(‘settings_2’)[‘listName’], ‘ ‘, ‘_x0020_’), ‘_’, ‘_x005f_’), ‘ListItem”}’))
Where does this go???
- Do paste it directly into the compose for the site reference? Or create another action that composes this?

Was this reply helpful? Yes No
DublinOH_User 150 on at

Like (0)

Report
Copy link

Link copied!
Thank you for your replies and valued time.
Quick questions
BATCH UPLOAD
Am I correct in thinking that when I create the table columns that date values need expressions to get correct formatting?
Both upload & delete
Can these flows be triggered using PowerApps instead of manual?
Can they be strung together in a Solution as a Parent-Child? (Delete all - Upload all)

List names have underscores
I saw this post on the link you shared.
GG says
September 9, 2021 at 4:07 pm
Works great, but underscores in SP list names should be replaced as well else you get the type resolution error from comments above. This worked for me:
json(concat(‘{“type”:”SP.Data.’, replace(replace(outputs(‘settings_2’)[‘listName’], ‘ ‘, ‘_x0020_’), ‘_’, ‘_x005f_’), ‘ListItem”}’))
Where does this go???
- Do paste it directly into the compose for the site reference? Or create another action that composes this?

Spoiler (Highlight to read)

Also found this solution for underscores in list name, but could get it to connect either:
https://www.alanps1.io/power-platform/flow/flow-stripping-unwanted-characters-or-special-characters-from-a-string/

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
Copy link

Link copied!

Date columns may need an expression to make them correct, it really depends on what format they start off in. But yes, from an Excel date column, that is highly likely to be the case.

It can be triggered by any means that can trigger a flow (so yes). But you probably wouldn't want your PowerApp waiting for a response to such a long running flow, so you'd set your response immediately after the trigger and then allow the flow to continue to execute.

Was this reply helpful? Yes No
DublinOH_User 150 on at

Like (0)

Report
Copy link

Link copied!

What is you suggestion if the SP list name contains underscores between words?
I have thought about redoing the WHOLE process but it would be a lot of work just because of 3 undrescores.
(- There are other workflows, and 2 large apps already built that would all need exported, updated and relaunched)

Was this reply helpful? Yes No
DublinOH_User 150 on at

Like (0)

Report
Copy link

Link copied!

This HTTP method is great. However; I used underscores in my Sharepoint list name and it caused an error. I cant simply remove the underscore because then the table name would be incorrect and the list cannot be found.

I can't seem to find a formula where the SP list name containing underscores can be used.

I can't rename the list or resource the data because it is currently connected to a 15 screen canvas app. That would mean reformatting the entire app. Once the source is changed, the column reference, even if it has identical names, looses so many of its references for size, height , etc.

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (1)

Report
Copy link

Link copied!

I don't think (but I am not sure) that an underscore would be a problem.

See my blog post here: How to Encode SharePoint Column Names

Underscore is not one of the escaped characters. What is the internal name of your list? You can find out using this useful blog post by @tom_riha : What is SharePoint column internal name and where to find it

Was this reply helpful? Yes No