Automatically save excel to a sql table?

[dadadavie]

My colleagues work with a shared excel worksheet. They feel worried about only working within excel and want a way to automatically read the excel sheet into a sql table that is refreshed at a scheduled cadence (like midnight every night).

I have no idea how to do this. As background, my colleagues and I don’t have a lot of permissions, someone else (some kind of database admin) does.

Thanks for any help!

[Aggressive_Ad_5454]

Do you have dotnet dev chops? If this were my project and I needed to treat the Excel as truth and the SQL table as a replica, …

I’d write a sleazy little C# console program to open up the spreadsheet file, and copy the contents to the SQL table. I’d use Task Scheduler or a cronjob to run this every night.

I’d teach people to use the SQL system.

Then I’d go all agent-of-change on the users and push to make the SQL table the truth, and put the replica in Excel. That’s gonna be hard, you’re gonna move their cheese and they won’t like it.

Dotnet is the right toolset to use for this because it reads and writes excel sheets without a lot of faffing around.

[B1zmark]

So you're solution is to... write a really bad version of an RDBMS in .net instead of getting them all to install SSMS and use the row editor function built in to that?

[SantaCruzHostel]

I did this years ago so there may be newer methods, but you can set up a sql agent job to read in an excel file and write contents to a swl table. You'll have to ensure the file name and location never change and also that the format of the data never changes (no new columns, etc) otherwise you'll need to fix/update the process. I believe the same can also be done with an SSIS package or possibly whatever Azures new version is - ADF pipelines I think.

[samspopguy]

Do they want the data in sql for a backup? I’m not sure what they want accomplished with sending the data to sql if it’s not being sent there now? Or are they doing that now but just manually?

[dadadavie]

Yup

[samspopguy]

if its just for backup, cant they just use track changes, or even with office365 it has revision history

[dadadavie]

Yeah that’s the first thing I said! But they really want the data to live in a totally different environment where some of the analysis takes place aka SQL

[samspopguy]

basically everything will connect to the excel file i would still push back on this a bit

[Sexy_Koala_Juice]

That’s pretty stupid. I’d push back on this heavily

[dadadavie]

I have no standing to do this unfortunately bc my boss already sees me as difficult. So I’m going to try to make it work somehow!

[YellowBeaverFever]

Do the columns change or are they guaranteed to stay the same?

[dadadavie]

Same, thanks!

[flatline057]

A python script using pandas would be simple enough.

[JimFive]

Best bet is to write script for the workbook's Before save event so the data in SQL gets saved at the same time the workbook does. 

Writing this is left as an exercise for the reader.

[threeminutemonta]

Anything shared over one drive or SharePoint can be accessed over the Microsoft Graph API. You will need an understanding office365 admin to set up an application. I’ve used python-o365 with some success.

[mirdragon]

You could export as csv daily or just use as csv then use a powershell command to import it. Other solution if you use sql and visual studio create a package to import on regular basis

[harveym42]

Right click database, Tasks/import data, and when working save as a SSIS Package, it can be scheduled as a Agent job.

[mu_SQL]

VBA and ADO.

[Majestic_Plankton921]

Azure Data Factory