0

I have a file that looks like this:

Text1 somethingAAxxxxxxxsomething,elseAAxxxxxxxfoo text1
Text2 somethingAAxxxxxxxsomething,elseAAxxxxxxxfoo text2
Text3 somethingAAxxxxxxxsomething,elseAAxxxxxxxfoo text3

The "something", something,else, and foo are random letters/spaces/commas The AAxxxxxxx is what I want to match. the X's are numbers, and it's always an array of 7 numbers 0-9 so example AA0000001 or AA9999999. I want to extract just the AAxxxxxxx part from column 2 so my output reads:

Text1 AAxxxxxxx,AAxxxxxxx text1
Text2 AAxxxxxxx,AAxxxxxxx text2
Text3 AAxxxxxxx,AAxxxxxxx text3

Example input

Text1 somethingAA0123456something,elseAA6543210foo text1
Text2 somethingAA1234567something,elseAA7654321foo text2
Text3 somethingAA2345678something,elseAA8765432foo text3

Desired output

Text1 AA0123456,AA6543210 text1
Text2 AA1234567,AA7654321 text2
Text3 AA2345678,AA8765432 text3

Edit: A few lines contain more than 2 "AAxxxxxxx" segments for example

Input

Text1 somethingAAxxxxxxxsomething,elseAAxxxxxxxfooblahAAxxxxxxx^blahblahAAxxxxxxx text1
Text2 somethingAAxxxxxxxsomething,elseAAxxxxxxxfooblahAAxxxxxxx^blah text2
Text3 somethingAAxxxxxxxsomething,elseAAxxxxxxxfoo text3

Desired Output

Text1 AA0123456,AA6543210,AA1231252,AA1256712 text1
Text2 AA1234567,AA7654321,AA1926572 text2
Text3 AA2345678,AA8765432 text3
2

sed can do that. We can use 4 matching groups to find the prefix, suffix and two IDs in the middle.

Code:

sed -rn 's/([^ ]+) .*(AA[0-9]{7}).*(AA[0-9]{7}).* ([^ ]+)/\1 \2,\3 \4/p' file1

Test Data:

Text1 somethingAA0123456something,elseAA6543210foo text1
Text2 somethingAA1234567something,elseAA7654321foo text2
Text3 somethingAA2345678something,elseAA8765432foo text3

Results:

Text1 AA0123456,AA6543210 text1
Text2 AA1234567,AA7654321 text2
Text3 AA2345678,AA8765432 text3
  • This works very well too, Is there any way you can repeat the ".*(AA[0-9]{7}).*" section an "unlimited" amount of times? For example line 1 has that expression 2 times, but line 3 has it 10 times and line 4 has it once. – Blaze Mar 30 '17 at 3:38
  • I do not make it a habit of answering the same question twice. My answer meets the requirements of your question. It does not meet the requirements of your second question. Which should have been another question. Editing the question after it has been answered (three times) is, .... arg.. You will note george, just gave an entirely new answer. – Stephen Rauch Mar 30 '17 at 3:44
  • I completely understand! Thank you for the solution as is though. – Blaze Mar 30 '17 at 3:52
2

perl approach. Similar to the sed approach. Test the line against a lengthy regular expression, capturing the relevant desired parts of the line into $1, $2, $3, $4. Construct an answer in $_ so that it then gets printed thanks to the '-p' flag.

$ perl -pe 'if(/^(Text\d+) .*(AA\d{7}).*(AA\d{7}).* (.*)/){$_="$1 $2,$3 $4$/"}' file1
Text1 AA0123456,AA6543210 text1
Text2 AA1234567,AA7654321 text2
Text3 AA2345678,AA8765432 text3
$
2

awk solution:

$ a="Text1 somethingAA0123456something,elseAA9876543foo text1"
$ awk -F"[ ,]" '{match($2,/(AA[0-9]{7})/,a);match($3,/(AA[0-9]{7})/,b);print $1,a[1],",",b[1],$NF}' <<<"$a"
Text1 AA0123456,AA9876543 text1

Also this works:

$ awk '{match($0,/(\w+\s)(\w+)(\w\w[0-9]{7})(\w+,\w+)(\w\w[0-9]{7})(\w+\s)(\w+)/,a);print a[1],a[3],",",a[5],a[7]}' <<<"$a"

UPDATE
For your new requirements and with GNU awk you can use something like this:

$ echo "$b"
Text1 somethingAA1111111something,elseAA2222222fooblahAA3333333^blahblahAA4444444 text1
Text2 somethingAA1111111something,elseAA7777777fooblahAA5454545^blah text2
Text3 somethingAA1111111something,elseAA2222222foo text3

$ awk '{gsub(/(AA[0-9]{7})/," & ",$2)}1' <<<"$b" |awk '{printf("%s ",$1);for (i=2;i<NF;i++) {if($i ~ /AA[0-9]+/) printf("%s%s",$i,(i==NF-1)?" ":",")}}{printf(" %s\n",$NF)}'
Text1 AA1111111,AA2222222,AA3333333,AA4444444  text1
Text2 AA1111111,AA7777777,AA5454545, text2
Text3 AA1111111,AA2222222, text3

Only pitfall that there is an extra comma in some records after the last AAXXXXXXX . Hope this is not a big problem.

This solution combines two awk. Ths first awk transform each line by injecting a space before and after each AAXXXXXXX found:

$ echo "$a"
Text2 somethingAA1234567something,elseAA0987654fooblahAA3333333^blah text2
$ awk '{gsub(/(AA[0-9]{7})/," & ",$2)}1' <<<"$a"
Text2 something AA1234567 something,else AA0987654 fooblah AA3333333 ^blah text2

This transformed records are then fed to second awk which prints the first field, the last field and the intermediate fields that match the pattern AAXXXXXXX

  • I tried your first solution and it works very well! Thanks! Some of my fields in col2 have more than 2 "AAxxxxxxx" to match against. This script will only output the first 2 matches. Is there anyway to match against all AAxxxxxx in col2? – Blaze Mar 29 '17 at 21:57
  • I would like to fix it, but can you please add such a line in your question? Will be easier if i see the sample – George Vasiliou Mar 29 '17 at 22:01
  • 1
    Thank you for helping so far! I just edited my initial post w/ a new example, at the end. Also added an example of an error I ran into, the code you provided returns everything after an "^" which appears in some of my lines. – Blaze Mar 29 '17 at 22:06
  • @Blaze Answer updated with new requirements. Let me know if it is ok. It was harder than expected... – George Vasiliou Mar 30 '17 at 0:16
  • @GeorgeVasiliou. Why are you answering a revised question? There are now three answers here that answer a question, that has been edited away. And one answer that answers the new question. Should not the new question have been a new question? – Stephen Rauch Mar 30 '17 at 3:50
2

Bash Solution for the updated request:

echo "$b"
#Output
Text1 somethingAA1111111something,elseAA2222222fooblahAA3333333^blahblahAA4444444 text1
Text2 somethingAA1111111something,elseAA7777777fooblahAA5454545^blah text2
Text3 somethingAA1111111something,elseAA2222222foo text3

while IFS=" " read -r f1 f2 f3;do 
  g=($(grep -Po 'AA[0-9]{7}' <<<"$f2"));
  out=$(printf '%s,%s,%s\n' "$f1" "${g[*]}" "$f3");
  echo "$out" |sed 's/,/\x00/g; s/ /,/g; s/\x00/ /g';
done <<<"$b"
#Output
Text1 AA1111111,AA2222222,AA3333333,AA4444444 text1
Text2 AA1111111,AA7777777,AA5454545 text2
Text3 AA1111111,AA2222222 text3
1

Perl

perl -pale '$_ = join $", $F[0], join(",", $F[1] =~ /AA\d{7}/g), @F[2..$#F]' yourfile

Bash

The use of cat is intentional here, as we don't want to destroy the positional parameters ($1, $2, ..., $#) and hence running the while-loop in a subshell.

cat yourfile |
while read -r f1 f2 rem; do
   set -- "$f1" "$(printf '%s\n' "$f2" | grep -oP 'AA\d{7}' | paste -sd,)" "$rem"
   printf '%s\n' "$*"
done

Sed

sed -e '
   s/[^ ]*[ ]*/&\
\
/
   s/AA[0-9]\{7\}/\
&\
/g

   :loop
      s/\nAA[0-9]\{7\}\(\n\)/\1&/
      s/\n\n.*\(\n\n\)/\1/
      s/\(\n\n\)\(AA[0-9]\{7\}\)\n/\2,\1/
   /\nAA[0-9]\{7\}\n/bloop

   s/,\n\n[^ ]*//
' yourfile

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.