Shell Programming and Scripting

I have a small quandry. I had server reports that I pulled from a database that came out pipe "|" delimited. The developers have now changed the format to CSV. The issue is that some fields have quotes around the text and other fields are blank with strings of commas denoting each field. To further complicate things, quoted fields can have commas in them as well. I used to do a bunch of awk processing based on the pipe delimiter that is obviously now broken. Does anyone know of a way to use the CSV format so that it can differentiate between the quoted text a la Excel text to columns conversion?

Can you try this?

I remember pulling this from the internet a long time ago, though never used it.

Code:

awk -vq='"' '
func csv2del(n) {
  for(i=n; i<=c; i++)
    {if(i%2 == 1) gsub(/,/, OFS, a[i])
    else a[i] = (q a[i] q)
    out = (out) ? out a[i] : a[i]}
  return out}
{c=split($0, a, q); out=X;
  if(a[1]) $0=csv2del(1)
  else $0=csv2del(2)}1' OFS='|' file

that looks like it should do the trick for converting the data to pipe delimited. i can strip out the extra quotes after the fact. thanks a million, you just saved me a ton of work.