locked
OleDB duplicating records RRS feed

  • Question

  • User-718146471 posted

    Forgive me if this is in the wrong place but I figure I would ask here anyway.  I am using OleDB to read records from Excel.  The crazy thing is it was working properly for the longest.  Now all of a sudden, it takes my records and duplicates them.  To rule out code related issues, I tried a test on an excel with only one record.  That one was fine.  So I figured after my reboot the problem was resolved. It is not.  I simply do not get what the problem is because it does not always do this.  It is at random.

    Thursday, February 19, 2015 2:28 PM

Answers

  • User-718146471 posted

    This issue is resolved.  By using OleDB dumping into a Data table, then bulk sql copy into SQL Server.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 16, 2015 12:07 PM

All replies

  • User-718146471 posted

    alright, I think I may have figured out what is causing the duplication. If an excel file is the newer format of XLSX (2007/2010), OleDB will duplicate the records.  If you run it through the older 2003 xls format, the duplication is not occurring according to my testing.  I am running really large files through the process just to see if that is the case or not.  Stay tuned!

    Thursday, February 19, 2015 3:32 PM
  • User269602965 posted

    Wow.. I load XLSX often with OLEDB and not found this to be problem... but maybe I am not looking hard enough.  Anxious to hear more details.

    Thursday, February 19, 2015 7:32 PM
  • User-718146471 posted

    What I noticed is when I have more than 10 records, I end up with 20.  And the crazy thing is it does not happen every single time.  However with the 2003 format it does not do that.  I suspect it may have something to do with the Open XML format of the newer files.  If you want to see something really interesting with XLSX format, rename the file to .zip and open it.  It has an actual zip file structure which is really neat.

    Friday, February 20, 2015 7:02 AM
  • User-718146471 posted

    I have confirmed the behavior is reproducible as a bug, so I have submitted a bug report to Microsoft's Office team.  This bug seems to occur when using Microsoft Access Database Engine 2010.  Stay tuned.

    Tuesday, February 24, 2015 9:32 AM
  • User-718146471 posted

    This issue is resolved.  By using OleDB dumping into a Data table, then bulk sql copy into SQL Server.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 16, 2015 12:07 PM