OPENROWSET error if Excel column has more than 8224 characters

Question

I used OPENROWSET to import Excel file. It worked if the column <= 8224 characters. My code is as below:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=E:\Test\TestImport3.xls;', 
'Select * From [Sheet1$]')

This test file has only one column with 8224 characters, and it run without any error. But after I add 1 more character, it raised the error as below:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "External table is not in the expected format.". Msg 7303, Level 16, State 1, Line 3 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Could anyone give me some advice? Thank and sorry for my bad English

Answer 1

I have found the root cause in this link: https://support.microsoft.com/en-us/help/2836058/access-errors-during-import-export-to-excel-xls

There are some solutions to resolve this issue:

1. Use the *.xlsx format instead

2. Restrict any memo/long text fields to a length less than 8224 bytes

3. When exporting, delete the existing .xls file before performing the export using the same file name

4. Open the .xls file in Excel before performing the import/export from Access