Read excel range of columns

RPA Discussions
,
1

i’m trying to read an excel sheet columns upto certain range of columns. some times the file comes with columns which may be a duplicate column name of my reading range. which is resulting into duplicate column error if leave range range as empty in the activity.
if i remove option has header then its difficult to work with the file because i have to go by the column names.
if i give read range as A:X its not reading records but when i give as A1:X10000 then only its working.
since i wont be able to know the record count how do i generalize to read range for particular group of columns with out specifying as A1:X10000 to read till 10K records but any number of records?
thanks for your inputs

2

@gsunilusa

Try below steps

  1. Specify the “Range” property as “” (Empty String) → This will read entire excel
    sheet.

  2. Uncheck “AddHeaders” property → By doing this you get Default Column
    Names in excel like Column0, Column1,…ColumnN.

  3. Now you can go with Column Names like Column0, Column1 … and process
    with group of Columns as per your need.

3

thanks karuna for the reply
but as i mentioned i can’t go without column names and need to have column headers as the columns position may vary and my logic will not be consistent and i need to filter columns from entire sheet and take only few.
and my solution is to read any number of records between certain column range and need to know how to do that

4

In case you are able to read in DT, follow below

You can use “” in read range to read all the records to a DT,

If you know the column name that is getting duplicated, you can get the column index of the duplicate column.

DataTable.Columns.IndexOf(“ColumnName”).ToString

Then you can remove the column using ‘remove column’ activity. Now you will have distinct columns.

in case DT is not getting read, read the excel to a DT using read range activity. Mark ‘Add headers’ unchecked.

Now remove the duplicate column from the DT using columnIndex and using remove column activity.

Thats it.

5

thanks sumit.
Unfortunately i will not be able to know the duplicate as it could be any column that can be a duplicate. so i want to keep range to particular columns (for ex A to X or A to AC)and read any number of records and require headers

6

for now i found a way is to read the excel without header store to a data table variable dt_datatable
and again read the excel by taking column range for example “A1:AF”+dt_datatable.RowCount.ToString

1 Like
7

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