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

Javier Gilvonio 1 Reputation point

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]
select [Agencia] as Agencia,
[Fecha] as Fecha,
[Apellido Beneficiario] as Apellido_Beneficiario ,
[Nombre Beneficiario] as Nombre_Beneficiario,
[Remitente] as Remitente,
[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',
'Select * from [Maestro OutBound Soles$A8:M]')

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,589 questions
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.
459 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,561 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,456 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 93,051 Reputation points

    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 24,011 Reputation points

    @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