How to Delete (or) Remove Excel row using OLEDB.

[Anonymous]

Hi, I have one Excel. I am using "OLEDB" for Excel operations either insert,update and select but "OLEDB" does not support "DELETE" command . I need to delete few rows in excel depending upon "Policy Number" in excel sheet Policy number column is number type. How to delete excel row depending upon Policy number..? When i try to delete data I am getting below error message"Internal : Could not execute code stage because exception thrown by code stage: Deleting data in a linked table is not supported by this ISAM." Thanks

[Denis__Dennehy]

I did a google search of your query and the findings suggest it is not possible to delete rows, you would need to reorchestrate your solution to instead select all the rows you want to keep and write them into a second worksheet.

[Anonymous]

That is not business requirement.It is combination of different request types. There is any alternate way to delete rows..?

[RajithaJembula]

HI Srikanth, As per my requirement I have to read the data from excel to BP then should perform some filtering in BP for that respective data. Once the filtering is done then I want to write my filtered collection data to excel sheet. I tried through MS Excel VBO but it is taking lot time and writing record by record. Here can you please guide me how to insert the collection data to excel through OLEDB to make it fast.

[ShreyansNahar1]

Hi, Can anyone let me know how to insert data from 1 excel sheet to another sheet within the same excel using OLEDB Insert command?

[JonathanHolstin]

Hi Shreyans Nahar,
I was wondering if you ever found a solution to the insert command for OLEDB.

Thanks!
Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

[JonathanHolstin]

Hi Rajitha,
Did you ever find a solution to your issue?  We are currently working through this same situation and not finding any good solutions.

Thanks,

Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

[JonathanHolstin]

Hi Denis,
Is there a way to do this without first dumping the collection into BP. The drop back into excel takes forever.

We have also tried to use the same sql statement that is done to gather the collection:
SQL:  

"SELECT DISTINCT * FROM [Sheet1$]"

And use this as an execute statement in OLEDB but this does not seem to save or update the file.  I'm wondering how you actually commit this execute statement.

Thanks,
Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------