5 ms·

Yesss. Glad to see this getting some love. I use this tool very frequently in my day-to-day and my work would be so much harder without it. A lot of folks ment

• nojito 5y ago
  Even shorter with r and probably a good deal quicker r -e "data.table::fread(data.csv)[col1 != "foo", .(1,3)]"
  • burntsushi 5y ago
    I don't know anything about R and I couldn't get this to work. First, I had to install R. Then it told me that 'data.table' was unrecognized. So I searched around and installed that. Then, 'data.csv' is not quoted. So fixed that. So I tried running it on some data I have: > data.table::fread("version_downloads.csv")[date == "foo", .(1,3)] Error: character string is not in a standard unambiguous format Execution halted Dunno how to fix this now.
    • splithalf 5y ago
      Fix your strings. :)
      • burntsushi 5y ago
        That's not helpful. CSV data is what it is. If you're advertising an alternative for processing CSV data but it chokes on valid CSV, then that's kind of a show-stopper IMO. And I have no idea which string to fix or how to even fix it. The failure mode here is not great.
    • nojito 5y ago
      Might be the datatypes. fread automagically tries to determine the datatypes of your columns by jumping around the file as it's being read. https://www.rdocumentation.org/packages/data.table/versions/1.14.0/topics/fread https://www.rdocumentation.org/packages/data.table/versions/... Or you can pass colClasses = 'character' as an argument to read everything as a string, but that will be much much slower. fread also reads from stdin so you can pass bash commands into the first parameter instead of using bash to run r via R -e.
      • burntsushi 5y ago
        I guess that's nifty, but it makes it much less universal. It's also not clear to me why reading everything as a string would be slower. If anything, it seems to me like it would be faster, since you wouldn't need to do type guessing. Full disclosure, I'm the author of 'xsv' and the thing I was most interested in here was performance.
• tacostakohashi 5y ago
  Is that better than: cut -d, -f1,3 data.csv | grep -v '^foo' I'm sure there are some things where you need sql/q, but this isn't one of them, and there is a lot of utility in productive with bare POSIX tools.
  • forgetfulness 5y ago
    Expressing an 'or' or having any logic to the condition becomes quickly annoying with usual *nix tools, and most people use SQL much more often than they forget how to use sed and awk.
    • tacostakohashi 5y ago
      egrep '(a|b)'
  • burntsushi 5y ago
    If always splitting on ',' is correct for your data, then your solution is probably fine. You probably want '^foo,' though, otherwise yours includes things where 'foo' is just a prefix of the first column value. But CSV permits quoted fields that contain literal ','. So in that case, splitting on ',' would be incorrect.
  • timeinput 5y ago
    This breaks down when you have commas in your CSV data like if you had something like locations as the quoted string "Atlanta, GA"
• borisvl 5y ago
  If you want the best of two worlds, you can also give bigbash [1] a try. It generates a bash statement using unix tools from a sql query that can be run on csv files. [1] http://bigbash.it/ http://bigbash.it/
• e12e 5y ago
  Why use xsv in this case (as opposed to SELECT col1, col3)?
  • nkcmr 5y ago
    Mostly for just demonstrating how it combines with other tools in my workflow over pipes :D
• dima55 5y ago
  And yet another set of tools is vnlog (https://github.com/dkogan/vnlog https://github.com/dkogan/vnlog). It assumes you have whitespace-separated data, with a legend comment, then you can do the above thing with < data.vnl vnl-filter -p thiscolumn,thatcolumn 'thiscolumn != "foo"' If you don't strictly need csv, those tools are quite nice, and have a really friendly learning curve. Disclaimer: I'm the author
• SahAssar 5y ago
  Is that any different than using sqlite3 directly like this? cat data.csv | sqlite3 -csv ':memory:' '.import /dev/stdin data' 'select col1, col3 from data where col1 != "foo"' You can of course use the .import command directly on the file, but keeping it there to show piping.
  • psanford 5y ago
    sqlite has become my preferred way to work with csv files in an adhoc manner. I do it often enough that I wrote a small wrapper around sqlite to make it easier to work with multiple csv files with overlapping column names, and to normalize column names: https://github.com/psanford/csv2sqlite https://github.com/psanford/csv2sqlite
  • anitil 5y ago
    Using sqlite3 for any task makes me so happy, I try to use it even when it's overkill
  • deleted 5y ago
    [deleted]