RegEx: Replaces every match that occurs, but not in the first 8 characters

user_15830899 0
Viewed 74

I have a textfile which contains some service usage data. I want to replace every dot (.) with a comma (,). This needs to be done due to decimal seperator inconsitancy. Then remove all spaces, then separate every column with a semikolon (;).

Original Data:

A.FOOBAR       0.000        0.000          0.000          0.000        0.061         0.000        0.000        0.061
AFOOB.BI       0.000        0.000          0.000          0.000        0.085         0.000        0.000        0.085
ABFOOB         0.000        0.000          0.000          0.000        0.000         0.000        0.000        0.000
ABFUUUA        0.000        0.000          0.000          0.000        0.061         0.000        0.000        0.061
ACFOO.GA       0.000        0.000          0.000          0.000        0.061         0.000        0.000        0.061
ADFOO.BA       0.000        0.000          0.000          0.000        0.061         0.000        0.000        0.061
AFOO.ER        0.000        0.000          0.000          0.000        0.061         0.000        0.000        0.061
AFBARFE1       0.000        0.000          0.000          0.000        0.061         0.000        0.000        0.061
AGFOOFD        0.000        0.000          0.000          0.000        0.061         0.000        0.000        0.061
AGFOOO.K       0.000        0.000          0.000          0.000        0.061         0.000        0.000        0.061

My current regex looks like:

echo in.txt > sed -r 's/[[:blank:]]+/;/g' | sed -r 's/\./,/g'

This results in

A,FOOBAR;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AFOOB,BI;0,000;0,000;0,000;0,000;0,085;0,000;0,000;0,085
ABFOOB;0,000;0,000;0,000;0,000;0,000;0,000;0,000;0,000

Of cource this matches also the first dot... I cannot use the 2g-Operator because not every username contains a dot. How am I able to "not match" the first 8 characters?

Thanks a lot!

sed awk regex
4 Answers

Perhaps you could make use of awk instead of sed, replacing all dots with a comma, except for the first field.

awk 'BEGIN{FS="[[:blank:]]+";OFS=";"}{for(x=2;x<=NF;x++) {gsub("\\.",",",$x)}}1' file

In parts

awk '
BEGIN{                 
  FS="[[:blank:]]+"       # Field separator is 1+ occurrences of spaces or tabs
  OFS=";"                 # Output field separator is a semicolon
}
{
  for(x=2;x<=NF;x++) {    # Loop all fields, start at the second field. NF is the number of all fields
    gsub("\\.",",",$x)    # Replace all dots with a comma
  }
}1                        # 1 evaluates to true, default action to print current line
' file                    # input file

Output

A.FOOBAR;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AFOOB.BI;0,000;0,000;0,000;0,000;0,085;0,000;0,000;0,085
ABFOOB;0,000;0,000;0,000;0,000;0,000;0,000;0,000;0,000
ABFUUUA;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
ACFOO.GA;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
ADFOO.BA;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AFOO.ER;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AFBARFE1;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AGFOOFD;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AGFOOO.K;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
user_54249880

With your shown samples, please try following.

awk '
BEGIN{
  OFS=";"
}
{
  val=substr($0,9)
  gsub(/\./,",",val)
  $0=substr($0,1,8) val
  $1=$1
}
1
' Input_file

Explanation: Adding detailed explanation for above.

awk '                      ##Starting awk program from here.
BEGIN{                     ##Using BEGIN section of this program.
  OFS=";"                  ##Setting OFS to ; here.
}
{
  val=substr($0,9)         ##Creating val with sub string for current line.
  gsub(/\./,",",val)       ##Globally substituting to substitute dot with , in val.
  $0=substr($0,1,8) val    ##Re-creating current line with sub string of 8 chars and val
  $1=$1                    ##re shuffling $1 here.
}
1                          ##Printing current line here.
' Input_file               ##Mentioning Input_file name here.
user_58665800

Besides 2 working awk answer, using gnu awk, you can also use this shorter command:

awk -v OFS=';' '{$1=$1; print substr($0, 1, 8) gensub(/\./, ",", "g", substr($0, 9))}' file

A.FOOBAR;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AFOOB.BI;0,000;0,000;0,000;0,000;0,085;0,000;0,000;0,085
ABFOOB;0,000;0,000;0,000;0,000;0,000;0,000;0,000;0,000
ABFUUUA;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
ACFOO.GA;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
ADFOO.BA;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AFOO.ER;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AFBARFE1;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AGFOOFD;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
AGFOOO.K;0,000;0,000;0,000;0,000;0,061;0,000;0,000;0,061
user_5482250

This might work for you (GNU sed):

sed -E ':a;s/^([^ .]*)\./\1\n/;ta;s/ +/;/g;y/\n./.,/' file

Replace every period in the first field by a newline.

Replace one or more spaces by a semi-colons.

Translate newlines back to periods and periods to commas.

user_9674920
Related