Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Telephone Numbers (XP)

    I have Excel spreadsheets with 2 separate columns for Area Code and Telephone Number. Telephone Number field is formatted as Text. No dash after first 3 prefix. I have copied and paste special - Values only. When I import into DB with telephone field being text, length 255 (after noticing this, I'll cut it down to 8) - no input masks, sometimes the phone number appears as 6525000 and sometimes it appears as 6.525e+006. What am I doing wrong?

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Importing Telephone Numbers (XP)

    It's a common problem encountered when trying to import from Excel or text files. Access makes the determination on the first few records (sometimes the first it seems), and then does it's own thing. I would suggest you consider creating the table before you import so you can control the result, and then forcing it to be a text field. Invariably, someone wants to store an extension, or some other arcane thing, or they want to store international numbers, etc. For most of our designs, we now use either a 32 or 64 character text field, and require things like dashes in the number, either with input masks, or with VBA to verify the number after entry. Since people often have multiple phone numbers these days, we create an Electronic Address table, and store phone numbers, faxes, pagers, cell phones, email addresses and web addresses in the same table. Hope this helps.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Telephone Numbers (XP)

    Creating table first and importing didn't help!! Anyone else have an idea?

    thanks.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Importing Telephone Numbers (XP)

    If you are getting 6.525e+006 then Access is treating the phone number as numbers, not text.

    Try putting in a dummy phone number before your first one with each column containing completely unambiguous pieces of text : "aaaa" , "bbbb" to try to force access to create the field as a text field rather than a number field.
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Telephone Numbers (XP)

    Finally got it to work!!! Only way I could do it was save Excel spreadsheet as a txt file and then importing text file into Access.

    Thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •