Updating value from Excel to Sql Server

Question

I have an excel having following columns and values(Sample Data)

F_PRODUCT       OLD_F_COMP_ID         F_C_NUMBER       NEW_F_COMP_ID
00231149         017-002-00-2           C_4457          017-002-01-X
ADRENALINE SPRAY 017-002-00-2           C_4457          017-002-01-X

My Table Structure(Testupdate)

Create Table Testupdate(ID INT,F_PRODUCT NVARCHAR(50),F_C_NUMBER NVARCHAR(40),F_COMP_ID NVARCHAR(100))

Sample Values

F_PRODUCT        F_C_NUMBER     F_COMP_ID
00231149           C_4457       017-002-00-2
ADRENALINE SPRAY   C_4457       017-002-00-2

I want to update the F_COMP_ID column in TestUpdate table with NEW_F_COMP_ID in excel.

Example

update TestUpdate set F_COMP_ID=excel.NEW_F_COMP_ID where TestUpdate.F_COMP_ID=excel.OLD_F_COMP_ID and
TestUpdate.F_C_NUMBER=excel.F_C_NUMBER

I tried below command

UPDATE TestUpdate
SET TestUpdate.F_COMP_ID= ExcelTable.NEW_F_COMP_ID
    FROM TestUpdate
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
         'Excel 8.0;Database=D:\testdata.xls;',
         'SELECT F_PRODUCT,OLD_F_COMP_ID, F_C_NUMBER,NEW_F_COMP_ID
          FROM [Sheet1$]') AS ExcelTable
ON TestUpdate.F_PRODUCT = ExcelTable.F_PRODUCT
WHERE (TestUpdate.F_C_NUMBER = ExcelTable.F_C_NUMBER
  AND TestUpdate.F_COMP_ID = testexcel.OLD_F_COMP_ID
)

eXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

when I tried to execute the update statement I got a below error. I am using LibreOffice Calc for excel.how to fix it OR any other way to update this. Because excel having more than 5000 records and it is difficult to do it manually.

Error:

Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Answer 1

In case you can convert the excel to csv/txt, then you can simply get everything by bulk insert.

DECLARE @temp_log table{ //your csv/txt sturcture}
DECLARE @SQL varchar(300)
SET @SQL = 'BULK INSERT @temp_log FROM ''' + @path + @file_name + ''''
print('SQL:' + @SQL)
EXEC(@SQL)

Then, you can insert it with statement u want. See if this can work or not first.