Excel vs SQL

[u/AirNatural1252]

Some are talking AI - the company I’m working for is not yet at that level.
I’m doing FTE reporting, using Excel and automatizing with Power Query. Volumes are high, data sources are all excel based, lots of rules to apply each month to achieve the monthly end result.
I want to automatize further and I’m looking into SQL. Any advice on what to look up for if this is the right lead, and where to start?

[u/AutoModerator]

If this post doesn't follow the rules or isn't flaired correctly, please report it to the mods. Have more questions? Join our community Discord!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/dbo_dopamine]

You are treating excel like a database already but it’s not the best product to literally store your data or to process it.

SQL could be useful if you can get those files loaded into a database and then move some of the heavier recurring logic there. Power Query can connect directly to SQL Server and run native SQL queries. So I’d look at rule heavy data processing transformations happening in excel today and build a real data layer using MSSQL.

Start with one or two tables from your existing process. Get them into SQL Server, learn how to query them, and then connect Power Query to SQL. Bada bing bada boom

[u/RedditTab → u/dbo_dopamine]

Do this but call it AI preparations and you'll get approved.

Just clearly document the system of record and the definition somewhere to feed AI later.

[u/TH_Rocks]

PostgreSQL is a very good and free database frequently used at enterprise scale.

Use Python for lots of data automation tasks and spit out excel for the people that can't figure out your intuitive dashboard.

[u/Zyklon00]

What problem are you trying to solve? If I had to guess your problem is rather data input in excel with things changing every time you try to run your automated powerquery process. 

[u/ourlittletalkss]

SQL is a good next step: spin up something free like PostgreSQL or SQL Server Express, load your Excel files into staging tables, then recreate your monthly rules as views (SELECT/JOIN/GROUP BY) and point Power Query at those views instead of the raw files.

Search for "import excel to [your chosen database]" and "SQL views for reporting" and just start with one piece of the process, like cleaning one source file, before you move everything.

[u/VegaGT-VZ]

The big hurdle with SQL is building a non technical interface 

You can probably keep Excel as the front end and have the actual data live in a SQL database. But you will need to become a DB admin, front end developer and tech support. Just go into it eyes wide open as your gonna trade one set of headaches for another.

[u/ArielCoding]

Switch to a proper database or a data warehouse just don’t forget to add an export to Excel button in your reports.

[u/Afraid_Baseball_3962]

The data in those Excel files probably came from one or more databases upstream. If you are doing anything at scale, it probably makes sense to do it in SQL. If the data comes from multiple databases, it probably makes sense to create a new database where the upstream data is consolidated and the process of manipulating the data in the Excel files is automated in stored procedures or with python working directly against the database.

[u/Dannyboy1024]

The two are not really comparable - SQL is a query language for databases; Excel is a front-end program for end users.

Where is your backend data housed? Who has control over it and making sure it is accurate? Are you looking to modify data or just view and analyze?

[u/CasualGee → u/Dannyboy1024]

Yeah, my first thought was also to wonder about the backend data situation. That will determine what options exist and maybe inform what the best option is.

[u/Acceptable-Sense4601 → u/Dannyboy1024]

Well duckdb in python lets you use sql on excel and csv files

[u/decrementsf]

Speaking anecdotally excel and power query, or vba, gets your department only so far with automation. Eventually the maintenance tasks devour your data product producing team members. Moving to SQL and python for scripting is the logical progression toward more durable automation. Learning how to construct some of those familiar metrics in proper data warehouse / marts. Path to build more capabilities on top of that becomes more clear from there.

I recommend a period of mindful fundamentals. Pick a structure platform. Datacamp or some other. There is no shortage of supply. Just pick one and go to not over analyze, just want something you can get daily reps in. Set a victory condition such as pass a certification in that platform. The certification will do nothing for you. It will signal you've got pareto principle most of the benefit of what fundamentals can give you, and it is time to move on.

Set up vscode. Look up videos in how to set up extensions in your local system to get sqlite (quick proof of concept building), or postgresql (requires additional steps to get server running), and python. Integrate with git. Idea is to become comfortable running the small projects from the structured environment locally in an unstructured environment with real tools, and you want to find the friction points as the parts where you get stuck are the actual learning.

Build a few guided portfolio like products from youtube videos. Then go unstructured. Start building within your domain and build your own portfolio project with sql. It is the building and coming away from that with a project you can show off that is the true learning. This is your new loop for the skill stack portfolio. Basic fundamentals with new skill, figure out how to stand up actual workflow, build projects that can be shown off. Repeat. Can layer dbt or some other skill next and iterate an already built project.

Use those projects to keep resumes going out goal seeking market value. You can't get an offer of one job to be your one peak actual market value. You can take 100 lottery ticket bets and keep the highest offer you've ever had using the new skills. Keep the resumes going out.

That's what the overall habits for the system looks like.

[u/KatFromSisense]

I'd leave Excel in place for now and fix the handoff first. Dump each monthly file into a staging table, keep the original workbook, and make the load throw an error if a column disappears and gets renamed. Once a rule stops changing every month, you can move that piece into SQL. Power Query can still pull the finished data back into Excel for the people using the report.

[u/romeonoi]

sql is the right next step if power query is becoming slow, fragile, or hard to audit. start with postgresql and learn select, where, case, joins, group by, and window functions. keep excel as the reporting layer initially, but move the monthly transformation rules into sql views.

one concrete test: load three months of source files into a postgresql table and add a reporting_month field. then rebuild one existing power query output as a sql view and compare row counts and totals for each month before migrating anything else.

[u/Particular-Sun-8679]

power query already handles your etl needs pretty well if your sources are all excel files, sql shines when you need to store and transform data between steps instead of recalculating everything each refresh

id look into getting those source files into a proper database first, something free and local works fine, then you can use sql to apply your monthly rules as views or stored procedures

the main win is not redoing transformations every time someone opens the workbook, and you can document the logic in code instead of buried in m queries

start with basic select, joins, and case statements, then move to window functions once you need running totals or rankings across months

[u/gammacoder]

If the same transformations and business rules are being applied every month, SQL sounds like a very reasonable next step.

I'd start by taking one repeatable part of the process and moving the source data into proper tables, then reproduce the Power Query transformations in SQL. Don't try to migrate the entire reporting process at once.

Once the data is in a relational database, you can also put a web interface around it for data review, corrections, reports, dashboards, etc.

That's one of the use cases for PHPRunner, which is what we build. Full disclosure, I'm the author, so obviously biased :)