How to add multiple data in one go to a column in Excel

Help Something Else
, , ,
1

Hi All,

I was wondering, how can I add multiple data to a column in excel in one go, without using a loop.

Lets say, I have an excel file with 2 columns Area and Dispatcher, the Area column already has data, but I need to add the value Dispacher1 or Dispacher1 to the Dispatcher column, how can I do this at once without a loop

image
image387×599 35.3 KB

2

If same value needs to be updated in all rows use Dt.AsEnumerable.ToList.ForEach(Sub(row) row(“Dispatcher”)= “Val”)

3

Hi @Sidney_Vogel ,

Normally for Update operations, we do loop through the Datatable Data and perform the necessary update.

We could also use a Linq for this but we would need to know whether the For Each is really affecting the performance.

Also, If a Same value is to be added to all the rows then we could also try adding the DataColumn with a Default Value. However, first we would need to delete the Data Column first, in your case Dispatcher column.

image
image1075×362 18.3 KB

Let us know what is the exact operation you would want to perform.

1 Like
4

Thank you @supermanPunch this helps. I appreciate the help.

5

Just use write range and rewrite the entire sheet.

6

Hi @postwick How would you do it?

7

Hi @Lak_Ui I am trying to also use this expression

Shipment.AsEnumerable.ToList.ForEach(Sub(row) If(AcebeyDispacher >0, row("Dispacher") = "Vit", row("Dispacher") = "Bet" ))

however I get the following error message:
Compiler error(s) encountered processing expression

“Shipment.AsEnumerable.ToList.ForEach(Sub(row) If(AcebeyDispacher >0, row(“Dispacher”) = “ACEBEY”, row(“Dispacher”) = “Crane” ))”. Expected ‘)’

I think I have all the closing parenthesis added here.

Also, If I use Dt.AsEnumerable.ToList.ForEach(Sub(row) row(“Dispatcher”)= “Val”) in an assign method I get an error stating:

Expression does not produce a value, in which activity should I use this expression than if not in an assign activity?

Thank you for your help in advance

1 Like
8

Hi @Sidney_Vogel
I think this is the most eficient way to do it (without looping rows):
image

Just don’t forget the single quotes around your string.
Let me know if that works for you :slight_smile:

9

I said how I would do it. Use Write Range and rewrite the entire sheet.

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.