Importing telephone numbers from excel

[Support]

Hi, I have a spreadsheet showing call data from our phone bills The column
containing the phone numbers was as format 0111 123 1234 or 01234 567890
which are UK format numbers. I have run a formula =SUBSTITUTE(C1," ","") in
my phone number column (C) to remove spaces succesfully although this seems
to have the cell formatted as text.
When I import this into my table, records are deleted. I know my table
design is at fault here as I have formatted that field as number. If I
change the field format to text, the records import fine. My question is in
good db design, should the phone numbers be stored as text or numbers? If
as numbers, how can I import them correctly?
Thanks

 

[Brian]

Hi, I have a spreadsheet showing call data from our phone bills The column
containing the phone numbers was as format 0111 123 1234 or 01234 567890
which are UK format numbers. I have run a formula =SUBSTITUTE(C1," ","") in
my phone number column (C) to remove spaces succesfully although this seems
to have the cell formatted as text.
When I import this into my table, records are deleted. I know my table
design is at fault here as I have formatted that field as number. If I
change the field format to text, the records import fine. My question is in
good db design, should the phone numbers be stored as text or numbers? If
as numbers, how can I import them correctly?
Thanks

Personally I always have phone numbers as text fields, because they commonly
have various non-numeric characters in them such as "-", "+", "(", ")", the
word "Ext" and so on.

If you can't import all of your data into a numeric field, then probably you
have some such non-numeric data in your spreadsheet. If you are determined
to use a numeric field, then you will need to eliminate the non-numeric
data. You could find this data by linking or importing the spreadsheet with
the phone number as a text field, and running a query such as this:

SELECT * FROM my_table WHERE NOT IsNumeric(phone_number)

What you then do to fix the data (manual correction, update queries or
whatever) will depend on what you find!

 

[Support]

Thanks guys, just wan't sure whether I should use text or numbers as best
practice.

 

[Brian]

Sorting if you want to sort by large to small
and not based on the first digit also need to be numbers or have leading
zeros.

And not only that, but once the leading zero(s) have gone it's not even a
valid phone number any more!

 

[Support]

OK, my next question is:
I am trying to run analysis on phone bills from our retail outlets - 17 in
all.
Would best design be to import each outlet bill into it's own table or just
import into a master 'total' table or both?
My query requirements are :
1) I would like to run queries on individual shops - eg how many calls to
a certain number
2) I would like to do the above but see how many calls to a certain
number in total (for all outlets)

 

[Douglas J. Steele]

Definitely one table.

 

[Support]

Thanks Douglas. Thought that would be the case.

Definitely one table.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


calls even