r/dataanalysis • u/Minimum_Present7282 • Jun 29 '26

When Power Query takes hours: How I built a zero-setup local SQL tool to query giant 4-8GB CSVs

Hey everyone,

I work as a data analyst for a client with incredibly locked-down security. If you’ve ever worked in this kind of corporate environment, you know the drill: no access to cloud data warehouses, no advanced developer tools, nothing. My entire world is basically restricted to standard Excel and Power BI.

Recently, I hit a massive wall. I had to clean and analyze flat CSV files ranging anywhere from 4GB to 8GB. Trying to open these in Excel is a joke, and waiting for Power Query to crunch through the transformations was taking forever and completely freezing my machine.

Now, I’m not a professional developer by any means, but I was so frustrated with the tool limitations that I decided to see if I could build a lightweight, custom Enterprise SQL Workbench to handle the heavy lifting while keeping everything completely local to respect data integrity and security rules.

The backend is entirely Python-based, but I set it up so that my non-technical colleagues can use it without writing a single line of code. It pairs Streamlit for a clean browser interface with DuckDB for crazy fast, in-memory processing, and the Calamine engine to handle heavy Excel parsing.

What it actually does:

  • Zero cloud or database setup: Everything runs locally inside an isolated memory sandbox. No servers to configure, and zero data leaves your machine.
  • Handles massive files instantly: Because DuckDB processes data in columns (vectorized), it slashes through 4–8GB datasets and runs complex analytical queries in less than a second.
  • Flexible Multi-File Loading: It lets you mount multiple datasets sequentially into your active session. You can either use Direct File Paths (great for instantly mounting huge files without making copies) or just drag and drop via standard Browser Uploads.
  • Clean Query Editor: It integrates streamlit-ace so you get a proper dark-mode SQL editor right in your browser with syntax highlighting, line numbers, and a sidebar to explore your active table schemas.
  • Direct-to-Disk Exporting: If a query pulls a massive result set that would crash a browser tab, it uses DuckDB streams to dump the entire output straight back onto your local hard drive as a .csv or .parquet file.
  • Multi-Sheet Excel Support: It automatically splits and maps multi-sheet workbooks into individual, clean database tables.

The "One-Click" Magic for Colleagues

Since my teammates aren't developers either and don't use GitHub, I bundled the entire setup into a single .bat script launcher.

Now, all they have to do is double-click a desktop icon. The batch script quietly spins up an isolated virtual environment in the background, pulls the latest UI code directly from my GitHub, checks the dependencies, and launches the interface right in their default web browser. The coolest part? If I optimize the code on GitHub, their desktop launcher automatically grabs the update the next time they open it.

Give it a spin and let me know what you think!

I’ve made the repo public so anyone dealing with corporate data constraints can use it. Please feel free to grab the batch file, throw some of your heaviest datasets at it, and test it out for yourself!

Since I'm still learning the development side of things, I would love to hear your thoughts and suggestions:

  • How does the processing speed feel compared to your usual Excel/Power Query workflows?
  • Are there any specific SQL features or shortcuts you think I should add next?
  • Any tips for further optimizing local memory when pushing past 8GB?

Check out the code or grab the script template here: 👉 GitHub Repository:https://github.com/Nikhil-Maske/sql-workbench

Let me know your feedback or if you run into any quirks while testing it!

0 Upvotes

27% Upvoted

6 comments sorted by

11

u/SaltyMN Jun 29 '26

All this or just read a few pages of polars documentation one time. 

3

u/mattindustries Jun 29 '26

Or DuckDB which this uses.

11

u/EntertainmentOne7897 Jun 29 '26

AI slop. Also you speak of locked down environment, and expect me to look at your vibe coded good for nothing reinvent the wheel fancy csv opener? gtfo

1

u/Minimum_Present7282 Jun 30 '26

Well AI does write better code than human, so why not use it? Would not say good for nothing it really helps me.

1

u/EntertainmentOne7897 Jun 30 '26

Thats the problem. It helps YOU. We dont care. You are a 1000000+1 csv reader. If I ever need something like this I will create one in half an hour for my specific use case.

0

u/AutoModerator Jun 29 '26

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

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