Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

johnkerl.org

Post reply on HN
Print view View original

61–70 of 81 posts

Re: Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

#61

Post by iampims »

jakobegger wrote: post #40

It looks like this tool doesn't support semicolon-separated CSV files, quotes, or numbers using a comma as decimal separator. Too bad. It's a pity that the CSV file format is so fragmented. It's all too common that a CSV file written by tool A can't be read by tool B. Only very few tools do it right. For example, I think the ruby CSV module has a heuristic to automatically detect the "style" of CSV files, which is pr…

I found the pandas tests to be a great source for implementing robust CSV parsers:

https://github.com/pydata/pandas/blob/master/pandas/io/tests...

Re: Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

#62

Post by technofiend »

san_dimitri wrote: post #35

I am surprised no ones looking at Apache Drill. https://drill.apache.org

I've used it out of curiosity and it works, but honestly I just fell back to my primary weapon of choice: Pentaho. This isn't because I'm not comfortable searching data at the command line, it's more because of momentum in that all my other data is already in a database and I usually need to join datasets, not just search them.

It's trivial to create a two hop transformation in Pentaho. The first step reads your data out of a CSV, the second loads it to a table. Pentaho will generate the DDL needed to create the table based on discovered field names and types found in the import step based on data introspection.

Having said that I've often wished for a tool exactly like this that's lighter weight than drill (and doesn't expose my data on a web page like drill does) so I will definitely bookmark this for the next time it's needed. Hopefully it makes it into the Redhat EPEL.

Re: Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

#63

Post by technofiend »

loevborg wrote: post #37
michaelmior wrote: post #8

I love csvkit[0] for this purpose [0] https://github.com/onyxfish/csvkit

csvkit is excellent. I like `csvlook` for display csv files in the terminal. Can anyone recommend an easy way to convert csv to xls on Linux? I know that libreoffice and gnumeric can do it in headless mode, but it seems terrible overkill.

It is mega overkill but Pentaho does conversion between a long list of file and database formats. I'm more mentioning it so you can check it out in case you ever see a more complex requirement, or want to learn the tool. Once I learned it the one off stuff is trivial. It takes me 5-10 mins to import an xls, let the tool examine the file for field names and types, hook that to a table load step, generate the ddl, edit the ddl to remove spaces from field names and then execute the ddl followed by the loader job.

Since you're going flat file to flat file it would be faster for you as no field name edits are required. You just create a csv input step, xls output step and that's it, save and run the transformation and you're done.

Re: Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

#64

Post by acveilleux »

ggrothendieck wrote: post #39
acveilleux wrote: post #9

I have my own little toolkit of csv parsing tools that do most of these (using python's csv module.) I suspect most people who do deal with bulk CSV files all the time do as well. I'd be curious about how gracefully it handles large CSV files. The lack of quoting support kills it for my use case however. I wrote my own tools, starting with csv_cut because cut(1) didn't do quoting.

Regarding quoting support, check out https://github.com/dbro/csvquote

Not sufficient, but close. It substitutes the quoted character for another one.

Re: Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

#65

Post by singingfish »

pyre wrote: post #44
singingfish wrote: post #38

Earlier quoted context omitted.

does it bring any advantages over https://metacpan.org/pod/DBD::CSV ? (not being perl is not an advantage).

Well, DBD::CSV is just a driver for DBI, not a command that is "unix pipeline friendly"... So there's that.

Perl is pipeline friendly though ...

Re: Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

#66

Post by dbro »

acveilleux wrote: post #64
ggrothendieck wrote: post #39

Earlier quoted context omitted.

Regarding quoting support, check out https://github.com/dbro/csvquote

Not sufficient, but close. It substitutes the quoted character for another one.

Hi- I'm curious to know what your use case is. Can you explain why substitution is not sufficient?

Re: Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

#67

Post by [deleted] »

ghshephard wrote: post #20
natch wrote: post #12

This is lovely. Portable C, awesome.

Not totally portable. They have built some ctags option dependencies that I'm going to have to futz with in order to get the makefile to run. 12:58 shephard:c shephard$ make all ctags -R . ctags: illegal option -- R usage: ctags [-BFadtuwvx] [-f tagsfile] file ... make: *** [tags] Error 1 [Edit - No "-R" option for ctags in OS X 10.8.5, or in OpenBSD Current - http://www.openbsd.org/cgi-bin/man.cgi/OpenBSD-current/ma…

[deleted]

Re: Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

#68

Post by john_kerl »

Several feedbacks incorporated at

https://github.com/johnkerl/miller/commit/a1d117d3b299bbf273...

https://github.com/johnkerl/miller/commit/7aead02c71a76fb281...

Also there is one issue closed (thanks epilanthanomai!) and two open. Remaining feedback items on the miller todo.txt.

Thanks all for the initial feedback -- exactly what I was looking for by posting here!

Re: Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

#69

Post by harelba »

dajobe wrote: post #11

There is also the (impossible to google) https://github.com/harelba/q that lets you SQL over CSV and is unix pipeline friendly.

Hi. q's developer here. Thanks for the mention and kind words everyone.

I've considered the searchability issue when deciding on a name for it, but eventually favored the day-to-day minimum-typing short name over better searchability.

Anyway, you can search for "harelba q" in order to find it if needed.

Harel @harelba

Re: Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV

#70

Post by teh_klev »

Pamar wrote: post #19

Can anyone suggest something similar for Windows?

Take a look at LogParser:

https://technet.microsoft.com/en-us/scriptcenter/dd919274.as...

http://www.microsoft.com/en-us/download/details.aspx?id=2465...

It handles around 20 different input formats and is pretty fast. Has a COM API and is extensible. we use it to capture event log data from our windows platform. Has an edge over PowerShell when it comes to speed for certain types of tasks.

Post reply on HN