5

I want to extract data between " " from a data file having delimiter as comma.

Sample input file:

,7/30/2019,7/31/2019,Wed,8/1/2019,FH/FN 30yr & 20yr TBA & Spec ,"10,000",8/13/2019,

Expected o/p:

,7/30/2019,7/31/2019,Wed,8/1/2019,FH/FN 30yr & 20yr TBA & Spec ,"10000",8/13/2019,
9
  • (1) Will there only be one pair of double quotes in one line? (2) Can there be higher numbers with multiple comma like 1,000,000? (3) How far did your own attempts lead?
    – Philippos
    Aug 9, 2019 at 13:14
  • (1) Will there only be one pair of double quotes in one line? No , there can be many, but i am okay to replace the comma in between " " to a blank (2) Can there be higher numbers with multiple comma like 1,000,000? Yes. (3) How far did your own attempts lead? $ cat asdf ,7/30/2019,7/31/2019,Wed,8/1/2019,FH/FN 30yr & 20yr TBA & Spec ,"10,000",8/13/2019, $ sed '/\"/,/\"/s/,//' asdf 7/30/2019,7/31/2019,Wed,8/1/2019,FH/FN 30yr & 20yr TBA & Spec ,"10,000",8/13/2019, $
    – Ramkumar
    Aug 9, 2019 at 13:15
  • Do you need to replace all numbers with a "," in them?
    – Ned64
    Aug 9, 2019 at 13:19
  • I need to replace them with blank. "10,000" to "10000"
    – Ramkumar
    Aug 9, 2019 at 13:20
  • Is there a limit on the numbers (e.g. can 12,000,000,000,000 occur?), how many "," max?
    – Ned64
    Aug 9, 2019 at 13:23

5 Answers 5

5

Assuming this is properly formatted CSV (the example data looks ok in this respect), we can use csvformat from csvkit to temporarily change the field delimiters to some other character not otherwise present in the data, such as @, delete all commas, and change the field delimiter back to the default again:

$ csvformat -D '@' file.csv | tr -d , | csvformat -d '@'
,7/30/2019,7/31/2019,Wed,8/1/2019,FH/FN 30yr & 20yr TBA & Spec ,10000,8/13/2019,

The output does not have quotes around the field that we modified, but that's because it no longer needs it.

Obviously, "deleting all commas" may delete commas that we don't actually want to delete, so we can be a bit more selective and only delete the commas in the 7th field:

$ csvformat -D '@' file.csv | awk -F '@' 'BEGIN { OFS=FS } { gsub(",", "", $7); print }' | csvformat -d '@'
,7/30/2019,7/31/2019,Wed,8/1/2019,FH/FN 30yr & 20yr TBA & Spec ,10000,8/13/2019,
3

Another awk solution:

awk -F\" '{
    OFS="\"";
    for ( i = 1; i <= NF; i++ ) {
        if ( i % 2 == 0 ) {
            gsub(/,/, "", $i)
        }
    }
}1' input.csv

This will use the double quote as a field separator and loop through all fields. If the field number is an even number (which is not fool-proof, but given your example it should mean that the field exists between quotes) it will remove any commas from that field. The 1 will cause awk to print everything (with the changes made) using the double quote as the output field separator.

In use:

$ cat input.csv
,7/30/2019,7/31/2019,Wed,8/1/2019,FH/FN 30yr & 20yr TBA & Spec ,"10,000",8/13/2019,
,7/30/2019,7/31/2019,"100",FH/FN 30yr & 20yr TBA & Spec ,"10,000,000",8/13/2019,
,7/30/2019,7/31/2019,"Jack, Mary, and Jane",8/1/2019,"123,456,789,012,345,678","10,000",8/13/2019,
$ awk -F\" '{
>     OFS="\"";
>     for ( i = 1; i <= NF; i++ ) {
>         if ( i % 2 == 0 ) {
>             gsub(/,/, "", $i)
>         }
>     }
> }1' input.csv
,7/30/2019,7/31/2019,Wed,8/1/2019,FH/FN 30yr & 20yr TBA & Spec ,"10000",8/13/2019,
,7/30/2019,7/31/2019,"100",FH/FN 30yr & 20yr TBA & Spec ,"10000000",8/13/2019,
,7/30/2019,7/31/2019,"Jack Mary and Jane",8/1/2019,"123456789012345678","10000",8/13/2019,

NOTE: This will remove the commas in fields that are not numbers. In order to read this file correctly as a csv you will need to do that. If for some reason you want to retain those commas you can use the below solution.


awk -F\" '{
    OFS="\"";
    for ( i = 1; i <= NF; i++ ) {
        if ( i % 2 == 0 && $i ~ /[0-9]/ ) {
            gsub(/,/, "", $i)
        }
    }
}1' input.csv
10
  • This will not work correctly if there are non-numbers in quotation marks. These are not to be changed. Anyway, why write the same thing again I already answered?
    – Ned64
    Aug 9, 2019 at 13:59
  • I thought, the more precise, the better.
    – Ned64
    Aug 9, 2019 at 14:00
  • 1
    @Ned64: I don't disagree, but this site encourages multiple answers to a single problem.
    – jesse_b
    Aug 9, 2019 at 14:01
  • Upon thinking about it further, it seems like OP is trying to make this a readable csv file, so having commas within any field would be undesirable, whether that field is a number or not. It seems the correct thing to do is to remove the commas between Jack, Mary, and Jane otherwise that will be split into 3 separate fields when read as a CSV.
    – jesse_b
    Aug 9, 2019 at 14:28
  • No, if the commas are between quotes "" they will be ignored as field delimiters. That's likely the reason why the quotes are there in the first place, even though they are unusual around a number.
    – Ned64
    Aug 9, 2019 at 14:31
3

Try for example awk:

cat oldfile | awk '{ print gensub ("(,\"[0-9]+),([0-9][0-9][0-9]),?([0-9][0-9][0-9])?,?([0-9][0-9][0-9]),?","\\1\\2\\3\\4","g");}' > newfile

This works for large numbers, too.

Explanation:

awk is a programmable filter. The command given here in the commandline (between the outer single quotes "'") will be executed for every line of input from your file.

The awk program looks like this (different formatting):

{
    print gensub ("(,\"[0-9]+),([0-9][0-9][0-9]),?([0-9][0-9][0-9])?,?([0-9][0-9][0-9]),?",
                  "\\1\\2\\3\\4",
                  "g");
}

The awk-builtin command gensub replaces things given in the first argument, with the replacement given in the second. If the third argument is a string starting with "g" or "G" it will replace all occurrences (tries until no more are found).

What is replaced? The first argument is a regular expression (q.v.) in double quotes, here are the parts: ,\ then afterwards [0-9]+ which means a digit 0-9 repeated one or more times (postfix operator +) then , which is just a character, then [0-9][0-9][0-9] and a comma , followed by a question mark ? (you know what the first part means now but the postfix ? is new - the comma digits can be omitted). Then more digit groups and commas which may be omitted - this is for larger numbers.

In this explanation I have left out the parentheses ( and ) so far! These mark those things that are matched by the expression but remembered. In the second argument to gensub we reference the first \1 through fourth \4 things that were matched (the numbers) and print them out again here.

8
  • Thanks this worked. But i am not able to understand how it is working. Could you please explain?
    – Ramkumar
    Aug 9, 2019 at 13:42
  • OK, but only if you mark then answer as working :-)
    – Ned64
    Aug 9, 2019 at 13:43
  • :-) :-) I did.. Of course i will do. :-)
    – Ramkumar
    Aug 9, 2019 at 13:46
  • @Ramkumar Explanation OK?
    – Ned64
    Aug 9, 2019 at 14:00
  • @Jesse_b Thanks. That's why I asked about the maximum number in the beginning.
    – Ned64
    Aug 9, 2019 at 14:33
0

Try this:

sed 's/\(\".*\),\(.*\"\)/\1\2/' file
5
  • This will only replace one comma and will make a mess when there are more double quotes on that line.
    – Philippos
    Aug 9, 2019 at 13:33
  • @Philippos Agreed with the comma, I'll keep working and edit if necessary, although OP data does not show any other double quotes. Aug 9, 2019 at 13:37
  • @Philippos If it works for the data the OP has it could be OK? I tried a more specific answer, let's see what Ramkumar thinks.
    – Ned64
    Aug 9, 2019 at 13:38
  • As Philippos said if i have another data after "10,000", it failed. But thanks for responding. I am taking Ned64 syntax. $ cat asdf ,7/30/2019,7/31/2019,"Wed",8/1/2019,"FH/FN 30yr & 20yr TBA & Spec" ,"10,000",8/13/2019,"ram", $ sed 's/(\".*),(.*\")/\1\2/' asdf ,7/30/2019,7/31/2019,"Wed",8/1/2019,"FH/FN 30yr & 20yr TBA & Spec" ,"10,000",8/13/2019"ram",
    – Ramkumar
    Aug 9, 2019 at 13:45
  • You need to replace the .* by [^"]* to avoid those problems and add a loop to cover higher numbers; see my answer for that. I don't like using loops, but at least they are less complicated as the awk loops.
    – Philippos
    Aug 12, 2019 at 8:03
0

Your own attempt sed '/\"/,/\"/s/,//' fails because the address range you give only filters for a range of lines, not a range inside a line.

This type of task is nasty in standard sed. If it's just about one comma, then sed -E 's/("[0-9]*),([0-9]*")/\1 \2/ would do the trick, but for multiple comma you'd have to loop, giving ugly results like

sed -Ee :loop -e 's/("[0-9 ]*),([^"]*")/\1 \2/;tloop'

The ("[0-9]*) matches the opening double quote followed by any number of digits and will be referred to as \1 in the replacement, the ([^"]*") matches anything after the comma until the closing ", so \1 \2 is the same, but with the first comma replaced.

Now the t command branches to the loop mark if a replacement was made. This gets repeated until there is no comma left to be replaced.

This even works for cases with more than one number with as many commas as you like: ,7/30/2019,"99,999,999,999,999",0,1 ,"10,000","foo, bar" will get transformed to ,7/30/2019,"99 999 999 999 999" 0 1 "10 000" "foo, bar"

You must log in to answer this question.

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