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

Question

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.

Answer

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.

Answer

@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.

Share via

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

2 answers

Your answer