1

/* This question is informational. I had to post it as question since I couldn't comment under others' replies to the questions associated with 3 errors below. These errors occurs in SSIS and SQL Server Import and Export wizard. */

  1. The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)
  2. The 'Microsoft.ACE.OLEDB.15.0' provider is not registered on the local machine. (System.Data)
  3. The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

As remedies to these errors, some people have suggested to save Excel file as .xls(Microsoft Excel 97-2003) and error will go away. Well it will go away but at cost of the data loss. Please read the answer below...

pavnis
  • 39
  • 7
  • 2
    If you have an answer to your own question, post it as an answer, not as part of the question. :) – Thom A Jul 19 '19 at 16:04
  • thanks, I just did. I could not comment to other questions so I assumed I may not get chance to comment. That's why I put little note in description saying /* This question is informational. I ha... */ – pavnis Jul 19 '19 at 16:29
  • You will get to comment once you have enough reputation. – cybernetic.nomad Jul 19 '19 at 16:52
  • sometime making things better means breaking working things :) – pavnis Jul 19 '19 at 17:44

1 Answers1

1

If you have more than 65,536 rows on a file, DO NOT SAVE .xlsx (Microsoft 2010/2013/2016) file as .xls (Microsoft Excel 97-2003) for workaround. .xls file can have only 65,536 rows. It means if you have more than 65,536 rows on Excel file and you save it as .xls(Microsoft Excel 97-2003) then there will be data loss without any informational message. This will lead into data loss.

I hope this will help someone in need.

pavnis
  • 39
  • 7
  • 1
    You should mention the columns limit as well. xls has a maximum of 256 columns, if i recall correctly. There's also a lot more differences between 2 than just the number of rows. – Thom A Jul 20 '19 at 15:44
  • Thank you, Yes I agree with you. However this specific question and answer was intended for data professional dealing with those errors above, it will be absurd if someone has 256 fields in a table. Rows, I can see many people exceed that number very easily. – pavnis Jul 22 '19 at 18:09
  • *"it will be absurd if someone has 256 fields in a table"* clearly you've never seen an insurance exposure Bordereau. I've seen some get close to 500 columns in a spread sheet. – Thom A Jul 22 '19 at 19:59
  • Yes, I have seen that too but I think it's poor modeling. There might be some use cases for very wide tables but I haven't came across one yet. – pavnis Mar 24 '21 at 18:21