Segregate data and create two DT from one DT

Hi,

-I have to read an excel and have to segregate data from that excel and make 2 DataTable, I have attached the sample excel and also a screenshot.
-in 1st DT the data will have the A1:B19 and in 2nd DT rest of the data.

  • I have attached the output also in the same attached excel file.

tesdata1.xlsx (13.3 KB)

@indrajit.shah Will the Excel Format be the same always?And Will it Always be two tables that you need to Extract ?

@indrajit.shah,

as @supermanPunch said is your excel format is fixed…?
if yes you can usetwo read range activity by defining the ranges are

  1. A1:B19
    2)A20:H24
1 Like

@supermanPunch

Yes format will be the same but have to add one more column with Column name Account Number and the data for the column will be copied from the same excel, you can see in A1 field and in A2 field the data is there.

@venkat4u
I think of the same you suggested but the range is not fixed at least for the 2nd part as for sample purpose I had added only a few data but in the original file, it will vary from the very large margin.

P.S I am attaching the output in the screenshot and excel also, I had used the same excel file but we copy the data to different excel also.
image


tesdata1.xlsx (13.3 KB)

1 Like

@indrajit.shah

please find the Main.xaml (5.2 KB) file for your query.

  1. First Read the Whole excel Sheet - FullDT
  2. Second read the First DataTable(it is fixed length) - FirstDT (A1:B19)
  3. While reading second table use the full DT Details - “A”+(FirstDT.Rows.Count+2).ToString+":H"+(FullDT.Rows.Count+1).ToString

Cheers,
Venky

2 Likes

its helpful @venkat4u, do you mind sharing the output?

1 Like

you can use the XAML file which i shared.

2 Likes

Check this workflow and the output file, @indrajit.shah
ExcelAutomation_1.xaml (9.6 KB) Output.xlsx (8.8 KB)

2 Likes

Great one @Manish540
In output sheet name Table2 how can i add one more column with column name Account Number and with data from the table one. I had attached a screenshot above also mention the same in sample file.

Its not like that i have to add Account number in 1st column only we can add it in last also.

1 Like

Use add data column activity at the end of workflow.

1 Like

how can i copy string from a DT??
I have below record in a DT and have to copy/print only data in B1

image