Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import from Excel (Access 2000)

    Here's the scenario:
    In a spreadsheet in Excel I have a SSN field which is formatted as TEXT, along with other fields for personnel information. About 75 of 900 of those SSN records actually begin with a letter N for example N364568765 to distinguish them from the rest for example because they are Naval Personnel, and its always been relayed that way in this database...(I'm not here to change it). However, once imported into Access, I get of course an error table containing those 75 records. Can't Access tell that this is a formatted TEXT field and import ALL the SSN's whether they contain numbers or numbers with letters, it shouldn't make a difference. In Excel the SSN field is formatted as TEXT remember. Can't Access pick up on that? I've even tried making a new table (structure only with SSN being a TEXT data type), and then importing the Excel data into the new table I just created, and it still makes an error table of those 75 records with SSN's that have the letter N in them. I know I can remove the N, and all will be rosy. But I should not have to, and that is the point, nor do I want a seperate field for the letter N...rather...just the way it is. Can anyone clue me in on what I may be doing wrong, or what trick I can play here on Access to "do what I say"...(laughing uncontrollably on this comment). Appreciate any input here.
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import from Excel (Access 2000)

    Access scans the content of the first rows to determine the field type, not the format; I don't know the exact number it looks at (somewhere around 6, I believe).

    A trick I have used sometimes is to add a dummy row in Excel immediately below the field names where each field is of the desired type (a "1" in each numeric column, a "B" in each text column, something like 01/01/01 in each date column). This will force Access to use the correct field types. After importing, delete the first row.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import from Excel (Access 2000)

    Thank you HansV. I will try this "first thing" in the morning when I return to work. Sounds like a perfect solution.
    As always,
    Thank you sincerely,
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Import from Excel (Access 2000)

    Hans' comment is correct as long as you are importing into a new table, but if you import into an existing table, Access should happily accept the data as text data. As an alternative, you might try saving the excel workbook as a CSV file and then look at it in Word or Notepad to be sure the data is really text and not numeric for the ones which do not have an alpha prefix. If it is, then try importing from the delimited text format and see if that works - it's possible Excel is doing something in the transfer.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import from Excel (Access 2000)

    Wendell,
    Thank you. Yes, the Excel worksheets are going to go into brand new tables in a brand new Access Database. I tried Hans method first, and it worked perfectly, so did not have to try second method you suggested. But thank you sincerely for the extra idea, just in case the first did not work.
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Import from Excel (Access 2000)

    I too have had problems like this - both importing into a new and an existing table. As a final suggestion, what I do now is select my entire data range in Excel and sort it in descending order by the offending field, and then save the spreadsheet. Then when Access comes to do the import it is forced to read the alphabetic entries first.

    Jeremy

Posting Permissions

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