I have an excel file with multiple duplicate rows with no unique identifer. I am connecting that excel file with oledb and want to update only one single row out of many matching rows. How to do that
Check below for your reference
This is from a column, but you have to give your columns so that it will get unqiue values row only
Hope this may help you
Hello @Srini84 ,
Thank you for the quick reply…
However my case 2 row is exactly duplicate without any uniqe column value… and i want to update only one row using oledb update query
Check as below
I have given as below
dt_Test.DefaultView.ToTable(True, "Column1", "Column2")
Hope this helps you
I have the data in the excel file not in datatable and i am connecting that excel with oledb connection and run oledb update query
You can use Run Query and write Select * from your table, those will get all your data into Datatable then you can use update query after placing the filter as above
Hope this may help you
Correct but the only limitation i have is i need to update the excel file using oledb connector only
I believe this is the only way to do manipulation and then update query
You use Run Query and then Bulk update actiivty
Once you use Oledb for Excel then it becomes as Excel as Database, so you can use Select query and then update the same after manipulation
Select is not an issue, i am able to get one row using TOP keyword but update quary is not working …can you help me with update query which will update one row of excel file
Use Run Command activity and write as below
UPDATE [Sheet1$] SET Column3 = ‘NewValue3’ WHERE Column1 = ‘Value1’ AND Column2 = ‘Value2’
and you use above as per your columns
If i run the following update command all the dupliacte row will be updated at once(as i have no unique identifier) which i dont want. I want to update only first row for the condition
WHERE Column1 = ‘Value1’ AND Column2 = ‘Value2’
You can perform an update on a subselect using TOP and your condition in it:
UPDATE (SELECT TOP 1 * FROM [Sheet1$] WHERE Column1 = ‘Value1’ AND Column2 = ‘Value2’) Set Column3=‘NewValue’
The result then looks like this:
Thank you .
This is exactly what I was looking.
It is working .
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.