CODEWITHSUNDEEP
sql-server-2000openrowset
Kings
Kings

Reputation: 1591

Importing .XLSX excel file using OPENROWSET into SQL 2000 table

Hi I can successfully import an .XLS excel file data into a table of SQL server 2000, like this:- 

SELECT * INTO tblCustReports FROM 
    OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
       'Excel 8.0;Database=C:\Customer Reports.xls', 
       'SELECT * FROM [Sheet1$]')

But the same I'm unable to do if the excel file is in .XLSX format.

Any suggestion?

For my question, I tried this:-

SELECT * INTO tblCustReports FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml;HDR=YES;Database=C:\Customer Reports.xlsx',
       'SELECT * FROM [sheet1$]')

But getting this error:-

Server: Msg 7403, Level 16, State 1, Line 28

Could not locate registry entry for OLE DB provider 'Microsoft.ACE.OLEDB.12.0'. OLE DB error trace [Non-interface error: Provider not registered.].

Thanks

Upvotes: 0

Views: 2296

Answers (1)

Leptonator
Leptonator

Reputation: 3499

Look at the difference between your providers.. Also, look at the difference between your versions of Excel..

SQL 2000 is quite old and would only support older versions of Excel.

if you want to use a newer version of Excel, you are going to have to save as in a legacy file format. Save the file as xls and not xlsx.. The main difference is that the version of office that saves in xlsx uses compression in the file format.

HTH,

Kent

Upvotes: 1

Related Questions