Write range overrides and gives the latest file processed data. how to get rid of this overridden thing?

#1

i have multiple files to process.
each file is read, data is retrieved and finally loaded in the application.

I have created a data table to track the status. Say after each file is processed and an order gets created, the order number is stored in the data table. Other info like No of lines loaded, etc. Each record is created after a file is processed and loaded into the application.

I am doing Write range to create excel from that DT.

My requirement, I want to have all the orders created, say 20 files will create 20 orders. So i want the DT to have all 20 records.

Currently write range overrides and gives the latest file processed data.

#2

not clear what exactly is your problem ? share the workflow too

#3

Write Range activity overrides existing content in the excel sheet by default.
You can either write your DataTable using Write Range activity after all 20 files are processed.
Or use Append Range activity to add DT to the existing sheet.

#4

the file is also created on the fly. It is nor pre created.
Since I am creating this as log file… So it has to be created while writing/append range

#5

Excel application scope and Append range activities create a new workbook / sheet if the Workbook path / Sheet Name don’t exist. So this shouldn’t be a problem.

#6

I am doing this:

image
image.png898x329 16.5 KB

But the problem is I am getting the last processed data along with others:

The required would be just File number 1 and 2,
But if u see the latest file processed that is file num 2 is at the 1st record and then
all other records are appended.

I want Write range should just write the headers, and append to add all records.
So in write i have specified the range as A1: F1

#7

Check the content of the LogTable before writing or appending. I guess something is going wrong there.
Also, in your case, each and every time these two steps are executed, Write Range replaces the entire content of the sheet with content of LogTable. Then Append Range once again ‘adds’ the contents of LogTable into the existing sheet. I guess that is not what you are intending to do.

You can use Write Range before starting to process any of the files, to write the headers. Range can be mentioned as just A1 where the activity would start writing the table from.

Append Range you need to use it after every file is processed.

#8

But in Write Range i am specifying the range : A1: F1, which i expect it to be just the headers

#9

Write and Append is used inside the loop after the file is processed

#10

In Write Range the Starting Cell is something is to specify where you want to start writing the table from.
From that starting cell, it writes the entire datatable.

In Append Range the starting cell is based on the last used row in the chosen excel sheet. From the starting cell, the entire datatable is written into the sheet.

So use your write range and append range as I suggested in the post above to achieve your intended results.

#11

Thanks for the reply.But let me re explain my issue:
This is what I have been doing:
Loop each file:

  1. Process 1st file

  2. Adds row to the DataTable

  3. Write Range: Creates an excel file and writes the data from DT to an excel : specified range is A1: F1 (I want only headers)

  4. Appends data from DT to the excel.

  5. Come out of loop

  6. Process 2nd File

  7. Adds row to the DataTable

  8. Write the data from DT to an excel : specified range is A1: F1 (I want only headers)

  9. Appends data from DT to the excel.

  10. Comes out of loop
    and so on

Issue:

Row 2: Write Range is writing the last processed data
Row 3 to Row 7 is the Append range, adding records for each file.

I require data From Row 3 to 7
Row 2 is redundant. How do i get rid of that.

It means the range specified in write range does not work. Though i have specified A1: F1, still it keeps adding records from data table. So it also prints that last processed data.

I hope i could explain my issue

#12

Are you using two different tables - one with headers and one where you add the rows?
If not and you are trying to write only the headers of the main data table, this is not going to work. YOu cannot just write the headers of a data table. You would need to have a separate data table and have the headers as first row and write the rrange without headers. Also you don’t need to write the headers everytime you process the file, once is enough.
Does your table have allways just one row with the last file or are you adding to the data table?

#13

I have trouble understanding your issue so I created a mock process, where you can see what can you do to make it work.Main.xaml (27.2 KB)
Let me know if it is clear now.

#14

Thanks a ton. It was exactly what i was looking for. It resolved my long open issue.
Thanks a lot Barbora !!!

#15

I’m glad it helped. Just out of curiosity - what was the issue, what were you doing wrong?

#16

I was doing this one after the other :

  1. Write Range:
  2. Append range.

Due to which I was getting a redundant record as write was writing the latest processed record and append used to write all the processed record. Thus 1 record that is the last one used to get printed twice in the file.

I was in the notion that if i specify a range in the write range, it apperas only that, thus I gave the range of A1 to F1, which are only headers. But write range writes all records starting from the range given.

But using ur reference, i took the decision flow of : if it is the 1st file, write range else append. and exactly this is what my requirement is.

A heartfelt thanks to you. :slight_smile:

#17

Oh I see now. I’m happy it works for you now. Good luck with your project!

#18

Thanks a lot and Best wishes to you too!

#19