5 ms·
I always use sqlite3 for working with large CSV files: $ sqlite3 $ .mode csv $ .import my.csv foo $ SELECT * FROM foo WHERE name = 'bar'; It r
by yung_steezy 5y ago
I always use sqlite3 for working with large CSV files:
$ sqlite3
$ .mode csv
$ .import my.csv foo
$ SELECT * FROM foo WHERE name = 'bar';
It reads the header in automatically for the field names and then stores all the values as strings.
- greazy 5y agoThis is extremely ugly, I have no idea why you'd do this. If you're going to call a tool in interactive mode on the CLI, then use R for proper data handling.
- skrebbel 5y agoYou can't understand that there are people who know SQL and not R?
- momirlan 5y agoI don't think the comment refers to people, just to better ways of doing things. People learn.
- lugged 5y agoIn my experience people rarely learn anything after you insult them and call their work ugly. If AP had a "better way of doing things" maybe they should have actually given us an example of what it looks like. Right now, all I think about R is that snobby, gatekeeping assholes use it to do things I can already do easily in SQL.
- deleted 5y ago[deleted]
- deleted 5y ago[deleted]
- halayli 5y agoIf there is anything ugly here, it's your communication. Aside from the fact that OP's example can run in a non-interactive mode, it makes absolutely no difference to the discussion.
- sdmac99 5y agoduckdb is another good option as well.
- tyingq 5y agoThis stack exchange answer makes that into a fairly simple bash alias: https://unix.stackexchange.com/questions/424555/how-to-insert-csv-data-into-an-sqlite-table-via-a-shell-pipe/642364#642364 https://unix.stackexchange.com/questions/424555/how-to-inser...
- inanutshellus 5y agoholy moly, that's rad. definitely adding this to my toolbox, thanks!
- noisy_boy 5y agoGreat tip. A simple utility script can be used for repeated use/customization: https://pastebin.com/qEkimk7W https://pastebin.com/qEkimk7W Another advantage is that this can be executed to import as part of initialization and get to sqlite prompt with the table ready for sql execution: $ csv_to_table.sh sample-test.csv Or it can be executed using heredoc for non-interactive execution as well: $ csv_to_table.sh sample-test.csv <<-CMD select * from sample_test limit 10; CMD ... output ...
- majkinetor 5y agoNot bad. Here is the Windows equivalent with Excel separator: PS> gsudo choco install sqlite3 PS > sqlite3 sqlite> .mode csv sqlite> .separator ; \n sqlite> .import my.csv foo sqlite> SELECT * FROM foo WHERE name = 'bar'; For CSV that contain up to several 100k records native PowerShell solution is way faster and more practical: Get-Content my.csv | ConvertFrom-Csv | ? name -eq bar FYI, I maintain miller choco package: https://community.chocolatey.org/packages/miller https://community.chocolatey.org/packages/miller
- psanford 5y agoI do the same. I wrote[0] a little wrapper around this workflow that does a few additional niceties: 1) normalizes column names and removes whitespace, 2) supports importing multiple csv files without importing the header line as a record. [0]: https://github.com/psanford/csv2sqlite https://github.com/psanford/csv2sqlite