Need to format a CSV

Question

I have a CSV file with the following values sample shown below

"Basic","""21,21""","[""21"",""21""]","","","","",""

I need to remove the extra double quotes on certain columns, e.g. 2 and 3

Expected output is below

"Basic","21,21","[21,21]","","","","",""

How can I achieve this using awk, sed or any other Linux tool?

some more exampled of the files are mentioned below, the values in that column are always in [] with the quotes within the [] that have to be removed.

"Basic","""40""","[""40""]","""13F""","[""13F""]","","" 
"Basic","""0""","[""0""]","","","""MCOMB""","[""MCOMB""]"

Answer 1

Use a csv parser that understands CSV with quoting and commas embedded in quoted fields and more, which can be a bit more complicated than simple comma-delimited fields.

miller is a good command-line tool for this, as is csvkit.

Or use a csv parsing library for a language like perl or python - e.g. Text::CSV for perl or csv for python.

If you're on Linux, all of these are probably available as packages for whatever distribution you're using.

Answer 2

I come with a sed solution

sed -e 's/,"""/,"/g' -e 's/""",/",/g' -e 's/\([^,]\)""/\1/g' -e 's/""\([^,]\)/\1/' 

which gives

"Basic","40","[40]","13F","[13F]","",""
"Basic","0","[0]","","","MCOMB","[MCOMB]"
"Basic","21,21","[21,21]","","","","",""

sed command are pretty straightforward

• 's/,"""/,"/g' replace ,""" by ," for all occurences g
• 's/\([^,]\)""/\1/g' find any not comma char [^,] and two " , remember char \( \) and replace by char remembered \1

note that a trailling space in end of line will delete last ""

as @cas pointed out, it would be better in long run to use a csv tool.

Answer 3

I'm assuming that you want to remove all double quotes that are part of the data, i.e., not the double quotes that are part of the CSV format and necessary for quoting embedded quotes, commas, and newlines.

Using csvformat from csvkit, and tr, to remove the internal quoting on each field:

$ cat file
"Basic","""40""","[""40""]","""13F""","[""13F""]","",""
"Basic","""0""","[""0""]","","","""MCOMB""","[""MCOMB""]"
"Basic","""21,21""","[""21"",""21""]","","","","",""

$ csvformat -Q "'" file | tr -d '"' | csvformat -q "'"
Basic,40,[40],13F,[13F],,
Basic,0,[0],,,MCOMB,[MCOMB]
Basic,"21,21","[21,21]",,,,,

The pipeline above first changes the quoting character used in the CSV file from double quotes to single quotes. The tr command deletes all remaining double quotes (part of the data). The final csvformat command converts the data back to using double quotes for quoting.

If you need every field quoted, even the empty ones, add -U 1 to csvformat's second invocation. The csvkit utilities, by default, only output quotes for the fields that need it.

Answer 4

👋

You need to look into a CSV-aware tool. As you've already seen, you can write some very complicated awk and sed and still get it wrong. @cas mentioned miller and csvkit, I haven't used those so I cannot say if they can do what you need.

I do use GoCSV which is a very simple tool with a number of commands that can help you achieve your goal.

Just taking that one line by itself, the first thing GoCSV needs is a header, so I'll feed that line into its cap command to create a dummy header with a default name of 'Col':

echo '"Basic","""21,21""","[""21"",""21""]","","","","",""' | \
gocsv cap --default-name Col

and I get:

Col 1,Col 2,Col 3,Col 4,Col 5,Col 6,Col 7,Col 8
Basic,"""21,21""","[""21"",""21""]",,,,,

('Col' was appended with unique index numbers for each column)

You can see the command stripped away the unnecessary empty quotes at the end. Now, on to the quoted quotes.

I'll take that output and pipe it into the replace command where I can specify a regex patten to find, ", and a replacement of empty string:

echo '"Basic","""21,21""","[""21"",""21""]","","","","",""' | \                                                                  
gocsv cap --default-name Col | \                                   
gocsv replace --regex '"' --repl ''

and I get:

Col 1,Col 2,Col 3,Col 4,Col 5,Col 6,Col 7,Col 8
Basic,"21,21","[21,21]",,,,,

If you need the header gone, pipe that into behead. Here's the complete pipeline:

echo '"Basic","""21,21""","[""21"",""21""]","","","","",""' | \
gocsv cap --default-name Col | \
gocsv replace --regex '"' --repl '' | \
gocsv behead

Basic,"21,21","[21,21]",,,,,