Read only filled rows from excel

Help
,
1

I have an excel sheet with formulas from row 1 until row 12500 on columns A-AA and only, let’s say, first 3000 have values (as it takes values from 2 other sheets), how do I get only the filled rows? Read range is crashing as I’m assuming is reading all 12500 rows. Thanks!

2

Hi @Alinutza13,
Normally , When you leave blank (“”) in the Range property of ReadRange Activity, it will read all the data into datatable. After that you can filter the data by the condition.

image

Regards
Balamurugan

3

Thanks, but I want to read only the rows that have values, the rest have only formulas. Read range is crashing when reading the whole sheet because is trying to get everything including cells that are empty, but have formulas.

1 Like
4

Hi @Alinutza13 ,
Below I have attached a sample. It has the formula util Row 25. But it has the data 18 row. The sample will show the row count only 17.

Sample File : ReadOnlyFilledRows.zip (8.8 KB)

Note :slight_smile:
If possible , can you share the source.

Regards
Balamurugan

5

@Alinutza13 You can perform UiAutomation on top of your excel sheet in order to delete the empty rows. Once the empty rows are deleted, you can use read range to read the data from excel.

You can follow below steps to delete empty rows from excel using UiAutomation (desktop recoder/ click, type into etc activities):

  1. Press [F5] in excel using “Send hotkey” activity.
  2. In the resulting Go To dialog box, click on “Special” button.
  3. Click the “Blanks” option(Radio button) and click OK.
    Doing so selects the blank cells (what you might think of as rows) in the selected range. Now you’re ready to delete the selected cells.
  4. On the Home tab, click the Delete dropdown in the Cells group and choose Delete Cells. Excel will display the Delete dialog box, with the Delete Cells Up option selected. Click OK. Or, press [Ctrl]± (using send hotkey activity). Excel will delete the blank cells from the selected data range.
6

Thanks! I wanted to avoid automating excel :smiley: because I can also do this with a macro. I’m trying to see if this can be done without automating excel or macro :smiley: I just need to read the filled lines, the issue is that the excel file is quite big, 30 MB (and increasing) and I’m getting Read Range Faulted.

7

It’s not working as there are no blanks, all the cells have formulas even if the result is “”.

8

@Alinutza13 Is it possible for you to share the excel file here?

9

Unfortunately not as it contains confidential client data. “Read Range Faulted” but I don’t see any reason why:

3
3947×1046 200 KB

I tried also outside the Excel application scope (Read Range from Workbook) and it’s the same.

10

Just found out why it was faulting: 2 columns have the same header (excel file is downloaded from SAP), changed it now.
Thank you all!

1 Like