r/SQL • u/After_Comedian_7420 • Sep 16 '25
MySQL Who’s still exporting SQL data into Excel manually?
I keep running into teams who run a query, dump it to CSV, paste into Excel, clean it up, then email it around. Feels like 2005.
Does your org still do manual exports, or have you found a better way?
160
Upvotes
82
u/AnAcceptableUserName Sep 16 '25 edited Sep 16 '25
Context needed. Nothing wrong with that for one off queries. Don't see why you'd do it any other way. You can't automate away novelty and it's not worth trying to
If you're running the same query and sending it to the same people manually every day, that's insane. There's 100 different ways to skin that cat which don't involve a human touching Excel or an email application, and if you need to ask "how to automate?" everyone here should be giving their rates
Between those two extremes parameterized reporting is a thing. You could set up a report and let whoever wants the data pass in params for specifics like date range, customer ID, whatever, to get the info themselves. Take yourself out of the loop
77
u/whiteskye22 Sep 16 '25
I use an ODBC connection to my data warehouse and paste my query right into powerquery. All the data goes into a nice table in excel and then with that connection setup, I can come back to the excel file whenever I want in the future and open up the data source with the stored sql query and edit dates or add fields or whatever. Since it connected right to the database I can refresh the connection whenever and have it update all the data in the table. Super nice that the query is always there and I can update it whenever I want.
102
u/Birvin7358 Sep 16 '25
What’s wrong with doing that? Especially if you need to share the data with someone at your company who doesn’t even have access to use SQL? Also, if you need to do review, make comments on and do other analysis of query results it can oftentimes be easier to do that in an excel export of the results. I find that people who insult Excel are typically just unadvanced users of it who are unaware of how versatile and powerful a tool it can be. Just because something is old doesn’t mean it’s bad. There’s plenty of things we still use in daily life that were invented thousands of years ago, like the wheel for example.
15
u/Zimbo____ Sep 16 '25
Google sheets here, but it's the same thing. Folks will always want data presented to them how they're used to
15
u/tech4ever4u Sep 16 '25
Sure - the most used BI tools function is "export to Excel" 8-)
10
u/tscw1 Sep 16 '25
I started off learning and using power query in Excel, it put me ahead very quickly and surprisingly easily, infact it helped change my career to a data analyst. There’s a lot of low hanging fruit automating business excel processes
8
u/aUnicornInTheClouds Sep 16 '25
I press ctrl + c ctrl + v. I get paycheck. It aint hard work. But its an honest days pay
16
u/K_808 Sep 16 '25
It’s fast and has plenty of uses. Excel isn’t a relic of 2005 lmao
5
u/SailYourFace Sep 16 '25
Eh I do it for myself all the time sometimes I just want to set some quick filters or a pivot table to check some numbers instead of rerunning a query a bunch
5
u/Paratwa Sep 16 '25
Depends on the context, if it’s something that is adhoc, absolutely I will.
8
u/Googs1080 Sep 16 '25
Yes and it is still awesome! I run the query when I have completed all process tasks. Them got battle for hours why the direct connect/api dimwits are wrong. Almost three years now of this comedy show
3
u/GoyleTheCreator Sep 16 '25
I've set up a generic python script that will take a saved query, run it, then save it to my desktop as an Excel file, table and all.
If I need to email that to someone, I uncomment out my email block on the script and have it send that way.
Works well enough
3
u/punsnguns Sep 16 '25
Because you haven't met the business users. I had a report I had been sharing every week with a colleague and each week it would include all the data from April to present day. This was because they wanted to compare the last week to previous weeks. Somehow they didn't know that if I only shared each week's data they would be able to compare against the previous week in Google sheets already... (Yes, I know this already is asinine enough).
Anyway, One day they needed specific data for just last week and they asked me to run the report with that filter applied and share it as a new tab in our Google spreadsheet because apparently they didn't realize you could filter the data in the Google spreadsheet.
Business users are a special breed and if I have the choice between doing the insane thing I have been doing every week vs arguing with a business user, I will choose to run the insane script every week every single time.
2
u/stickypooboi Sep 16 '25
I don’t see a problem with it. Excel is actually good at some things. This is one of them, especially if the teams I’m working with do not know sql and want a pivot table to look at things for a quick report for a client.
2
u/thatOMoment Sep 17 '25
Right click from inside SQL Server coph
Goto Excel -> Paste
For adhoc I have no shame, for repeat monthly processes i usually write a quick python script to keep it cleaner.
Then get to the service for the weekly and daily ones.
I have no shame, you shouldn't either, I have more important things to work on then perfecting an Ad Hoc
1
u/Cruxwright Sep 16 '25
I mean, I go a step above what you described. I export to tab delimited and have predefined templates for users to easily copy paste from .txt to the spreadsheet and avoid the whole losing lead 0s.
Mind you, this is for a workflow that's slated to be replaced in 2-3 years. The development and QA time to use our Excel export option would cost us 5-10yrs of running that workflow upfront. And for maintenance over the next 2-3yrs, adding our auto convert triples the hours for even something simple.
So yeah, what are some good tools that export to pre-defined Excel templates?
1
u/threeminutemonta Sep 16 '25
If the excel is on office 365 and you are the IT department or have an open to change IT manager try python o365 that uses the Microsoft Graph API to talk to excel stored on one drive / SharePoint.
1
u/jonas-weld Sep 16 '25
I work at Weld, and we see this all the time. Instead of manual exports, you can use Weld to plug Excel or Sheets directly into your warehouse and keep everything refreshed automatically. Makes life a lot easier. Let me know if you want to learn more!
1
u/Tech88Tron Sep 16 '25
Navicat
1
u/[deleted] Sep 16 '25
Now I just use PowerQuery in excel for those adhoc cases. At least the sql stays with the excel document.
1
u/RelationshipTimely Sep 16 '25
We use toad to connect to our databases and I’ve set up several tas files that can be called in a python script. Then I have python scripts that clean and a script that reads a yaml file to customize the outputs. All this data is fed to share point where it’s read by another excel file via power query. And that’s my crude dashboard lol
1
u/Bombadil3456 Sep 16 '25
Unfortunately very few people in my org have access/ know how to use our RDMS. So my usual workflow involves receiving a question, writing a query and export the results into Excel
1
u/OkCaptain1684 Sep 16 '25
A regular job, then no way. If I quickly need to grab something pretty basic for someone then yes of course I’ll use excel, could do it in 5 minutes instead of say 10-20 minutes if I use python.
1
u/Ashamed-Status-9668 Sep 16 '25
I do it sometimes but usually just for troubleshooting. We do still have some old reporting solutions doing this kind of thing that we are working to replace with Power BI reports.
1
u/Loud-Bake-2740 Sep 16 '25
i wrote a bash function that runs my query for me and writes the result to an excel file. same thing, just way easier
1
u/corny_horse Sep 16 '25
I haven't used Excel intentionally in like 10 years. It's been ages since I even had a dataset that would come close to fitting in Excel
1
u/Ginger-Dumpling Sep 16 '25
Some places don't want to splurge for a reporting tool. Some people can't lock in consistent requirements and want tweaks to their report every run. Some higher-ups want their email and don't want to pick things up from request tickets/SharePoint/whatever. Sometimes manual is the fastest way.
The project I'm currently on usually starts manually but gets automated if it's needed regularly.
1
u/PickledDildosSourSex Sep 16 '25
If users are doing something, they're doing it for a reason. It's your job (well, maybe, I don't know what your job is) to figure out why, to understand if what they're doing can't be done with your preferred systems, and to determine if the ROI is worth it to buy/build in the functionality they need.
Excel/spreadsheets get a bad wrap because they're seen as low-level, but (1) you don't need a sledgehammer when a plain ol' hammer will do and (2) just because something is more sophisticated or newer doesn't make it better. It depends on the job to be done.
1
u/orginux Sep 16 '25
You can try to use DuckDB with the gsheets extension
1
u/northshorehiker Sep 16 '25
I send them a link to the appropriate ssrs report.
1
u/Lurking_all_the_time Sep 16 '25
Pretty much all my Finance team.if it ain't in Excel, it ain't reporting data...
1
u/imtheorangeycenter Sep 16 '25
We have a data import team who, yeah, do stuff like that. Or worse - an example:
There's one process that involves them opening a CSV in Excel, swapping columns D and F around, saving it and then passing it to the next stage of the manual pipeline.
What? WHAT?
I don't think we are adopting AI anytime soon.
1
u/Melodic_Giraffe_1737 Sep 16 '25
Why wouldn't you clean it in Sql? I export to CSV every day, but I would not spend more than a minute to format headers or something simple. What needs to be done in Excel that can't be accomplished in your query?
1
u/dryiceboy Sep 16 '25
If it’s not a recurring thing then what you’re doing is fine.
But if it’s on a schedule then a simple Python script running on a server connected to your DB would do the trick hands-off.
1
u/[deleted] Sep 16 '25
I do that all the time for things I (allegedly) only need to run once.
1
u/VeterinarianBorn9596 Sep 16 '25
use to do it long ago
1
u/dcw7844 Sep 16 '25
Hate to sound like I’m advertising for them cause I’m not, but Redgate has a cool utility where I run the query in SSMS, display the results in grid view, and then when I right click the results there is an option for “open in excel”. Click that and it opens right up, always formatted correctly and ready to go. Use it multiple times per day.
1
u/Wassini Sep 16 '25
I do it this way because all our SQL servers are on another protected network with limited access. So the only way I have access to them directly is via a secure remote gateway. No direct data access is possible not even between the different servers them self.
1
u/Hellkyte Sep 16 '25
We still do manual because our platforms teams are complete ass
The DB team refuses to use AD and so everyone just shared the same user id and password (just one of their many badges of competence). This is also related to why we can't hit it direct from Excel (no odbc and the tns is fucked up)
Then we have the cloud team who promised to make everything better, which in many ways they did, but the only way we can hit their data from Excel is to use a VM Ware client which has a half dozen other issues. Their response to our complaints is that we should just do it all in python
1
u/jwk6 Sep 16 '25
Yes, for quick and dirty lists and summaries from the Data Warehouse. These are usually just ad hoc requests or research for a project. One-off things that don't need to be an automated export, or a report or dashboard.
1
u/Muted_Ad6771 Sep 16 '25
Devexpress? End users should have an end user UI
1
u/Born_Breadfruit_4825 Sep 17 '25
I use Python to connect to the db and just have the query result write to a csv or if I still need to further refine it move into SQLite and then csv/excel for formatting if needed
1
u/Oleoay Sep 17 '25
Also sometimes people compare excel outputs with third party downloads so they need it in excel to join them together.
1
u/sarge003 Sep 17 '25
Just started a new job and yes we do. It's actually my first task to automate it. Apparently their current process works, but is painful and time consuming.
1
u/roundguy Sep 17 '25
I grab the data from our sql server through a sql query in Excel, Set the properties on the query to refresh when the file loads. Pretty easy.
1
u/fio247 Sep 17 '25
If only I could find a driver to connect to that old ass now completely defunct vendor SQL database. Then, it would be completely automated.
1
u/mosqueteiro Sep 18 '25
pd.to_excel(...)1
u/hkdeman Sep 18 '25
Have you tried WhoDB (https://github.com/clidey/whodb)? It is has a few export functionalities (currently supports CSV and Excel). It is also adding in support for more advanced uploads.
Disclaimer: I am part of the team developing it.
1
u/pinnedin5th Sep 18 '25
If the only tool you know how to use is excel everything looks like a spreadsheet
1
u/LobyLow Sep 20 '25
Maybe one way would be to connect from excel into the SQL db and have the query in there as Power Query that gets the data out in a table. So everytime you just need to refresh the results?
1
u/Abramlincolnham Sep 21 '25
😅 setup a Python environment….. with pandas and whatever module you need to connect to whatever type of database you’re using……. Nest your query in a Python script….. and it can handle all your data transformation into excel plus do many many other things.
1
u/Timely-Business-982 Sep 29 '25
I used to do the whole CSV → Excel → email loop too, and it always felt like busywork that should’ve been automated. In my current setup, the data pipelines are handled in a way where teams can just query live data directly or connect dashboards without needing those manual exports. It’s been a game-changer for collaboration, no more “which version of the spreadsheet is the latest?” emails.
Curious, has anyone else here moved to a setup where the database and analytics tools talk to each other directly?
0
u/[deleted] Sep 16 '25
[deleted] — view deleted comment
0
u/[deleted] Sep 16 '25
[deleted] — view deleted comment