cut an array column and join an another column to a csv file
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?
text-processing csv
edited Aug 20 '18 at 22:51
Rui F Ribeiro
39.5k1479132
asked Oct 24 '16 at 3:04
joker21joker21
5816
add a comment |
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?
text-processing csv
edited Aug 20 '18 at 22:51
Rui F Ribeiro
39.5k1479132
asked Oct 24 '16 at 3:04
joker21joker21
5816
So how is that question a duplicate and this question is not?
– Bananguin
Oct 24 '16 at 13:08
add a comment |
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?
text-processing csv
edited Aug 20 '18 at 22:51
Rui F Ribeiro
39.5k1479132
asked Oct 24 '16 at 3:04
joker21joker21
5816
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?
text-processing csv
text-processing csv
edited Aug 20 '18 at 22:51
Rui F Ribeiro
39.5k1479132
asked Oct 24 '16 at 3:04
joker21joker21
5816
edited Aug 20 '18 at 22:51
Rui F Ribeiro
39.5k1479132
asked Oct 24 '16 at 3:04
joker21joker21
5816
edited Aug 20 '18 at 22:51
Rui F Ribeiro
39.5k1479132
edited Aug 20 '18 at 22:51
Rui F Ribeiro
39.5k1479132
edited Aug 20 '18 at 22:51
Rui F Ribeiro
39.5k1479132
39.5k1479132
asked Oct 24 '16 at 3:04
joker21joker21
5816
asked Oct 24 '16 at 3:04
joker21joker21
5816
asked Oct 24 '16 at 3:04
joker21joker21
5816
5816
So how is that question a duplicate and this question is not?
– Bananguin
Oct 24 '16 at 13:08
add a comment |
So how is that question a duplicate and this question is not?
– Bananguin
Oct 24 '16 at 13:08
So how is that question a duplicate and this question is not?
– Bananguin
Oct 24 '16 at 13:08
So how is that question a duplicate and this question is not?
– Bananguin
Oct 24 '16 at 13:08
add a comment |
3 Answers
3
active
oldest
votes
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
answered Oct 24 '16 at 3:43
steeldriversteeldriver
35.1k35185
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 evenpaste -d, input.csv column.csv | sed 's/,"{[^}]*}"//'
– Sundeep
Oct 24 '16 at 5:58
add a comment |
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)
answered Oct 24 '16 at 12:58
Iñaki MurilloIñaki Murillo
585312
add a comment |
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
answered Jan 6 at 18:06
aborrusoaborruso
1769
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "106"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f318446%2fcut-an-array-column-and-join-an-another-column-to-a-csv-file%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Oct 24 '16 at 3:43
steeldriversteeldriver
35.1k35185
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 evenpaste -d, input.csv column.csv | sed 's/,"{[^}]*}"//'
– Sundeep
Oct 24 '16 at 5:58
add a comment |
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
answered Oct 24 '16 at 3:43
steeldriversteeldriver
35.1k35185
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 evenpaste -d, input.csv column.csv | sed 's/,"{[^}]*}"//'
– Sundeep
Oct 24 '16 at 5:58
add a comment |
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
answered Oct 24 '16 at 3:43
steeldriversteeldriver
35.1k35185
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
answered Oct 24 '16 at 3:43
steeldriversteeldriver
35.1k35185
answered Oct 24 '16 at 3:43
steeldriversteeldriver
35.1k35185
answered Oct 24 '16 at 3:43
steeldriversteeldriver
35.1k35185
answered Oct 24 '16 at 3:43
steeldriversteeldriver
35.1k35185
35.1k35185
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 evenpaste -d, input.csv column.csv | sed 's/,"{[^}]*}"//'
– Sundeep
Oct 24 '16 at 5:58
add a comment |
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 evenpaste -d, input.csv column.csv | sed 's/,"{[^}]*}"//'
– Sundeep
Oct 24 '16 at 5:58
Is it possible to do with 'awk'?
– joker21
Oct 24 '16 at 5:33
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
@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
add a comment |
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)
answered Oct 24 '16 at 12:58
Iñaki MurilloIñaki Murillo
585312
add a comment |
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)
answered Oct 24 '16 at 12:58
Iñaki MurilloIñaki Murillo
585312
add a comment |
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)
answered Oct 24 '16 at 12:58
Iñaki MurilloIñaki Murillo
585312
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)
answered Oct 24 '16 at 12:58
Iñaki MurilloIñaki Murillo
585312
answered Oct 24 '16 at 12:58
Iñaki MurilloIñaki Murillo
585312
answered Oct 24 '16 at 12:58
Iñaki MurilloIñaki Murillo
585312
answered Oct 24 '16 at 12:58
Iñaki MurilloIñaki Murillo
585312
585312
add a comment |
add a comment |
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
answered Jan 6 at 18:06
aborrusoaborruso
1769
add a comment |
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
answered Jan 6 at 18:06
aborrusoaborruso
1769
add a comment |
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
answered Jan 6 at 18:06
aborrusoaborruso
1769
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
answered Jan 6 at 18:06
aborrusoaborruso
1769
edited Jan 6 at 20:57
answered Jan 6 at 18:06
aborrusoaborruso
1769
answered Jan 6 at 18:06
aborrusoaborruso
1769
answered Jan 6 at 18:06
aborrusoaborruso
1769
1769
add a comment |
add a comment |
Thanks for contributing an answer to Unix & Linux Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f318446%2fcut-an-array-column-and-join-an-another-column-to-a-csv-file%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
So how is that question a duplicate and this question is not?
– Bananguin
Oct 24 '16 at 13:08