Reading an Excel file issue

Help Activities
, ,
1

Hi Team,

In input Excel i have to read the columns where i am facing the issue.

Below is the input file
Sample.xlsx (157.7 KB)

each row i need to read from the above excel.
Please can you help me the solution

Thanks in Advance

2

@vinjam_likitha

Try using read range excel activity and give the first cell value and it would read till end

Then we can use for each row in datatable

Cheers

3

But @Anil_G did you see the rows it is added each record in multiple rows.

Still can we extract it?

Thanks

4

Hello @vinjam_likitha
You file have duplicate column name. it will caue the error while you reading the data from A4
Or else you can use read range like “A4:J20” and use filter data table to remove the empty values in 1st column
Refer to this Xaml file you may get some idea.
Forum_ExcelRead.xaml (8.8 KB)

image
image1110×565 15.2 KB

5

I am facing issue in opening the xaml that u have shared

Same columns is the one issue, but i cannot remove the duplicates columns because those are required columns

The other issue is rows ,can i read the rows and extract values because each record is present in multiple lines

ex–first record in 5 lines(2,3,4,5,6) in excel

Thanks in Advance

6

@vinjam_likitha

For removing the empty line we need to use the Filter data table. Refer to this file.
Forum_Excel.zip (204.2 KB)

With duplicate you cant raed the whole data, then read the data in certain range which dont have duplicate column name or else read range properties of Add headers is diabled
image

7

@vinjam_likitha

You can read it…

So after reading the data …use for each row and check if first column is empty merge it.Check the xaml

And for column names you can use column index as well… I guess the columns are constant so use column index…

image
image724×648 22.1 KB

BlankProcess5 - Copy (10).zip (3.4 KB)

cheers

8

can we write logic where each record complete data will be placed in one row

9

@vinjam_likitha

This is the logic for merging the rows only

In if condition i am checking if request id is empty or not and when empty i am merging and when not i am assuming it is the correct row

Cheers

10

@Anil_G in assign activity you have mentioned column name which column name i should give

Thanks

11

@vinjam_likitha

That is a example of how you can merge rows…so …ideally you have to use each column name or column index…else you can use a for loop on column as well and give this assign inside that for loop so that it also would happen for all column…

As I see only few columns are having data in all the rows…I did not use for loop for looping through all columns as it would append blank data …so You can specify only those columns where you know there is data to be merged into the first row as a single row

Eg:

image
image1358×449 21.6 KB

only the highlighted column has data I guess in all rows

cheers

12

there are 3 to 4 columns also can i apply same assign activity for other columns as well

13

@vinjam_likitha

Yes you can …just change the column index /column name as per your need

Cheers

14

@Anil_G

Can you please test with the xaml that you have shared

I am facing some issue

Below is the input file
Sample.xlsx (130.6 KB)

15

@vinjam_likitha

What issue are you facing?

My xaml is a prototype to give idea it does not reflect the exact columns

If you can let me know what you did and what issue you are facing may be L can help

cheers

16

Sample.xlsx (160.1 KB)

please See the output in sheet 2 when i run the bot

17

@vinjam_likitha

Please try this…I guess the value is in last row not in first when we extract

BlankProcess5 - Copy (11).zip (4.4 KB)

cheers

18

image
image1021×162 5.68 KB

error :disallow implicit conversion from object to integer
@Anil_G

please kindly let me know how to resolve the issue

19

Still i am getting the same output after running the bot

20

@vinjam_likitha

Can you please provide your xaml which you are trying to read…

And also for loop type argument must be changed from object to integer…

Hope this helps

Cheers