Advice needed please. Importing Sharepoint Text file into List and Deleting previous days data

[tucker61]

I have a text file, The text file is delimited by the number of characters, This file is dropped into a sharepoint site on a daily basis. The previous days file is overwritten.
I have a access database that needs a connection to that file, The data is imported daily, and appended to a table which is stored in SqlServer

Currently i run a task in Task scheduler, that copies the file onto a local drive, then access imports the data and appends into the SQL Back end.
but this only works when i am logged on - so trying to think of a way it can run autonomously.

I was thinking, that if i created a list in Sharepoint, then on a daily basis i would delete the previous days data, and then update with todays data, but when i have started this Delete action as a Flow it is taking far too long.

What would be the most efficient way to delete this data (Current list has 17000 rows, and is taking ages to delete,- and then i would need to update with the data from todays file ?

I am limited to any software i can install on my laptop, so mainly trying to do this with Office 365.

Any help appreciated.

 

[theDBguy]

Just thinking out loud but couldn't you use a flow to read the text file directly into the sql server? Why do you need a list in between?

 

[GPGeorge]

As I understand the current scenario, the only role SharePoint needs to play in this process is the transfer point for the incoming text file. The SharePoint list was introduced only as an effort to bypass the problem of running Task Scheduler with an account that requires you to log in. Am I understanding that correctly?

If so, I think you should have the option of running the Task Scheduler from a service account that can run unattended, although the computer itself would need to be running. Not my strong suit, but I understand it's possible.

Also, I think you should be able to create a flow as theDBGuy suggests as well.

 

[tucker61]

Thanks, one to look at tomorrow

 

[Isaac]

I agree, look into Power Automate, or maybe you already are, as I noticed you put your post in Power Apps forum ....
You can also use an SSIS package if you have SQL Server, and as long as Integration Services is setup there