How to use Unix Shell to show only the first n columns and last n columns?
Clash Royale CLAN TAG#URR8PPP
up vote
3
down vote
favorite
I have many csv files. The original design was supposed to have five columns.
I just found out that the middle column of the csv file has a string with arbitrary number of commas in it and it is not quoted properly. This leads to rows with arbitrary number of columns.
How do I get just the first two and last two columns of these csv files?
Since the number of commas can change from row to row I need a way to specify first two and last two columns.
awk sed csv cut
asked Jan 23 at 18:56
PoorLifeChoicesMadeMeWhoIAm
184
add a comment |Â
up vote
3
down vote
favorite
I have many csv files. The original design was supposed to have five columns.
I just found out that the middle column of the csv file has a string with arbitrary number of commas in it and it is not quoted properly. This leads to rows with arbitrary number of columns.
How do I get just the first two and last two columns of these csv files?
Since the number of commas can change from row to row I need a way to specify first two and last two columns.
awk sed csv cut
asked Jan 23 at 18:56
PoorLifeChoicesMadeMeWhoIAm
184
4
Please tell me the middle column is enclosed in quote marks. If it is not, tell whoever created these files they did it wrong.
– Monty Harder
Jan 23 at 22:06
1
If the columns are properly quoted, then you can use a language (like perl or python or even awk) that has a CSV parsing library. See also stackoverflow.com/questions/4205431/…
– cas
Jan 24 at 2:50
Yeah it wasn't quoted. The worst part is that column was not needed at all.
– PoorLifeChoicesMadeMeWhoIAm
Jan 25 at 1:11
add a comment |Â
up vote
3
down vote
favorite
up vote
3
down vote
favorite
I have many csv files. The original design was supposed to have five columns.
I just found out that the middle column of the csv file has a string with arbitrary number of commas in it and it is not quoted properly. This leads to rows with arbitrary number of columns.
How do I get just the first two and last two columns of these csv files?
Since the number of commas can change from row to row I need a way to specify first two and last two columns.
awk sed csv cut
asked Jan 23 at 18:56
PoorLifeChoicesMadeMeWhoIAm
184
I have many csv files. The original design was supposed to have five columns.
I just found out that the middle column of the csv file has a string with arbitrary number of commas in it and it is not quoted properly. This leads to rows with arbitrary number of columns.
How do I get just the first two and last two columns of these csv files?
Since the number of commas can change from row to row I need a way to specify first two and last two columns.
awk sed csv cut
asked Jan 23 at 18:56
PoorLifeChoicesMadeMeWhoIAm
184
edited Jan 25 at 1:23
asked Jan 23 at 18:56
PoorLifeChoicesMadeMeWhoIAm
184
asked Jan 23 at 18:56
PoorLifeChoicesMadeMeWhoIAm
184
asked Jan 23 at 18:56
PoorLifeChoicesMadeMeWhoIAm
184
184
4
Please tell me the middle column is enclosed in quote marks. If it is not, tell whoever created these files they did it wrong.
– Monty Harder
Jan 23 at 22:06
1
If the columns are properly quoted, then you can use a language (like perl or python or even awk) that has a CSV parsing library. See also stackoverflow.com/questions/4205431/…
– cas
Jan 24 at 2:50
Yeah it wasn't quoted. The worst part is that column was not needed at all.
– PoorLifeChoicesMadeMeWhoIAm
Jan 25 at 1:11
add a comment |Â
4
Please tell me the middle column is enclosed in quote marks. If it is not, tell whoever created these files they did it wrong.
– Monty Harder
Jan 23 at 22:06
1
If the columns are properly quoted, then you can use a language (like perl or python or even awk) that has a CSV parsing library. See also stackoverflow.com/questions/4205431/…
– cas
Jan 24 at 2:50
Yeah it wasn't quoted. The worst part is that column was not needed at all.
– PoorLifeChoicesMadeMeWhoIAm
Jan 25 at 1:11
4
4
Please tell me the middle column is enclosed in quote marks. If it is not, tell whoever created these files they did it wrong.
– Monty Harder
Jan 23 at 22:06
Please tell me the middle column is enclosed in quote marks. If it is not, tell whoever created these files they did it wrong.
– Monty Harder
Jan 23 at 22:06
1
1
If the columns are properly quoted, then you can use a language (like perl or python or even awk) that has a CSV parsing library. See also stackoverflow.com/questions/4205431/…
– cas
Jan 24 at 2:50
If the columns are properly quoted, then you can use a language (like perl or python or even awk) that has a CSV parsing library. See also stackoverflow.com/questions/4205431/…
– cas
Jan 24 at 2:50
Yeah it wasn't quoted. The worst part is that column was not needed at all.
– PoorLifeChoicesMadeMeWhoIAm
Jan 25 at 1:11
Yeah it wasn't quoted. The worst part is that column was not needed at all.
– PoorLifeChoicesMadeMeWhoIAm
Jan 25 at 1:11
add a comment |Â
3 Answers
3
active
oldest
votes
up vote
13
down vote
accepted
awk -F, 'print $1, $2, $(NF-1), $NF' < input
More generally (per the Question's title), to print the first and last n columns of the input -- without checking to see whether that means printing some columns twice --
awk -v n=2 '
for(i=1; i <= n && i <= NF; i++)
printf "%s%s", $i, OFS
for(i=NF-n+1; i <= NF && i >= 1; i++)
printf "%s%s", $i, OFS
printf "%s", ORS
' < input
(using -F as needed for the delimiter)
answered Jan 23 at 19:07
Jeff Schaller
31.7k847107
you dont have to use a redirect
– Steven Penny
Jan 23 at 23:52
That’s correct. It’s a habit I’m trying to form after seeing advice in that direction from other U&L members here. It means that the shell reports errors/failures instead of the utility — more consistency. It also prevents the utility from running if the IO redirection fails.
– Jeff Schaller
Jan 24 at 0:23
add a comment |Â
up vote
1
down vote
perl:
echo a,b,X,X,X,X,c,d | perl -F, -slane 'print join ",", @F[0..$n-1, -$n..-1]' -- -n=2
a,b,c,d
answered Jan 25 at 19:06
glenn jackman
46.6k265103
add a comment |Â
up vote
1
down vote
You can use this sed too
sed -E 's/(([^,]*,)2).*((,[^,]*)2)/13/;s/,,/,/'
answered Jan 27 at 12:22
ctac_
1,016116
add a comment |Â
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
13
down vote
accepted
awk -F, 'print $1, $2, $(NF-1), $NF' < input
More generally (per the Question's title), to print the first and last n columns of the input -- without checking to see whether that means printing some columns twice --
awk -v n=2 '
for(i=1; i <= n && i <= NF; i++)
printf "%s%s", $i, OFS
for(i=NF-n+1; i <= NF && i >= 1; i++)
printf "%s%s", $i, OFS
printf "%s", ORS
' < input
(using -F as needed for the delimiter)
answered Jan 23 at 19:07
Jeff Schaller
31.7k847107
you dont have to use a redirect
– Steven Penny
Jan 23 at 23:52
That’s correct. It’s a habit I’m trying to form after seeing advice in that direction from other U&L members here. It means that the shell reports errors/failures instead of the utility — more consistency. It also prevents the utility from running if the IO redirection fails.
– Jeff Schaller
Jan 24 at 0:23
add a comment |Â
up vote
13
down vote
accepted
awk -F, 'print $1, $2, $(NF-1), $NF' < input
More generally (per the Question's title), to print the first and last n columns of the input -- without checking to see whether that means printing some columns twice --
awk -v n=2 '
for(i=1; i <= n && i <= NF; i++)
printf "%s%s", $i, OFS
for(i=NF-n+1; i <= NF && i >= 1; i++)
printf "%s%s", $i, OFS
printf "%s", ORS
' < input
(using -F as needed for the delimiter)
answered Jan 23 at 19:07
Jeff Schaller
31.7k847107
you dont have to use a redirect
– Steven Penny
Jan 23 at 23:52
That’s correct. It’s a habit I’m trying to form after seeing advice in that direction from other U&L members here. It means that the shell reports errors/failures instead of the utility — more consistency. It also prevents the utility from running if the IO redirection fails.
– Jeff Schaller
Jan 24 at 0:23
add a comment |Â
up vote
13
down vote
accepted
up vote
13
down vote
accepted
awk -F, 'print $1, $2, $(NF-1), $NF' < input
More generally (per the Question's title), to print the first and last n columns of the input -- without checking to see whether that means printing some columns twice --
awk -v n=2 '
for(i=1; i <= n && i <= NF; i++)
printf "%s%s", $i, OFS
for(i=NF-n+1; i <= NF && i >= 1; i++)
printf "%s%s", $i, OFS
printf "%s", ORS
' < input
(using -F as needed for the delimiter)
answered Jan 23 at 19:07
Jeff Schaller
31.7k847107
awk -F, 'print $1, $2, $(NF-1), $NF' < input
More generally (per the Question's title), to print the first and last n columns of the input -- without checking to see whether that means printing some columns twice --
awk -v n=2 '
for(i=1; i <= n && i <= NF; i++)
printf "%s%s", $i, OFS
for(i=NF-n+1; i <= NF && i >= 1; i++)
printf "%s%s", $i, OFS
printf "%s", ORS
' < input
(using -F as needed for the delimiter)
answered Jan 23 at 19:07
Jeff Schaller
31.7k847107
edited Jan 24 at 1:00
answered Jan 23 at 19:07
Jeff Schaller
31.7k847107
answered Jan 23 at 19:07
Jeff Schaller
31.7k847107
answered Jan 23 at 19:07
Jeff Schaller
31.7k847107
31.7k847107
you dont have to use a redirect
– Steven Penny
Jan 23 at 23:52
That’s correct. It’s a habit I’m trying to form after seeing advice in that direction from other U&L members here. It means that the shell reports errors/failures instead of the utility — more consistency. It also prevents the utility from running if the IO redirection fails.
– Jeff Schaller
Jan 24 at 0:23
add a comment |Â
you dont have to use a redirect
– Steven Penny
Jan 23 at 23:52
That’s correct. It’s a habit I’m trying to form after seeing advice in that direction from other U&L members here. It means that the shell reports errors/failures instead of the utility — more consistency. It also prevents the utility from running if the IO redirection fails.
– Jeff Schaller
Jan 24 at 0:23
you dont have to use a redirect
– Steven Penny
Jan 23 at 23:52
you dont have to use a redirect
– Steven Penny
Jan 23 at 23:52
That’s correct. It’s a habit I’m trying to form after seeing advice in that direction from other U&L members here. It means that the shell reports errors/failures instead of the utility — more consistency. It also prevents the utility from running if the IO redirection fails.
– Jeff Schaller
Jan 24 at 0:23
That’s correct. It’s a habit I’m trying to form after seeing advice in that direction from other U&L members here. It means that the shell reports errors/failures instead of the utility — more consistency. It also prevents the utility from running if the IO redirection fails.
– Jeff Schaller
Jan 24 at 0:23
add a comment |Â
up vote
1
down vote
perl:
echo a,b,X,X,X,X,c,d | perl -F, -slane 'print join ",", @F[0..$n-1, -$n..-1]' -- -n=2
a,b,c,d
answered Jan 25 at 19:06
glenn jackman
46.6k265103
add a comment |Â
up vote
1
down vote
perl:
echo a,b,X,X,X,X,c,d | perl -F, -slane 'print join ",", @F[0..$n-1, -$n..-1]' -- -n=2
a,b,c,d
answered Jan 25 at 19:06
glenn jackman
46.6k265103
add a comment |Â
up vote
1
down vote
up vote
1
down vote
perl:
echo a,b,X,X,X,X,c,d | perl -F, -slane 'print join ",", @F[0..$n-1, -$n..-1]' -- -n=2
a,b,c,d
answered Jan 25 at 19:06
glenn jackman
46.6k265103
perl:
echo a,b,X,X,X,X,c,d | perl -F, -slane 'print join ",", @F[0..$n-1, -$n..-1]' -- -n=2
a,b,c,d
answered Jan 25 at 19:06
glenn jackman
46.6k265103
answered Jan 25 at 19:06
glenn jackman
46.6k265103
answered Jan 25 at 19:06
glenn jackman
46.6k265103
answered Jan 25 at 19:06
glenn jackman
46.6k265103
46.6k265103
add a comment |Â
add a comment |Â
up vote
1
down vote
You can use this sed too
sed -E 's/(([^,]*,)2).*((,[^,]*)2)/13/;s/,,/,/'
answered Jan 27 at 12:22
ctac_
1,016116
add a comment |Â
up vote
1
down vote
You can use this sed too
sed -E 's/(([^,]*,)2).*((,[^,]*)2)/13/;s/,,/,/'
answered Jan 27 at 12:22
ctac_
1,016116
add a comment |Â
up vote
1
down vote
up vote
1
down vote
You can use this sed too
sed -E 's/(([^,]*,)2).*((,[^,]*)2)/13/;s/,,/,/'
answered Jan 27 at 12:22
ctac_
1,016116
You can use this sed too
sed -E 's/(([^,]*,)2).*((,[^,]*)2)/13/;s/,,/,/'
answered Jan 27 at 12:22
ctac_
1,016116
answered Jan 27 at 12:22
ctac_
1,016116
answered Jan 27 at 12:22
ctac_
1,016116
answered Jan 27 at 12:22
ctac_
1,016116
1,016116
add a comment |Â
add a comment |Â
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
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f419162%2fhow-to-use-unix-shell-to-show-only-the-first-n-columns-and-last-n-columns%23new-answer', 'question_page');
);
Post as a guest
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
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
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
4
Please tell me the middle column is enclosed in quote marks. If it is not, tell whoever created these files they did it wrong.
– Monty Harder
Jan 23 at 22:06
1
If the columns are properly quoted, then you can use a language (like perl or python or even awk) that has a CSV parsing library. See also stackoverflow.com/questions/4205431/…
– cas
Jan 24 at 2:50
Yeah it wasn't quoted. The worst part is that column was not needed at all.
– PoorLifeChoicesMadeMeWhoIAm
Jan 25 at 1:11