2

I have a CSV file with some fields quoted, but the entries within quotes are separated by double or single spaces. I need these to be replaced with commas.

Example line:

This is okay,"ABC DEF GHI",123,"This is not okay",remove,spaces,within,quotes

And how it should look:

This is okay,"ABC,DEF,GHI",123,"This,is,not,okay",remove,spaces,within,quotes

  • Purely to satisfy my curiosity, we do a lot of csv stuff, never seen this type of problem, exactly why is this needed? are you going to split the comma separated quoted entries into some further thing? This is just curiosity, not a criticism of the question, I'd personally just like to know what scenario you have that makes this required, maybe to see if there isn't a better way to handle it in general. – Lizardx Aug 1 '16 at 21:22
  • I'm given a HTML output from a program ChimeraScan and have to convert that to CSV so that we can use it in R. – TomNash Aug 1 '16 at 21:28
  • are the " enclosed commas , going to create further csv columns in the same row? or are they going to create sub rows, in a different dataset? it appears you won't know in some cells how many data points there are, so it has to be dynamic, which is challenging. I assume the , in any escaped item will then be used to a different data row, but one related to the primary row, like a db foreign key sort of. – Lizardx Aug 1 '16 at 21:33
2
perl -pe 's/".*?"/do{$a = $&; $a =~ s: +:,:g; $a}/ge;'

Essentially, this is just a global regex replacement s/regex/replacement/g. The regex is ".*?", it matches every substring that starts with " and ends with the next following ". The tricky parts are:

  • The replacement is not a string, but an expression that is evaluated. (That's the meaning of the e modifier after g.)
  • The expression that is evaluated is again a global regex replacement s:regex:replacement:g that replaces any non-empty sequence of spaces by a comma. (We cannot use the same delimiter as in the outer replacement, so we use : instead of /.)
  • In order to execute the inner regex replacement, we have to assign the matched substring of the outer replacement $& to some other variable $a, then perform the inner replacement on $a, and finally print $a.

With a sufficiently recent perl version, the assignment to an auxiliary variable can be avoided. Using the r modifier, the inner replacement can be performed directly on a copy of the matched substring $& (thanks to Stéphane Chazelas):

perl -pe 's/".*?"/$&=~s: +:,:gr/ge;'
  • Nice and well explained ++ – sjsam Aug 2 '16 at 15:20
  • Can be shortened (with recent perl) to: perl -pe 's/".*?"/$&=~s: +:,:gr/ge' (the r flag being the recent addition) – Stéphane Chazelas Aug 2 '16 at 15:22
  • @StéphaneChazelas Thanks, that simplifies these constructions quite a lot. – Uwe Aug 2 '16 at 15:27
1

Consider this brute-force awk solution:

awk -F, -v OFS=, '
  {
    for(i=1;i<=NF;i++)
        if ($i ~ /^".*"$/)
                gsub(" +", ",", $i)
    print $0
  }'

It tells awk to split the records on commas -- noting that this will break if any of your fields contains a comma! -- and using OFS to tell the print statement to recombine the fields with commas. The for loop goes through each field of the line, and if the field starts ^ with a double-quote, has any characters .* and ends $ with a double-quote, then globally replace in that $i field any number of spaces with commas. After looping over the fields, print the whole record ($0).

  • clean and easy, upvoted even though as you realized, it will fail on comma , in "...". To me the real answer in a sense is to avoid the hacks and create the csv with all items "..." quoted, so you can treat them consistently and safely, but it's not a given that the person has control over the output format of the csv, which is why I didn't suggest it. I was waiting to see if anyone could fully solve this riddle however. I wonder what the real use for this is, it's clearly some type of workaround for problematic csv generation, would be interesting to see why this is required. – Lizardx Aug 1 '16 at 21:17
  • @Lizardx, hit the nail on the head with "workaround for problematic csv generation." I've got a HTML table created from a program which I've got to convert to CSV and then further correct the formatting. – TomNash Aug 1 '16 at 21:24
  • Lol, I have to do that too for a bunch of our data sources, I thought this felt familiar, parsing html tables to csv format in shell. There may be another solution, if you can post a few rows of the real table html, anonymized, there may be another way around it. We deal with some clinically insane table structures for data sources, but I've never found a table that couldn't be parsed, well, given enough rowspans within a primary row, there's a limit, but if it's a clean single row, it can be parsed in my experience. You may be able to use pre awk parsing to prep the formatting. – Lizardx Aug 1 '16 at 21:28
  • I've gotten the name of the program now, I'm going to look into modifying the HTML template file to remove the line breaks and use commas there instead. – TomNash Aug 1 '16 at 21:39
  • that's probably the best solution, this could be done in awk/gawk if it's a line break separating the results in a cell, but it would be really hard if it has to support , as a possible value in the line. Make sure that those cells don't contain commas though, otherwise it will fail. Usually I use unique separators, like ^, to avoid clashes with things that can actually be contained in the data like a comma, that's far safer and more reliable I find. Then whatever is using the csv splits the secondary stuff using that ^ separator, not a comma. – Lizardx Aug 1 '16 at 21:42
1

With GNU awk:

gawk -v RS=\" '
  NR % 2 == 0{gsub(/ +/, ",")}
  {ORS = RT; print}'

That is, the record separator is made the " character and whitespace are only replaced on even records.

RT is the GNU-specific part.

Same with GNU sed:

tr '\n"' '"\n' | sed -E '2~2s/ +/,/g' | tr '"\n' '\n"'

More portably:

tr '\n"' '"\n' | sed 'n;s/  */,/g' | tr '"\n' '\n"'

would work with some other seds though you could have issues if the last character of the input is not a ".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.