mjhjmtu

I periodically receive a text file with phone numbers formatted in wildly different ways: ##########, ###-###-####, (###) ###-###, etc. Usually there's ten digits, but I've seen +1 (###) ###-####.

Eventually the file gets imported into a database, but for reasons I won't go into, it'd be handy to have the phone numbers have a standard format, (###) ###-####.

The only constant is that the phone numbers always fall between the second and third tab character on each line.

Is there a way to do this from the command line?

This should cover you as long as the file is as you have described. The command preserves information before and after the phone number and formats it in the way that you asked for. If the output looks good, add the -i option to sed to edit it in place or provide it with output redirection using > output_file at the end.

sed -E "s/(.*t.*t)+?1?[[:space:]]?(?([0-9]3))?.*([0-9]3).*([0-9]4)(.*)/1(2) 3-45/g" filename

I tested it on a file containing this text:

 jfk 902-765-9292 hat jump cat
 jk 902 819 2244 hat jump cat
 98 902 823-4456 hat jump cat
 78h +1 075 242 1566 hat jump cat
jklj kjlj +1 075-242-1566 hat jump cat
jk jkj +1 (075) 242-1566 hat jump cat
 kj (204) 799-9810 hat jump cat
kj 89 (204)-799-9810 hat jump cat

The output was:

 jfk (902) 765-9292 hat jump cat
 jk (902) 819-2244 hat jump cat
 98 (902) 823-4456 hat jump cat
 78h (075) 242-1566 hat jump cat
jklj kjlj (075) 242-1566 hat jump cat
jk jkj (075) 242-1566 hat jump cat
 kj (204) 799-9810 hat jump cat
kj 89 (204) 799-9810 hat jump cat

You can construct a regular expression that matches any of the formats, and captures the digits then re-substitutes them in your desired format.

For example, to match and capture a sequence of three decimal digits optionally surrounded by parentheses with an Extended Regular Expression (ERE), you can write (?([0-9]3))? while [- ]? matches an optional hyphen or space. Building up in this way

(?([0-9]3))?[- ]?([0-9]3)[- ]?([0-9]4)

will match 3 digits optionally parenthesized, optionally followed by a hyphen or space, then more digits optionally followed by a hyphen or space, followed by 4 digits.

Applying the expression in a sed substitution:

$ cat <<EOF | sed -E 's/(?([0-9]3))?[- ]?([0-9]3)[- ]?([0-9]4)/(1) 2-3/g'
I periodically receive a text file with phone numbers formatted 
in wildly different ways: 123 456-7890, 123 456-7890, 123 456-7890, 
etc. Usually there's ten digits, but I've seen +1 555 456-7890.
EOF
I periodically receive a text file with phone numbers formatted 
in wildly different ways: (123) 456-7890, (123) 456-7890, (123) 456-7890, 
etc. Usually there's ten digits, but I've seen +1 (555) 456-7890.

You need to match the field and the re-format it; here's an awk script that looks for three variations and re-formats them (before default-printing the reconstituted line):

$3 ~ /^[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]$/ 
 $3="(" substr($3, 1, 3) ") " substr($3, 4, 3) "-" substr($3, 7, 4)


$3 ~ /^[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]$/ 
 $3="(" substr($3, 1, 3) ") " substr($3, 5, 3) "-" substr($3, 9, 4)


$3 ~ /^+1 ([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]$/ 
 $3="(" substr($3, 5, 3) ") " substr($3, 10, 3) "-" substr($3, 14, 4)


1

Save that to a file, perhaps phone.awk, then call it with: awk -F $'t' -f phone.awk < input.