Intento Insertar un Excel a una tabla SQL pero tengo problemas con "Microsoft.ACE.OLEDB.12.0"

Javier Gilvonio 1 Reputation point
2021-02-12T22:22:46.98+00:00

Hola Tengo el siguiente problema ,

Cuando intento importar un documento Excel con filas vacías a cierta tabla SQL obtengo el siguiente error .

Msg 7399, Level 16, State 1, Line 284
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 error.
Msg 7303, Level 16, State 1, Line 284
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Este es mi Query ;
INSERT INTO [dbo].[MaestroWU]
(Agencia,Fecha,Apellido_Beneficiario,Nombre_Beneficiario,Remitente,MTCN,Principal,Cargo,IGV,ITF,ToTal_Enviado,Pais_Beneficiario,Ciudad_Beneficiario)
select [Agencia] as Agencia,
[Fecha] as Fecha,
[Apellido Beneficiario] as Apellido_Beneficiario ,
[Nombre Beneficiario] as Nombre_Beneficiario,
[Remitente] as Remitente,
[MTCN] as MTCN,
[Principal] as Principal,
[Cargo] as Cargo,
[I#G#V#] as IGV,
[I#T#F#] as ITF,
[Total Enviado] as Total_Enviado,
[País Beneficiario] as Pais_Beneficiario,
[Ciudad Beneficiario] as Ciudad_Beneficiario
from OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=F:\PROCESOSTCC\CARGA\AGENCIA DE COBROS KASNET S.A.C..xlsx;HDR=YES;IMEX=1',
'Select * from [Maestro OutBound Soles$A8:M]')
GO

Cabe señalar que si le quito las 7 filas iniciales si importa con normalidad pero para este caso en particular debo tomar desde la fila 8 hasta el final.

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
568 questions
Microsoft 365 and Office | Excel | For business | Windows
Developer technologies | Transact-SQL
SQL Server | Other
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2021-02-13T10:44:05.797+00:00

    La lengua de estos forum es inglese. Non se español bastante bueno por ecrir un repuesta en español.

    (The language of this forum is English. My Spanish is not good enough for replying in Spanish.)

    You should look for a different way to import these files. If you google around, you will find no end of people having problem with the ACE provider. It it is intended for interactive use, and not for being called from a service. I guess what happens is that Excel remains open after the seventh file.

    There is the import/export wizard in SSMS for the simple stuff, and you can use SSIS when you need more automation. Other options include writing a program that reads the Excel file and inserts the data. Doing it from SQL Server is difficult. It works sometimes - but when it doesn't your kind of hosed, because it is difficult to troubleshoot.


  2. Yitzhak Khabinsky 26,586 Reputation points
    2021-02-14T00:40:01.99+00:00

    @Javier Gilvonio ,

    It seems that each Excel file has 7 rows header that needs to be skipped.
    The failing Excel file has 8 rows header.
    So the Excel range needs to be adjusted dynamically.

    Also, you need to make sure that the *.xslx file is NOT open in Excel.
    Your error indicates that the Excel file in question is opened in Excel.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.