Export data from excel to sql

Question

Export data from excel to sql

Navigation

#1 by (2 votes)

0

How can it be done to export an excel table to a SQL Server table and also, whenever someone adds, edits or deletes a value from the excel table, that same value is removed from the sql table?

I have already been able to export the values of a table to sql but if I add, edit or delete some excel nothing happens in the sql table, I also tried to do it with openrowset but I was throwing this error:

can not create an instance of ole db provider "microsoft.ace.oledb.16.0" for linked server

And I was doing something like this:

    INSERT INTO User (turno,num_empleado,num_asociado,apellido1,apellido2,nombre,cedula,posicion,jefe,usuario)
    SELECT --de los campos de excel
a.turno,a.num_empleado,a.num_asociado,a.apellido1,a.apellido2,a.nombre,a.cedula,a.posicion,a.jefe,a.usuario
    FROM 
    OPENROWSET ('Microsoft.ACE.OLEDB.16.0','Excel 16.0;Database=C:\Users\MiUsuario\Desktop\ExcelImport.xls','SELECT * FROM [Libro$]')AS a;

Then how could I do it to make it work for me?

sql-server excel

asked by sullivan96 15.02.2018 в 17:54

source

1 answer

Add actions to buttons Concatenate 2 repeaters of strings

score 2 · Accepted Answer

How about good afternoon, I'll do it with this:

SELECT * 
FROM OPENROWSET 
('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=C:\CNH\CNH_DGM_VHP.xlsx;HDR=YES', 
'select * from [CNH_DGM_VHP_02$]')

important, that if your system is 64 bits, the office must be in 64 bits and install the AccessDatabaseEngine_X64

You do not have to have any problems, now if you think you have a problem doing it with code you can do a DTS or an ETL.

you can also do a linked server:

EXEC sp_addlinkedserver
@server=OrderValues_EXCEL64,
@srvproduct='Whatever',
@provider= 'Microsoft.ACE.OLEDB.12.0',
@datasrc= 'c:\CNH\CasosdeUso.xlsx',
@provstr= 'Excel 12.0;'