(Excel) How to count the total records of excel file

I want to count the total number of records from excel sheet.How can i do it?

If by the total number of records, you mean the total number of rows, then you could use the action:

Excel.GetFirstFreeColumnRow Instance: FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow

@nutan

Once you read the data from excel by using following code

Excel.ReadCells Instance: StartColumn: StartRow: EndColumn: EndRow: ReadAsText:False FirstLineIsHeader:False Value=> Value

you will get the row count by using following code

Console.Write Message: Value.rows.count

I already used this but if i have a blank column in any row then it wil be wrong.

@nutan hey,

Admin Note: Please follow the forum’s rules and only post in the appropriate category, using a proper title (inlude the module name at the beginning)

That is why you must use the action:

Excel.GetFirstFreeColumnRow Instance:(YourInstance) \
FirstFreeColumn=> FirstFreeColumn \
FirstFreeRow=> FirstFreeRow

The combination ensures that you will get the whole range of data.
You get the outermost combination.
Then you can use the following command:

Excel.ReadCells Instance:(Your instance) \
StartColumn:0 \
StartRow:0 \
EndColumn:FirstFreeColumn-1 \
EndRow:FirstFreeRow-1 \
Value=> Value

And finally:

Console.Write Message: Value.rows.count

Best regards,
J.

but what value i put in EndRow because i don’t know the value of end row

@nutan Kindly refer the previous thread of @jpap . With command GetFirstFreeColumnRow you will have the end of the column and row .

@nutan, I know this is the kind of thing that can drive a person nuts due to the difference between zero-based indexing and one-based indexing, I’ve been through it often enough myself. :grinning:

This could be “drawsplaining”, but I hope it helps.

Consider this spreadsheet:

spreadsheet

and this code:

code

Here’s the code itself for your mods:

Excel.LaunchAndOpen         Path:  "C:\Users\nutan\Documents\Robin\forum\BlankTest.xlsx" \
                            Visible:True \
                            Password:'' \
                            ReadOnly:False \
                            LoadAddInsAndMacros:False \
                            Instance=> ExcelInstance

wait 1


Excel.GetFirstFreeColumnRow Instance:  ExcelInstance \
                            FirstFreeColumn=> FirstFreeColumn \
                            FirstFreeRow=> FirstFreeRow

Excel.ReadCells             Instance:  ExcelInstance \
                            StartColumn:  1\
                            StartRow:  1\
                            EndColumn:  FirstFreeColumn-1 \
                            EndRow:  FirstFreeRow -1\
                            ReadAsText:False \
                            FirstLineIsHeader:True \
                            Value=> Value 


wait 3


Console.Write               Message: "The number of rows containing data is " + Value.rows.count

Console.Write               Message: "The first free row is " + FirstFreeRow

Excel.Close                 Instance: ExcelInstance

And here’s the console output:

output

Of course I’m wrong here, one of the rows doesn’t contain data - or does it? Is blank data still data? :smiling_imp:

EDIT: It just occurred to me that you might want a) to know if there are blank rows and b) to delete them if there are blank rows, and c) to count them again after deletion. If that’s true, though, I know I haven’t led you in the right direction - apologies.

Regards,
burque505

1 Like