Importing telephone numbers from excel

  • Thread starter Thread starter Support
  • Start date Start date
S

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
 
Support said:
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
Click to expand...

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!
 
Good design will use text for phone numbers. You only need to use
numbers when you are going to use the numbers as numbers and you don't do
that with phone numbers. For example if you add subtract or compare numbers
you need them to be numbers. 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.

Almost always telephone numbers should be numbers.
 
Joseph Meehan said:
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.
Click to expand...

And not only that, but once the leading zero(s) have gone it's not even a
valid phone number any more!
 
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)
 
Thanks Douglas. Thought that would be the case.
Douglas J. Steele said:
Definitely one table.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


calls even
Click to expand...
 
Back
Top