Read Range Excel Issue

Hi Guys,

Please help me, I need to read excel file using read range only the cells contains some values. I’m using excel read range and provided range as “” . But it still reads all the cells that even contains blank values.

Typically you designate the range as the cell to start in ie “A1”

It will read empty cells and that’s fine. You deal with that in your code by skipping or filtering out the blank cells from the datatable.

Hi @Salman_Faries

It will read if there is any null rows in the Excel file. But it will not read all the rows which are empty.

Hope it helps!!

@Salman_Faries

Check if there are any hidden values or formulas are present.

@Salman_Faries

Can you send the screenshot of the sample excel

Regards


It has only 171 rows. But it was reading 17100 like that no. of rows.

No, It’s reading empty rows as well

I need to write the whole read data table into another excel without filtering it. Because filtering may consume time. Is it possible to read only non-empty rows and write that directly?

Filtering is very fast. Use the Filter Data Table activity.

Hi @Salman_Faries

Try this

filteredData = dt.AsEnumerable().Where(Function(row) Not row.ItemArray.All(Function(item) item Is DBNull.Value OrElse String.IsNullOrWhiteSpace(item.ToString()))).CopyToDataTable()

I hope it works!!

@Salman_Faries

Try this method:
Get the last cell position and use write range to excel.

I didn’t realize this is your issue. The Excel file has empty rows at the end. I would look into fixing this on the supply end, where the Excel file is generated. If you cannot then I would try the Find First/Last Data Row activity to find where the data ends, then use that “Save last row number as” value to create the range to pass to your Read Range activity.