How to read a worksheet with a table on top into a datatable with it's headers?

Help Studio
1

Hello Good people,

I need some guidance here.
I’ve an excel worksheet as shown below:

image
image993×475 15.4 KB

My task is to copy that data to a different worksheet but the challenge am having is:

  • Am not able read the headers of the data so am unable to manipulate the data the way I want.

  • When I use the read and write activities I don’t check the Add Headers option which makes more difficult to manipulate the datatable.

  • And also the format isn’t preserved.

The image below is the second workbook in which am supposed to paste the data too and that’s how it comes out when done manually.

image
image1199×540 45 KB

If there is a way I could first delete the table at the top then read the data into a datatable it would be very helpful without using UIAutomation.

Kindly advise and thank you in advance.

Regards,
Kakooza Allan Klaus

2

First of all read the whole rows and give it into a loop then check if Currentrow(0).contains(“SOL ID”) , if yes assign the index to a variable , then break the loop and use another read range with Add headders in which u can specify the range as “A+index”

1 Like
3

Can u share the sample file so that i can share you the Xaml file

1 Like
4

I’ve shared the sample file with you @muhamed_fasil
Thanks

5

I guess you might have shared the file to the wrong person?

1 Like
6

Hi @Kakooza-Allan-Klaus - Can you share the excel file

1 Like
7

@Kakooza-Allan-Klaus

  • When reading the data using Read Range you can actually read the range of data that you want.
  • Ignoring top table, assume the actual data/second table starts with A5. Then, you can specify this range in the Read Range properties as A5:H+Lastrowcount (Lastrowcount you can get using datatable rows count)
  • This way you can avoid top table writing in another spreadsheet
  • Also, to get the same format you can toggle Preserve Format
1 Like
8

Thanks @ushu this solution seems to be very feasible.
Once I get to my machine am going to share the file.

9

Apologies @muhamed_fasil thought I had shared it.

10

Input.xls (53 KB)

As requested that’s the input file.
@ushu @muhamed_fasil

11

Please find the xaml file
it will detect the header row and will read from that row with headders

ExtractTable.zip (15.0 KB)

1 Like
12

Thanks a lot @muhamed_fasil.
It comes with extract columns which I deleted using the delete column activity.
Thanks once again.

1 Like
13

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