Multiple datatables - writing to excel

Help
1

Hi all,

1.I have a master excel file for which I read the range and store as dt_datatable1.
2.Then I filter on a certain column “age” on ‘is not empty’. I store this as dt_datatable2
3.Then I perform certain actions on dt_datatable2 (filling in some columns).
4.Then I want to write everything back in the master excel file

I’m stuck at the 4th step. I want to write the dt_datatable1+the data of dt_datatable2 with the data that I just had enriched in step 3. So the data in dt_datatable1 needs to be overwritten for the ones that were in scope of my filter for dt_datatable2.

Thanks.

2

Hello @yannip

You can write the new data in another sheet.

1- you do a merge DataTable - Using the Merge Activitie
image

2 - Write the result of your merge output - using the “Write Range Activitie”

3

Hi @dkollyns,

Thank you for your reply. I have tried that, but then the lines of datatable2 get added. So eventually I have the data twice (the original one that was already included in datatable1 + the data I added from datatable2).

1 Like
4

Oh ok, I’m not sure, bu do you want write the data of step 3?

5

Yes, I want to write that data from step 3 @dkollyns.

1 Like
6

Ok, So after step 3 you can use the dt_datatable2 output and write in new sheet using the “Write range activite”.

7

Ah, but not only that data, I also need to keep the data of datatable1 (except for the ones that I used in datatable 2). Sorry for the confusion.

8

@yannip

But you’ll keep, only another sheet.

Your process is this way correct?

1 - Read Master Excel “Sheet1” - store in dt_datatable1
2 - you apply a filter - dt_datatable2 get the data of dt_datatable1 column “age” on ‘is not empty
3 - you perform certain actions on dt_datatable2 (filling in some columns). dt_datatable2 = this “filling in some columns”
4 - You can write the dt_datatable2 in your Master Excel , but “Sheet2”.

9

Datatable2 is not required here.

  • Use For each in datatable one.
  • If condition to check age<>“”
  • Perform your operations using assign row(5)=“New Value”
  • Write the datatable1 back to excel.
10

Only step 4 is not correct. I need to write the dt_datatable1 which I did not perform any actions on + dt_datatable2, in the same order as they already were, in the same excel sheet as I got the info in the first place.

11

@Vivek_Arunagiri, thanks for your reply.
Well, I think I do need the datatable2 because:

So, allow me to tell you the high level process.
I have an excel file which gets new data every day (appended).
For these new rows, I need to check whether the project number already existed in a previous row.

So, this is what I do:

  • I store the complete excel file as datatable1
  • I store the new ones that are appended as datatable2 and get row item for project number (=var_projectnumber)
  • For each row in datatable2 I check if the project number already exists by filtering the datatable1 on the var_projectnumber. If then datatable2.count.tostring >0 then it already exists and for that row I assign the value of a certain column as “project already exists”.
  • after this, I want to put everything back in excel (= datatable1 and datatable 2, in the same order as they already existed)
12

So you’ll write the dt_datatable1 in the same sheet1, and after you’ll write the dt_datatable2 in the same sheet1, but in Write range activitie you set this way Range = “A:” + dt_datatable1.rows.count + 1

image
image912×234 10 KB

Check if it

2 Likes
13

Allright, I managed to do it!
Was not completely how you said but you gave me the right idea!
I did the following:

Assign WritingStartingPoint=dt_datatable1.rows.count-datatable2.rows.count+1
write range: starting cell “a”+writingstartingpoint.

In your example I would have got datatable2 twice underneath each other (as it was already there)

1 Like