2

I have a large .csv file where I need to split a specific column by string length. I'm trying to take the last 6 characters of column 2 and move them into a new column.

Current:

3102017,90131112,0,740
3022017,8903944,90,0
3092017,127037191,475,0

Desired:

3102017,90,131112,0,740
3022017,8,903944,90,0
3092017,127,037191,475,0
  • What to do to rows with six or less digits in the second column? – Philippos May 3 '17 at 14:40
  • Your title says "use awk" but you also tagged perl; please be clear as to whether you need a tool-specific solution or are open to any (or some) – Jeff Schaller May 6 '17 at 1:40
5

With a POSIX-compliant awk:

awk -F, -v OFS=, '{sub(/.{6}$/, OFS "&", $2); print}'

With a POSIX-compliant sed:

sed 's/^\([^,]*,[^,]*\)\([^,]\{6\}\)/\1,\2/'

Those modify the lines only if the second field is at least 6 characters long (note that it will happily change 111,123456,333 to 111,,123456,333 leaving the second field empty).

  • 2
    Much easier: sed 's/.\{6\},/,&/2' – Philippos May 3 '17 at 14:38
  • 2
    @Philippos However, your solution breaks if the first field is shorter than 6 characters. Which is a shame, really, :) – Satō Katsura May 3 '17 at 14:42
  • Nitpicking duel? (-; The behaviour in this case is undefined. Solution above adds an empty second field for six digits, but not for five. Maybe better sed 's/[^,]\{,6\},/,&/2', which always adds a second field and is still shorter. (-: – Philippos May 3 '17 at 14:49
  • 1
    maybe '{sub(/.{6}$/, OFS"&", $2); print}' instead of '{gsub(/.{6}$/, ",&", $2); print}'? – iruvar May 3 '17 at 15:59
  • @iruvar, good points. Changed. – Stéphane Chazelas May 3 '17 at 16:13
3

KISS approach, with awk:

$ awk -F, '{l=length($2); if (l>6) {$2=substr($2,1,l-6) OFS substr($2,l-5)}} 1' OFS=, file
3102017,90,131112,0,740
3022017,8,903944,90,0
3092017,127,037191,475,0
  • I like this answer as it will always give an extra column, even if the string in column 2 is too small. – Robert Benson May 3 '17 at 18:26
2

With a sed implementation with support for extended regular expressions, assumes at least 6 characters in 2nd column for all lines

$ sed -E 's/,([^,]*)([^,]{6}),/,\1,\2,/' ip.csv 
3102017,90,131112,0,740
3022017,8,903944,90,0
3092017,127,037191,475,0
  • -E for extended regular expressions, some implementations need -r instead
  • ,([^,]*)([^,]{6}), will match , 2nd column and ,
    • 2nd column is captured in two groups with last 6 characters in 2nd group
  • ,\1,\2, replace as desired
2

With perl, since sed and awk are already taken:

perl -F, -lpe 'splice @F, 1, 1, ($F[1] =~ m/^(\d+)(\d{6})$/g); $_ = join ",", @F' file

With vim, just for fun:

:%s/\m,\d*\zs\ze\d\{6},/,/
  • can you explain the use of splice here? perl -F, -lane '$F[1]=~s/.{6}$/,$&/; print join ",", @F' would be similar to awk solution.. – Sundeep May 3 '17 at 14:23
  • @Sundeep No real reason, I suppose I was thinking in terms of arrays. – Satō Katsura May 3 '17 at 14:27
  • oh okay.. I did get to check the doc what is splice ;) – Sundeep May 3 '17 at 14:31
1
perl -pe 's/(,.*?,)/$1 =~ s!......,!,$&!r /e'  x.csv
  • substitute second field (,.*?,) by the ...
  • result of the substitution ( =~ s! ...!...!r) of last 6 digits (......) by them and and a ,

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.