3

I've asked almost the same question already, but this time, I want to retrieve the X latest elements of a row of a CSV file. For example, with an input file as this one:

1;foo;bar;baz;x;y;z
2;foo;bar;baz;x;y;z
3;foo;bar;baz;x;y;z

What would be the command (eventually using cut) to get the last 2 columns, so I get:

y;z
y;z
y;z

In fact, my real target is to retrieve the first 3 and the last 2 fields of each row, so I get:

1;foo;bar;y;z
2;foo;bar;y;z
3;foo;bar;y;z

Unfortunately, I cannot use a command like cut -d \; -f 1-3,10-11 (if there are 11 elements in the row), because the CSV file does not respect the real CSV format. Indeed, some fields in the middle of the rows are encrypted, and their encrypted value may sometimes contains a ; characters (and of course, they are not wrapped inside "). In others words, my lines may look like that:

1;foo;bar;#@$"é&^l#;baz;x;y;z
2;foo;bar;#¤=é;)o'#;baz;x;y;z
3;foo;bar;#]]'~é{{#;baz;x;y;z

and as you can see, on the second line, there is an additional ; character, so I can't use here a command like cut -d \; -f 1-3,7-8, because if will return that, which is wrong:

1;foo;bar;y;z
2;foo;bar;x;y  (-> Wrong here, there is a shift)
3;foo;bar;y;z

So how can I use cut to solve my problem?

Thanks

ps: I am specially in love with the cut command, so if you have a command that does what I want but that is not cut, then it's fine too :)

Edit It seems important to note that the machine is quite old: uname -a give this message:

SunOS ###### 5.10 Generic_142900-05 sun4u sparc SUNW,Sun-Fire-V240

and some commands may not be present (like rev)

Community
  • 1
Romain Linsolas
  • 235
  • If your file has fixed width fields, you can use the -b option to select items via byte or -c for characters, rather than -f. – ernie Jan 07 '13 at 21:35

5 Answers5

5

Here is a multiple-command solution for retrieving the first 3 and the last 2 fields using only cut, rev (for reversing) and shell-builtins:

while read line
do
    first=$(echo -n "$line" | cut -d ";" -f -3)
    second=$(echo -n "$line" | rev | cut -d ";" -f -2 | rev)
    echo "$first;$second"
done < my_file

These statements can also be put into a single line, of course.

EDIT:

I gathered some one-line alternatives to rev (which omit printing the final '\n'):

python: python -c "import sys; sys.stdout.write(raw_input()[::-1])

perl: perl -ne 'chomp;print scalar reverse;'

and there are a lot more possibilities for reversing a string. Maybe some of those work on your system.

scai
  • 10,793
  • Argh. Your solution seems interesting, but unfortunately, the command rev is not installed on my server (SunOS 5.10) – Romain Linsolas Jan 07 '13 at 14:59
  • @romaintaz Too bad, my rev comes from the util-linux package which contains various essential commands for Linux. I wonder whether SunOS has a similar command. – scai Jan 07 '13 at 15:01
  • 1
    perl (5.8.4!) is present on my Solaris 10 and (even that) can do the field-split and select all by itself in one go: perl -lane '@F=split(";"); print join ";",@F[0..2,-2,-1]' <in >out – dave_thompson_085 Mar 20 '24 at 05:13
  • @dave_thompson_085 and here's the Raku (a.k.a. Perl6) version, although you'd have to build it from source for SunOS/Solaris: raku -ne 'my @F = .split(";"); put join ";", @F[0..2,*-2,*-1];' – jubilatious1 Mar 25 '24 at 23:57
5

On your version of SunOS nawk(or for that matter awk) should be able to do the trick

 nawk -F';' 'BEGIN{OFS=";"}{print($1,$2,$3,$(NF-1),$(NF))}' file.txt
iruvar
  • 16,725
2
% cat a
1;foo;bar;#@$"é&^l#;baz;x;y;z
2;foo;bar;#¤=é;)o'#;baz;x;y;z
3;foo;bar;#]]'~é{{#;baz;x;y;z
% sed -r 's,^(([^;]+;){3}).*;([^;]+;[^;]+)$,\1\3,' < a
1;foo;bar;y;z
2;foo;bar;y;z
3;foo;bar;y;z
0xFF
  • 211
0

Ok, I came across this question for the same reason and found an alternative with cut as requested. Remember that reverse function is really useful to avoid going in the middle part.

Here is my solution:

$ cat /tmp/fixup.csv
1;foo;bar;#@$"é&^l#;baz;x;y;z
2;foo;bar;#¤=é;)o'#;baz;x;y;z
3;foo;bar;#]]'~é{{#;baz;x;y;z
$ cat /tmp/fixup.csv |cut -d ';' -f-3 > begin.txt
$ cat /tmp/fixup/file.txt |rev |cut -d ';' -f-2|rev > end.txt
$ paste -d";" begin.txt end.txt > result.csv
$ cat result.csv
1;foo;bar;y;z
2;foo;bar;y;z
3;foo;bar;y;z
$
Martin P.
  • 101
  • 1
0

Although personally I'd go with the awk or perl methods, this can also be done, without lots of extra processes, with builtins in the bash 3.2 on (my now-archived) Solaris 10:

# unless in a one-off script, save IFS first and restore afterwards
# most simply just put this in parens so it runs in a subshell
IFS=';'; while read -ra a;do N=${#a[*]}; 
  set -- "${a[0]}" "${a[1]}" "${a[2]}" "${a[N-2]}" "${a[N-1]}";
  printf %s\\n "${*}";done  <in >out
dave_thompson_085
  • 4,062