Results 1 to 2 of 2
  1. #1
    mari_hitz is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    28

    Import Data from Excel returns blank rows

    Hi All,

    Hope you are good. Please note that I have a question regarding importing data from excel to access. My goal is to have a code that allows you to choose a file from excel and then from that file import the data to a table I have in access.
    The thing is that when the information is transferred to the Access table all the data is pasted , lets say 400 rows, but in addition ALL the blank rows from the excel file -around 15000 rows -. Do you know why this is happening?


    I would like to only paste the data not the blank rows.

    Thanks in advance!

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Usually that happens when those "extra" rows in Excel contain formatting or formulas. If you remove that from Excel (or delete extra blank lines) and re-save the Excel file, it should take care of that.

    Note that there are also a few other workarounds, such as:
    - Name the range you want to import in the Excel file, and then have Access import the named range instead of the whole sheet.
    or
    - Make one of the fields in Access a required field that does not allow blanks. Then when you import, all the "blank" rows will fail and not import into your table.
    or
    - Import the data into a temporary Table first in Access. Then write an Append Query that writes the non-blank records from the temporary Table to your final Table in Access.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DateDiif Query returns blank data?
    By MarkF2004 in forum Access
    Replies: 7
    Last Post: 01-27-2017, 06:52 PM
  2. How to remove blank rows when I import from excel
    By timpepu in forum Import/Export Data
    Replies: 3
    Last Post: 09-14-2015, 04:04 PM
  3. Replies: 2
    Last Post: 12-12-2013, 02:13 PM
  4. Importing Excel File - Getting 7 extra blank rows each import
    By eking002 in forum Import/Export Data
    Replies: 4
    Last Post: 06-13-2013, 09:15 AM
  5. Replies: 4
    Last Post: 01-24-2013, 12:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums