How to check if excel sheet is empty with no header and no data with header

I need to check if excel sheet has data or no data, i am using below

dt.Rows.Count>1 or dt.Rows.Count<0
but in both cases it is surely giving output number for the sheet where there is data …( i am printing in msg box- dt.Rows.Count.ToString

But somehow for empty excel sheet it’s giving object reference error .

Please help me with condition which will check both the things

Shee1 is fully empty without headers and rows 0 but headers are there …
if above is true then I need to give business exception.

TIA

Hi @8b6861dc5e0c9f7008548ca66

Surround the activities with the Try Catch. In the Try give Read Range Workbook. In the Catch give, BusinessRuleException and give the required exception.

Hope it helps!!
Regards,

Hi Thanks for reply @Parvathy

Workbook has many sheets, in fact sheetname is as a transaction from queue.
Hence need to check if specific sheet has data or no data
I am using rows.count but if there r no headers n full blank sheet with valid sheet name then it’s giving obj ref error

Hi,

Use “dt is Nothing” for checking null and dt.Rows.count = 0 is to check empty.

Hello @8b6861dc5e0c9f7008548ca66

Assign Activity:
Left side: isEmpty
Right side: dt IsNot Nothing AndAlso dt.Rows.Count > 0 AndAlso dt.Rows(0).ItemArray.Any(Function(rowValue) Not String.IsNullOrEmpty(rowValue.ToString()))

If isEmpty Then
The DataTable has data (excluding headers)
Your code for handling non-empty DataTable goes here
Else
The DataTable is either null, empty, or contains only headers
Your code for handling empty DataTable goes here
End

Thanks & Cheers!!!

1 Like

HI Nitin,

if excel sheet is completely blank, read range activity itself will throw exception,
better you keep read range activity in Try catch, if flow goes to try catch, means sheet does not have data(not even headers),

to check if there is data except header row, use dt.rows.count>0

hope it is helpful

you can check in catch block if the exception is Object reference not set to an instance of an object.

Hi @8b6861dc5e0c9f7008548ca66 ,

If u have not initialized the dt
dt= new system.data.datatable while creating then u need to chk
if( dt Is Nothing) then Datatable empty
Else if dt.rows.count>0 then not empty

—If you hv initialised then for empty sheet—
Check dt.rows.count = 0

Regards,
Geetishree Rao

@Kartheek_Battu
this working fine for

  1. if there is data in sheet
  2. if there is no data, and only headers

But failing if , there is complete blank sheet, obj ref error.

hi @Parvathy , I can see SE but how do I capture it as a BE and how to go for next transaction , I need to try once. I will let u know.
TIA

Hi @8b6861dc5e0c9f7008548ca66

Unser Exception You can Click Browse for types and type BusinessRuleException.

Regards

Hi @8b6861dc5e0c9f7008548ca66

You can use the below modern activity to chk if file empty:



1 Like

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