4 ms·
Segue to something that I've been fighting with for years: Do you have a suggestion for working with CSVs in the CLI? Something that will show a table, with a
by dotancohen 6y ago
Segue to something that I've been fighting with for years: Do you have a suggestion for working with CSVs in the CLI?
Something that will show a table, with a single pixel border between cells, that allows searching for a value, copying a cell?
- lmns 6y agoSqlite3 can import and export CSV via the CLI application. Both Ruby and Python provide CSV parsers in their standard library.
- 7thaccount 6y agoDoesn't it convert everything to strings including numbers? Or can you specify types?
- dotancohen 6y agoThank you. I've used sqlite, but being able to query the data is not the same as being able to browse that data. For instance, consider a project that I'm currently working on. Google Docs sheet exported to CSV. The file documents dozens of servers used as load balancers, websites, and database servers. The files has gone from MS Excel to Google Docs, and been maintained by four admins over 12 years. There are no columns per se, rather "sections of rows", each of which isn't even consistent in itself. One section is double columned, with the top row listing public IP address, login name, etc and the second row listing LAN IP address, login password, etc. sqlite is useless on these files and frankly I've seen these things more often than well-formatted spreadsheets at every company I've ever worked with. Nobody but software developers treat spreadsheet columns as database fields.
- lmns 6y agoI know, there is no easy fix for that.
- dotancohen 6y agoJust a heads-up, if you're interested. I'm writing an app in Python for browsing spreadsheets: https://github.com/dotancohen/osheet https://github.com/dotancohen/osheet
- rnestler 6y agoThere is https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv but it doesn't seem to print with borders between cells.
- dotancohen 6y agoThat's good for data formatted like a database. I need something to just browse the cells, so I'm writing an app in Python for browsing spreadsheets: https://github.com/dotancohen/osheet https://github.com/dotancohen/osheet
- walrus 6y agoIt depends what I'm doing, but I typically use one of the following (starting with the most frequent): 1. IPython with Pandas if I expect to do any in-depth exploration/manipulation of the data. 2. A short Python script like `python -c 'import csv, sys; r = csv.reader(sys.stdin); ...' <data.csv` if it needs to run on someone else's machine. 3. https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv if I want to quickly munge some data or extract a field. 4. Gnumeric if I want a GUI. 5. https://github.com/andmarti1424/sc-im https://github.com/andmarti1424/sc-im if I want a TUI. This is closest to what you were asking for.
- dotancohen 6y ago> 5. https://github.com/andmarti1424/sc-im if I want > a TUI. This is closest to what you were asking for. Thank you! Yes, this seems to be almost perfect. I cannot believe that there is no "arbitrary string search" feature, but I can grep in another terminal window at least. Thank you.
- dotancohen 6y agoJust a heads-up, if you're interested. Only SC-IM was fit for the non-database-format files I need to work with. But it does not have a search function! So I'm writing an app in Python for browsing spreadsheets: https://github.com/dotancohen/osheet https://github.com/dotancohen/osheet
- bsg75 6y agohttps://www.visidata.org/ https://www.visidata.org/
- dotancohen 6y agoThat's good for data formatted like a database. I need something to just browse the cells, so I'm writing an app in Python for browsing spreadsheets: https://github.com/dotancohen/osheet https://github.com/dotancohen/osheet
- mauvehaus 6y agoIf I'm deep enough into a csv file that I'm worried about everything I noted above, I usually just load the data into Postgres and trust that their devs got it right. Pretty presentation is free at that point :-)
- dotancohen 6y agoPostgress or even sqlite are great if the CSV is formatted like a database, with each column a distinct field. Alas, most spreadsheets created by non-software-devs are not so strict. I need to browse the cells in a something like curses.
- ydant 6y agoI pretty much exclusively rely on: csvlook -I | less -S or csvformat -T | less -S (-I to avoid mixed data-types being displayed weird, but I don't consistently use that) from csvkit - https://csvkit.readthedocs.io/en/1.0.2/scripts/csvlook.html https://csvkit.readthedocs.io/en/1.0.2/scripts/csvlook.html Tons of useful CSV tools in there - csvcut, csvjoin, csvgrep. csvformat -T Makes most CSV much more manageable for a shell pipeline, as tabs are easy to deal with and unlikely to be in the actual data
- dotancohen 6y agoJust a reply to myself, for anybody else looking for an application to browse spreadsheets on the CLI. There are a few CLI applications for working with data stored in a CSV file, so long as the data is formed as a database: each row as a data entry, each column as a database field. The single CLI app that run on Linux for browsing a spreadsheet that is not formed as a database, SC-IM, does not support searching for arbitrary text! Therefore I am writing osheet: A Linux CLI app for browsing a spreadsheet. It currently supports only XLS files, because that is what I need. It currently crashes a lot. It currently has no features other than searching. It won't even scroll yet to display all the rows and columns. I just banged it out last night when I should have been sleeping. But I'm working on it, you are invited to test it, report bugs, and pull requests are of course welcome. It's written in Python and Curses. https://github.com/dotancohen/osheet https://github.com/dotancohen/osheet