Importing From Excel

[Guest]

I am importing contact information data from Excel. The phone numbers in the
excel spreadsheet have been entered inconsistently. Here are examples:

(xxx) xxx-xxxx
xxx-xxx-xxxx
xxx/xxx-xxxx
xxx.xxx.xxxx

I am importing into a phone number field with an input mask of:

!\(999") "000\-0000;0;_

My question: What is the easiest and fastest way to import this data? Do I
need to eliminate all of the non-numeric characters [(,),-,/ and spaces]
before I import?

Thanks for your help!

 

[Frank Stone]

hi,
see this web site
http://www.contextures.com/xlDataVal01.html
you need to contact all your users about the correct way
to input a phone number. this would be easier than
writing potentiually hundreds of lines of code to
compensate for all of the possible input parameters
(perticularly since you have an input mask).
(been there, done that....don't work)
a company standard seems to be in order. data validation
seems in order too.
to answer you question, eliminate all of the characters
and rebuild per the input mask (but this takes us back to
the "potentiually hundreds of line of code")
good luck
Frank

 

[Jamie Collins]

import this data? Do I
need to eliminate all of the non-numeric characters
[(,),-,/ and spaces]
before I import?

you need to contact all your users about the correct way
to input a phone number. this would be easier than
writing potentiually hundreds of lines of code to
compensate for all of the possible input parameters

Tool late, sound like the data is already written!

Rather than trying to eliminate all the illegal characters, just
select the ones you know are valid i.e. numeric characters e.g.

SELECT
IIF(MID(MyDataCol,1,1) LIKE '[0-9]', MID(MyDataCol,1,1), '') &
IIF(MID(MyDataCol,2,1) LIKE '[0-9]', MID(MyDataCol,2,1), '') &
IIF(MID(MyDataCol,3,1) LIKE '[0-9]', MID(MyDataCol,3,1), '') &
IIF(MID(MyDataCol,4,1) LIKE '[0-9]', MID(MyDataCol,4,1), '') &
IIF(MID(MyDataCol,5,1) LIKE '[0-9]', MID(MyDataCol,5,1), '') &
IIF(MID(MyDataCol,6,1) LIKE '[0-9]', MID(MyDataCol,6,1), '') &
IIF(MID(MyDataCol,7,1) LIKE '[0-9]', MID(MyDataCol,7,1), '') &
IIF(MID(MyDataCol,8,1) LIKE '[0-9]', MID(MyDataCol,8,1), '') &
IIF(MID(MyDataCol,9,1) LIKE '[0-9]', MID(MyDataCol,9,1), '') &
IIF(MID(MyDataCol,10,1) LIKE '[0-9]', MID(MyDataCol,10,1), '') &
IIF(MID(MyDataCol,11,1) LIKE '[0-9]', MID(MyDataCol,11,1), '') &
IIF(MID(MyDataCol,12,1) LIKE '[0-9]', MID(MyDataCol,12,1), '') &
IIF(MID(MyDataCol,13,1) LIKE '[0-9]', MID(MyDataCol,13,1), '') &
IIF(MID(MyDataCol,14,1) LIKE '[0-9]', MID(MyDataCol,14,1), '') &
IIF(MID(MyDataCol,15,1) LIKE '[0-9]', MID(MyDataCol,15,1), '') &
IIF(MID(MyDataCol,16,1) LIKE '[0-9]', MID(MyDataCol,16,1), '')
FROM MyTable;

Jamie.

--