0
Good morning, I’m trying to read an Excel file . xls by the OLEDB driver used in SQL Server for integrations with Microsoft.ACE.OLEDB.12 files. 0 in SQL Server 2016 Express (64bit). Using command OPENROWSET but I’m getting the famous mistake:
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null).
I have performed several procedures reported in research but unsuccessful!
Procedures:
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Selecting the spreadsheet:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Users\Underline\Documents\Temp\Publicacao.xls', [Publicação $])
I’ve already changed the permissions in the folder to access Network and Local accounts:
I downloaded and installed Microsoft Access Database Engine 2010 Redistributable version x64, but nothing yet. Has anyone experienced this? And please share some of your experience.
Evandro, can you transcribe the first error message, complete? In the image was truncated // The internal name of the sheet is "Publication"? // Note that there is a space in the command; remove it: [Post$]
– José Diz
@Josédiz First line:
Msg 7399, Level 16, State 1, Line 14
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the errorThe name of the sheet is Publication and has a space even, I receive this sheet from an external site and there is this space on the sheet I have to keep.. but I’ve done the test without the space too.– Evandro
Did you authenticate in the instance using the Windows Authentication method? If so, the user is not part of the group
Administrador?– José Diz
@Josédiz Yes Windows Authentication method and the user in the Administrator group.
– Evandro
@Josédiz discovered that the problem is with the file, when below the external site it comes in the format Web Page. When opening the file in Excel it gives the following message
O formato de arquivo e a extensão de 'Publicacao.xls' não correspondem. O arquivo pode estar corrompido ou não ser segura. Não o abra, a menos que confie na fonte. Quer abri-lo mesmo assim?when confirming it opens in protected view mode. It has some way to open direct or convert via code!?– Evandro
I have a similar situation in which I get spreadsheet in HTML format and with the file having the extension
.htm. Rename the extension to.xls, open it in Libreoffice Calc and save in CSV format. From there is the automatic process of importing file with CSV format together with format file. // In your case, if the whole process has to be automatic, you will need to search for html to csv converter.– José Diz
@Josédiz I wanted to automate the whole process, but I see no other alternative I will have to open the file and save as to later select the data from the spreadsheet. :(
– Evandro
Good morning, because you do not use the sql "Bulk Insert", it is native to sql.
– Wellington Araujo