Importing From Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mk2 - 29 Oct 2004 16:13 GMT

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!

Reply to this Message

Frank Stone - 31 Oct 2004 22:53 GMT

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

>-----Original Message-----
>I am importing contact information data from Excel. The phone numbers in the
[quoted text clipped - 15 lines]
>Thanks for your help!
>.

Reply to this Message

Jamie Collins - 01 Nov 2004 12:30 GMT

> > import this data? Do I
> > need to eliminate all of the non-numeric characters
[quoted text clipped - 5 lines]
> 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.

--

Reply to this Message