-1

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)".

Neeraj Sewani
  • 3,952
  • 6
  • 38
  • 55
Ram
  • 727
  • 2
  • 16
  • 33
  • Will you consider to convert the excel file to csv or txt? Then you can bypass using Office library, much less painful. – MT-FreeHK May 29 '18 at 06:13
  • ok.if i convert the excel file into csv and txt file how can i update the table?.Give the script for above requirement. – Ram May 29 '18 at 07:45

1 Answers1

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.

MT-FreeHK
  • 2,462
  • 1
  • 13
  • 29
  • @Ram, so you mean, even when you use csv/txt instead of xls, there is still error? May I know what error is it? – MT-FreeHK May 29 '18 at 13:57
  • i added the these lines.WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', FIRSTROW = 2, TABLOCK );' in third line. – Ram May 30 '18 at 13:16
  • No errors came.Thanks.But when we go for excel,is it required to do more steps? – Ram May 31 '18 at 04:44
  • @Ram For my own experience, you need to first download Microsoft OLE DB Provider for SQL Server. Go through the setting, connect to your sql server. Lastly, there are slightly different datatypes for OLEDB and general sql datatype. That's why I change everything to csv/txt instead of xls. – MT-FreeHK May 31 '18 at 05:33