Results 1 to 4 of 4
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Import Excel File (2002)

    I've imported Excel files before but this is a first time I've had this problem and maybe someone else has too that can help.

    I import a file of 56 records and the wizard dialog boxes shows my column headings and data. Everything looks good. I specify no primary key. When I am finished I get no errors. I open the table and I have over 34,000 rows with blank data. The column headings are correct and the data types are correct but no data.

    Would there be any bad characters hidden or otherwise in an Excel file that could cause this type of problem? The data in the Excel file is preceeded by an apostrophy in each cell that is text so that seems normal to me.
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Import Excel File (2002)

    The worksheet probably contains empty strings, or spaces, or formatting that makes Excel think the rows are not really blank.
    You can delete the blank looking records in Access, or you can do the following:
    - Open the workbook in Excel.
    - Activate the worksheet you want to import into Access.
    - Select all rows below the last "populated" row.
    - Press Delete.
    - Save the workbook.

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Import Excel File (2002)

    Since none of the records got copied over to the access table I had to take your second suggestion of deleting all the records in Excel past the last populated row and it work. There must have been something in those other rows that Access didn't like.

    Thanks for the quick help. That was simple. I wish all my technical problems were that simple. <img src=/S/angel.gif border=0 alt=angel width=15 height=21>
    You know it's time to diet when you push away from the table and the table moves.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import Excel File (2002)

    I think Hans has hit the nail on the head. Just from experience when I am importing csv files downloaded from legacy systems at work, I find hidden ASCII characters get imported from some of the fields. Just to check I am not going mad I create a query from the table including all the fields and then create a new field called lengthtest: len([TheField]) and run it to see if there are results greater than 0. If there are I know there is hidden stuff. So I delete it just to keep my recordset clean and tidy. My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth, if only to investigate an unexpected anomaly.
    Jerry

Posting Permissions

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