AWK - format a telephone number

Author

format a telephone number

kosuke

2005-04-10, 3:55 am

I have a lot of data in tab-delimited column form like this:

ABC Company Houston, TX 34592 7604528734
DEF Widgets and Software La Jolla, CA 92073 8583568236
and more ...

Company names and city names are of varying word length, which I can
deal with since it is tab delimited and ultimately I'll import this
into a mysql DB.

What I'd like to do before importing into mysql is format the telephone
number in the last column. All of the telephone numbers are 10 digits
exactly, and I'd like to format them as (xxx) xxx-yyyy.

I've attempted this countless times and always wind up with a mess more
than anything else. Have googled for "commify" and gotten good tips
but not a clean result yet. Would sure appreciate any pointers. I'm
using Gnu sed and nawk.

Thanks,
Kevin

Bob Harris

2005-04-10, 3:55 am

In article <1113098879.526511.214280@f14g2000cwb.googlegroups.com>,
"kosuke" <kevin@rustybear.com> wrote:

> I have a lot of data in tab-delimited column form like this:
>
> ABC Company Houston, TX 34592 7604528734
> DEF Widgets and Software La Jolla, CA 92073 8583568236
> and more ...
>
> Company names and city names are of varying word length, which I can
> deal with since it is tab delimited and ultimately I'll import this
> into a mysql DB.
>
> What I'd like to do before importing into mysql is format the telephone
> number in the last column. All of the telephone numbers are 10 digits
> exactly, and I'd like to format them as (xxx) xxx-yyyy.
>
> I've attempted this countless times and always wind up with a mess more
> than anything else. Have googled for "commify" and gotten good tips
> but not a clean result yet. Would sure appreciate any pointers. I'm
> using Gnu sed and nawk.
>
> Thanks,
> Kevin

awk '
BEGIN { FS="\t"; OFS="\t" }
{
areacode = substr($4,1,3)
exchange = substr($4,4,3)
number = substr($4,7,4)
$4 = "(" areacode ") " exchange "-" number
print
}
'

Bob Harris

Ulrich M. Schwarz

2005-04-10, 8:55 am

Bob Harris <nospam.News.Bob@remove.Smith-Harris.us> writes:
[color=darkred]
> In article <1113098879.526511.214280@f14g2000cwb.googlegroups.com>,
> "kosuke" <kevin@rustybear.com> wrote:
>
[...][color=darkred]

Littletested, off-topic here, and my sed is a bit rusty:
sed -e 's/$...$$...$$....$$/(\1) \2-\3/'

Ulrich
--
FX: A thunderclap, a loud wind, a plague of breakfast cereal, lava flows
under the raised-flooring, the earth opens, and D... suddenly goes Down,
Not Across. A choir of little tentacled things sings, and he achieves
Recovery. -- AdB

Ed Morton

2005-04-10, 3:55 pm

Ulrich M. Schwarz wrote:
> Bob Harris <nospam.News.Bob@remove.Smith-Harris.us> writes:
>
>
>
> [...]
>
>
>
> Littletested, off-topic here, and my sed is a bit rusty:
> sed -e 's/$...$$...$$....$$/(\1) \2-\3/'

gawk has a very similair capability in gensub():

gawk '$NF=gensub(/(...)(...)(....)/,"(\\1) \\2-\\3","",$NF)'

if you feel like keeping it topical ;-)

Ed.

kosuke

2005-04-10, 8:55 pm

Terrific! All 3 of these work with slight modifications. Thanks
folks.
Kevin

Sponsored Links