Copy auto Incrementing Data

[Pranay_Kumar_Bose]

Hello All,
I have multiple tables in an excel file…
I want to select a particular table from the excel file which I can do using read range, but the problem is every week data is added by one or more rows. How do I copy incrementing data’s like that…
For reference: I am receiving an excel file with multiple tables from one end and copying a particular table using read range and sending it to another end with a particular email address.

I cannot manually change the range each week.

[Anil_G (Anil Gorthi)]

@Pranay_Kumar_Bose

Ideally in read range you only need to give the start of the range any appended data will be automatically read

cheers

[Pranay_Kumar_Bose]

@Anil_G
Actually there are multiple tables in that excel file… Which has dynamic start and end… each table has to be separated and send it to respective emails, either by putting into a new excel file and attaching them or by attaching into the mail body.

[Pranay_Kumar_Bose]

Table size is around 7 rows *7 columns… like that there are 5…
Sorry, as I cannot attach pictures cuz the organization laptop is not allowing me to.

[Brian_Mathew_Maben]

Hey, do the tables have an empty row between them. If they do, then you can try making use of it as a condition to isolate the tables while reading it from the Datatable.
Then you wouldn’t have to use the range option.

[Pranay_Kumar_Bose]

Ya empty rows are there to separate the tables… but if data is added (usually a row gets added every week), then the first table starting range might be fixed but rest of the tables starting and ending range, both gets changed. Now what condition to put in ‘IF’. ?

[Yazan_Otaibi]

hi @Pranay_Kumar_Bose
create an asset ex asst=0
as @Brian_Mathew_Maben says, every time you read the table remove empty rows,
then dt.rowcount lets say 10.
then get the value in asst if = 0 then asst =10
else update a value in asst to be 10+ asst
now you have the current dt count and you have the last update one
then you will know the div of the tow numbers is the count of your new rows.
and if the div number is 4 you can get the last 4 rows

[Brian_Mathew_Maben]

@Pranay_Kumar_Bose I have made a test workflow using a sample excel

Main.xaml (16.5 KB)

Input.xlsx (9.6 KB)

1.In the ‘For each Row’ use an ‘If’ activity to check if the current row is Null or not ( Not String.IsNullOrWhiteSpace(CurrentRow(0).ToString) ).
a. If a value is present then convert the datarow to an array(CurrentRow.ItemArray) and add it to another datatable(eg: dt_output. Build this datatable with the necessary columns).
b. For the ‘Else’ condition(when datarow has Null elements) you can Write the above datatable(dt_output) to an excel file and perform the mail operations and then clear the datatable(Invoke Method).
c. Also make sure the ‘Else’ condtion has an ‘If’ activity to check if the datatable(dt_output) has rows present in it before performing any operations.
d. In the above workflow after the for each activity I have added another If condtion to check if dt_output has rows present. In the final iterations the loop is exited and it wont update the excel, therefore this step was necesssary.

Make sure to delete the output excel file after each iteration. Let me know if it works!

[Brian_Mathew_Maben]

@Pranay_Kumar_Bose Is the test solution working to solve your issue. If any issues let me know!

[Pranay_Kumar_Bose]

@Brian_Mathew_Maben
Hey, it’s working,
But my requirement is not getting fulfilled.
Input_1.xlsx (14.8 KB)
Sequence_1.xaml (19.9 KB)

I need the tables in diffrent sheets… Modifying your code a little bit am able to get last two tables in two different sheets but not getting the rest of the tables…
Need help on that…

Thanks

[Brian_Mathew_Maben]

Hey @Pranay_Kumar_Bose

Sequence_1.xaml (20.3 KB)
Output.xlsx (10.5 KB)

I have updated the workflow to suit your requirements. I added a count variable in the workflow in order to update the Sheet name. So after every Write operation, the sheet name will change. I have also attached the output file for reference. Let me know if this works for you!

[Brian_Mathew_Maben]

Hey @Pranay_Kumar_Bose Any updates?

[Brian_Mathew_Maben]

@Pranay_Kumar_Bose You can try unchecking the ‘add header’ option in the write range workbook activity

[Pranay_Kumar_Bose]

Ya its working @Brian_Mathew_Maben , further as I was getting 2 headers, so just needed to uncheck “add headers”.
Gratefull for you help and support…
Thanks