0

I have a CSV file as

input.csv

1,2,3,"{1,2,3}",9,12
1,2,3,"{1,2,3}",9,12
1,2,3,"{1,2,3}",9,12

I needed to remove the array column from the above CSV file and get the output as

output.csv

1,2,3,9,12
1,2,3,9,12
1,2,3,9,12

So I tried

cut -d , -f4 -- complement input.csv > output.csv

But the above command gave the error

The delimiter must be a single character

I also want to join the output.csv with another CSV file

column.csv
30
36
90

Then the result would look like

result.csv
1,2,3,9,12,30
1,2,3,9,12,36
1,2,3,9,12,90

Could anyone help me?

0

To properly handle quoted delimiters, you should consider using a purpose-designed CSV parser - for example, the one from the perl Text::CSV module. For example,

paste -d, input.csv column.csv | perl -MText::CSV -ne '
  BEGIN {$csv = Text::CSV->new()}
  if ($csv->parse($_)) {
    @a = $csv->fields();
    splice(@a, 3, 1);
    print join(",", @a) . "\n";
  }'
1,2,3,9,12,30
1,2,3,9,12,36
1,2,3,9,12,90
  • Is it possible to do with 'awk'? – joker21 Oct 24 '16 at 5:33
  • @joker21, yes possible but using a parser will be better and robust in the long run... paste -d, input.csv column.csv | awk -F',"{|}"' '{print $1 $3}' or even paste -d, input.csv column.csv | sed 's/,"{[^}]*}"//' – Sundeep Oct 24 '16 at 5:58
0

If you do not want to use purpose-designed CSV parser as sugested by @steeldrive, you can use awk:

awk '{split($0,a,"\""); print a[1] substr(a[3],2)}' input.csv > output.csv

Be aware that this will only work for the style provided in the example.

Explanation:

Split the text every "

{split($0,a,"\"")

Print and remove the first , of the second column

print a[1] substr(a[3],2)
0


with Miller (http://johnkerl.org/miller/doc) starting from

1,2,3,"{1,2,3}",9,12
1,2,3,"{1,2,3}",9,12
1,2,3,"{1,2,3}",9,12

is very simple and you can run

mlr --csv --implicit-csv-header --headerless-csv-output cut -x -f 4 input.csv | paste -d , - column.csv

to obtain

1,2,3,9,12,30
1,2,3,9,12,36
1,2,3,9,12,90

With cut -x -f 4 I remove the fourth column and with paste I add column.csv column

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.