Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've had a very colorful morning learning the innerparts of Linux's sort command, and have come across yet another issue that I can't seem to find an answer for in the documentation.

I'm currently using -t, to indicate that my fields are split by the comma character, but I'm finding that in some of my files, the comma is used (between double-quotes) within values:

Jonathan Sampson,,foo@bar.com,0987654321
"Foobar CEO,","CEO,",ceo@foobar.com,,

How can I use a comma to terminate my fields, but ignore the occurences of it within values? Is this fairly simple, or do I need to re-export all of my data using a more-foreign field-terminator? (Unfortunately, I do not have any control over declaring a different terminator with this particular project).

share|improve this question

I think sort is completely simple-minded about field delimiters. A good candidate might be a tab.

share|improve this answer
    
Unfortunately I don't have the ability to control the field-terminator. Perhaps I need to replace all commas appearing between double-quotes via a regular expression (would need to ask a new question), and then do my sort. – Sampson May 18 '10 at 15:54
    
@Jonathan: I would recommend using a Perl module ( Text::CSV , Text::CSV_XS ) or Python lib ( csv ) that understands CSV files and can convert to tab-delimited rather than try to create a regex. There are too many gotchas. – Dennis Williamson May 18 '10 at 16:33
    
I'm opened to just about any suggestion at this point. Please note that I'm a juvenile linux user, but very comfortable with regex syntax. I'd appreciate any solid direction you can offer. – Sampson May 18 '10 at 16:35
    
@Jonathan: sed might do the job, but it would take some complex gyrations if it's possible at all, but it can't do lookarounds. I don't do Perl but there's probably "an app for that" somewhere among the shifted numeral keys on your keyboard. Do you do Perl? Python? Take a look on SO for questions about converting or reading CSV files. A quickie hack might be to just import into Excel and export back out to tab-delimited (I'm pretty sure it can do that). – Dennis Williamson May 18 '10 at 16:47

CSV format allows for field to contain commas, provided that the field is quoted:

field1, "field2,with,commas"  ,  field3  ,  "field4,foo"

We have a mixture of quoted and unquoted fields here, which cannot parsed directly by any value of FS (that I know of, at least). However, we can still get the fields using match() in a loop (and cheating a bit):

$0=$0",";                                  # yes, cheating

while($0) {

  match($0,/[^,]*,| *"[^"]*" *,/);            

  sf=f=substr($0,RSTART,RLENGTH);          # save what matched in sf

  gsub(/^ *"?|"? *,$/,"",f);               # remove extra stuff

  print "Field " ++c " is " f;

  sub(sf,"");                              # "consume" what matched

}

As the complexity of the format increases (for example when escaped quotes are allowed in fields), awk solutions become more fragile. Although I should not say this here, for anything more complex than the last example, I suggest using other tools (eg, Perl just to name one). Btw, it looks like there is an awk CSV parsing library here: http://lorance.freeshell.org/csv/ (I have not tried it).

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .