format a telephone number

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

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

Bob Harris <nospam.News.Bob@remove.Smith-Harris.us> writes:

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

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

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.

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