none
Access 2013 imports over a million empty rows when importing Excel worksheets RRS feed

  • Question

  • I have an Access 2013 database where over a millions rows are added to the beginning the table when an import from Excel 2013 worksheet is run. For example, I imported 544 Excel rows into an Access 2013. Over a million blank records showed up in the Access table. The blank records were before the actual records. This was an Append to Table operation. 

    I tried it again with another table and Excel file. The Excel worksheet had about 2500 rows. Again, over a million blank records were put into the Access table before the actual records. This was also an append operation.

    I have never seen this before. The Access database is on a shared drive.

    This NOT an Access Web app.

    Tuesday, August 16, 2016 9:14 PM

Answers

  • I figured it out. We were importing an Excel worksheet through the External Data tab and Import Excel using the Import Wizard. No VBA or anything else. The issue was the entire Excel worksheet, empty rows and all, was included with the data. So I copied the data from the worksheet and did a Paste Values into a new worksheet. Now only the data is imported into Access using the Import Wizard. The original Excel worksheet must have been corrupted.
    • Marked as answer by TWIKLE Wednesday, August 17, 2016 4:59 PM
    Wednesday, August 17, 2016 4:59 PM

All replies

  • I have an Access 2013 database where over a millions rows are added to the beginning the table when an import from Excel 2013 worksheet is run. For example, I imported 544 Excel rows into an Access 2013. Over a million blank records showed up in the Access table. The blank records were before the actual records. This was an Append to Table operation. 

    I tried it again with another table and Excel file. The Excel worksheet had about 2500 rows. Again, over a million blank records were put into the Access table before the actual records. This was also an append operation.

    I have never seen this before. The Access database is on a shared drive.

    This NOT an Access Web app.

    Where does the worksheet come from?  Is it generated by a program, by any chance?  Are the blank rows present in the Used Range of the spreadsheet? What happens if you open the worksheet in Excel and press END+HOME ?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, August 16, 2016 9:44 PM
  • In addition to what Dirk said: apparently your table design allows blanks in all those fields. You may want to reconsider the Required property, as well as perhaps a unique index, to enforce business rules.

    -Tom. Microsoft Access MVP

    Wednesday, August 17, 2016 12:25 AM
  • Hi Twikle,

    First of all I want to confirm with you that how you import the excel data in Access?

    by user interface or by a code? if it is a code then can you show your code to us?

    you can try to import only specific range and test the results.

    if you are using user interface show your steps.

    also as other community member already suggested you to modify the design of your table and don't allow null and empty data.

    let us know about your approach so that we can try to provide you a further help.

    you can also try something like below. it will import only specified range of data in Access Table.

    Private Sub Command0_Click()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
     "xldata", "C:\Users\v-padee\Desktop\demo.xlsx", False, "A1:A10"
    End Sub
    

    For more information regarding DoCmd.TransferSpreadsheet Method (Access) visit the link below.

    DoCmd.TransferSpreadsheet Method (Access)

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 17, 2016 12:50 AM
    Moderator
  • I figured it out. We were importing an Excel worksheet through the External Data tab and Import Excel using the Import Wizard. No VBA or anything else. The issue was the entire Excel worksheet, empty rows and all, was included with the data. So I copied the data from the worksheet and did a Paste Values into a new worksheet. Now only the data is imported into Access using the Import Wizard. The original Excel worksheet must have been corrupted.
    • Marked as answer by TWIKLE Wednesday, August 17, 2016 4:59 PM
    Wednesday, August 17, 2016 4:59 PM