dbro

anno scorsodiscuss

Forgive me for promoting this that I wrote:

csvquote: https://github.com/dbro/csvquote

Especially for use with existing shell text processing tools, eg. cut, sort, wc, etc.

dbro

3 anni fadiscuss

Yes, this is what csvquote does. It does nothing else, just this so that programs like awk, sed, cut, etc. can work properly.

https://github.com/dbro/csvquote

dbro

3 anni fadiscuss

That is correct, the data needs to be simple where the delimiter characters are never embedded inside a quoted field. I wrote a simple (and fast) utility to ensure that CSV files are handled properly by all the standard UNIX command line data tools. If you like using awk, sed, cut, tr, etc. then it may be useful to you.

<https://github.com/dbro/csvquote>

Using it with the first example command from this article would be

  csvquote file.csv | awk -F, '{print $1}' | csvquote -u

By using the "-u" flag in the last step of the pipeline, all of the problematic quoted delimiters get restored.

dbro

5 anni fadiscuss

There is a small program I wrote called csvquote[1] that can be used to sanitize input to awk so it can rely on delimiter characters (commas) to always mean delimiters. The results from awk then get piped through the same program at the end to restore the commas inside the field values.

In principle:

  cat textfile.csv | csvquote | awk -f myprogram.awk | csvquote -u > output.csv

Also works for other text processing tools like cut, sed, sort, etc.

[1] https://github.com/dbro/csvquote

dbro

6 anni fadiscuss

Good idea! Looks similar to something I wrote called csvquote https://github.com/dbro/csvquote , which enables awk and other command line text tools to work with CSV data that contains embedded commas and newlines.

dbro

7 anni fadiscuss

To simplify working with CSV data using command line tools, I wrote csvquote ( https://github.com/dbro/csvquote ). There are some examples on that page that show how it works with awk, cut, sed, etc.

dbro

7 anni fadiscuss

While not exactly what you asked for, I wrote something similar called csvquote ( https://github.com/dbro/csvquote ) which transforms "typical" CSV or TSV data to use the ASCII characters for field separators and record separators, and also allows for a reverse transform back to regular CSV or TSV files.

It is handy for pipelining UNIX commands so that they can handle data that includes commas and newlines inside fields. In this example, csvquote is used twice in the pipeline, first at the beginning to make the transformation to ASCII separators and then at the end to undo the transformation so that the separators are human-readable.

> csvquote foobar.csv | cut -d ',' -f 5 | sort | uniq -c | csvquote -u

It doesn't yet have any built-in awareness of UTF or multi-byte characters, but I'd be happy to receive a pull request if it's something you're able to offer.

dbro

8 anni fadiscuss

You might want to check out https://github.com/dbro/csvquote which helps awl and other text tools handle csv files which have quoted strings as values.

dbro

11 anni fadiscuss

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

dbro

11 anni fadiscuss

Here's another suggestion for the criticism section (which is a good idea for any open-minded project to include):

Instead of using a separate set of tools to work with CSV data, use an adapter to allow existing tools to work around CSV's quirky quoting methods.

csvquote (https://github.com/dbro/csvquote) enables the regular UNIX command line text toolset (like cut, wc, awk, etc.) to work properly with CSV data.

dbro

12 anni fadiscuss

yes!

https://github.com/dbro/csvquote

csvquote allows UNIX tools to work properly with quoted fields that contain delimiters inside the data. It is a simple translation tool that temporarily replaces the special characters occurring inside quotes with harmless non-printing characters. You do it as a first step in the pipeline, then do the regular operations using UNIX tools, and the last step of of the pipeline restores those troublesome characters back inside the data fields.

dbro

12 anni fadiscuss

That's correct, and as you illustrate it's the possibility to have newlines and commas inside quoted fields that complicates things for grep/awk/cut/etc.

So instead of making a more complex version of tools like grep, we can make the data simple for these tools to understand. That's what https://github.com/dbro/csvquote does. It can be run in a pipeline before the grep stage, and allow grep/cut/awk/... to work with unambiguous field and record delimiters. Then it can restore the newlines and commas inside the quoted fields at the end of the pipeline.

dbro

12 anni fadiscuss

Usually the person parsing the CSV data doesn't have control over the way the data gets written. If he did, he would probably prefer something like protocol buffers. CSV is the lowest common denominator, so it's a useful format for exchanging data between different organizations that are producing and consuming the data.

https://github.com/dbro/csvquote is a small and fast script that can replace ambiguous separators (commas and newlines, for example) inside quoted fields, so that other text tools can work with a simple grammar. After that work is done, the ambiguous commas inside quoted fields get restored. I wrote it to use unix shell tools like cut, awk, ... with CSV files containing millions of records.

dbro

12 anni fadiscuss

Yes it's possible. I did this a few years ago as a learning project, and the relevant lua scripts can be seen here: https://gist.github.com/dbro/9920666.

... but I wouldn't claim that it's done efficiently. From the comments in the code's tests:

    # some results based on running speed tests on a lightweight netbook:
    # standard error = 0.01, 10kb of registers
    # < 1 ms per init()
    # < 1 ms per update()
    # < 1 ms per count() for single set
    # ~30 ms per set for count() of union of sets

dbro

12 anni fadiscuss

that's exactly what https://github.com/dbro/csvquote does for commas and newlines both.

dbro

12 anni fadiscuss

Thanks for bringing up csvquote. I wrote it last year, and am happy to hear that other people find it useful.

It is indeed a simple state machine (see https://github.com/dbro/csvquote/blob/master/csvquote.c), and it translates CSV/TSV files into files which follow the spirit of what's described in the original article in this thread.

But instead of using control characters as separators, it uses them INSIDE the quoted fields. This makes it easy to work with the standard UNIX text manipulation tools, which expect tabs and newlines to be the field and record separators.

The motivation for writing the tool was to work with CSV files (usually from Excel) that were hundreds of megabytes. These files came from outside my organization, and often from nontechnical people - so it would have been difficult to get them into a more convenient format. That's the killer feature of the CSV/TSV format: it's readable by the large number of nontechnical information workers, in almost every application they use. I can't think of a file format that is more widely recognized (even if it's not always consistently defined in practice).

dbro

13 anni fadiscuss

Thanks very much! You just made my day!

dbro

13 anni fadiscuss

I did, too. Different UI, though. See http://www.domainjig.com

dbro

14 anni fadiscuss

Nicely done. Here's a similar site I made previously: http://www.domainjig.com

Beyond the shameless self-promotion I found that getting people to use it would take a lot of work, with lots of alternatives out there that have dedicated teams promoting them. On the positive side, I learned a lot from it; but I never made any significant money from it. If you're comfortable sharing your next steps, I'd be interested to know what you're aiming for with this project.

Good luck!

dbro

14 anni fadiscuss

For those interested in giving this a try, here are a few additional suggestions from my recent experience.

There are less expensive alternatives to Linode. I switched to Loose Foot Computing (http://www.lfcvps.com) about a year ago and their service has been great. They often have promotional deals listed on lowendbox. See http://www.lowendbox.com/?s=loose+foot&searchsubmit=Find

tmux is amazing and takes care of window management. No need for dwm or xmonad anymore.

Lastpass integration with the browser works great with Chrome OS. This replaced keypassx for me.

USB tethering with my android phone "just works" to get on the internet.

Really happy so far. This is the right mix of easy, fast, cheap, and secure for me.

Submissions

Forget passwords

Google Maps API: October 1 clampdown coming, but pricing still unknown

CA high speed rail cartoon debate (humor)

comments