# Loop rows in Exel sheet **URL:** \ **Category:** Integrations\ **Tags:** integrations, katalon-studio\ **Created:** [October 4, 2018, 2:03pm UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557 "2018-10-04T14:03:02Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![test19](https://avatars.discourse-cdn.com/v4/letter/t/e79b87/32.png) [@test19](https://forum.katalon.com/u/test19)\ **Post date:** [October 4, 2018, 2:03pm UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/1 "2018-10-04T14:03:02Z")
| Name | Age | | Person 1      | 20 | | Person 2 | 21 | | Person 3 | 22 | | Person 4 | 23 | How to loop this exel sheet data in katalon I have to fetch each Names and corresponding Ages using katalon How i can implement this in katalon ---
**Author:** ![Arnel](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/arnel/32/6360_2.png) [@Arnel](https://forum.katalon.com/u/Arnel)\ **Post date:** [October 4, 2018, 2:32pm UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/2 "2018-10-04T14:32:19Z")
Hi Test, You can try this code: import com.kms.katalon.core.testdata.reader.ExcelFactory //third parameter means if you want the first row as your header or column name. //In your case, it should be true. Object excelFile = ExcelFactory.getExcelDataWithDefaultSheet(“your\_excelFile\_location.xlsx”, “SheetName”, true) //create a loop for (int index = 1; index \< excelFile.size(); index++) { println excelFile.getValue(“Name”, index) + " " + excelFile.getValue(“Age”, index) } Check the output in the console. Hope that helps… 🙂 ---
**Author:** ![test19](https://avatars.discourse-cdn.com/v4/letter/t/e79b87/32.png) [@test19](https://forum.katalon.com/u/test19)\ **Post date:** [October 5, 2018, 9:39am UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/3 "2018-10-05T09:39:17Z")
Hi [Arnel](http://forum.katalon.com/profile/7314/Arnel) I created a variable XX and I bind it with the column Name in the exel sheet and my for loop is as follows, but I am getting only one row of exel file, looping is not working, for (int i = 1; i \<= 2; i++) { ``` WebUI.setText(findTestObject('Object Repository/New Folder/Page\_test/textarea\_Kindly fill the form'), XX) ``` } and I tried the following code also for (int i = 1; i \<= 2; i++) { ``` WebUI.setText(findTestObject('Object Repository/New Folder/Pagetest/textarea_Kindly fill the form'), XX, i) ``` } but got the error ---
**Author:** ![test19](https://avatars.discourse-cdn.com/v4/letter/t/e79b87/32.png) [@test19](https://forum.katalon.com/u/test19)\ **Post date:** [October 5, 2018, 11:46am UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/4 "2018-10-05T11:46:05Z")
> ---
**Author:** ![Arnel](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/arnel/32/6360_2.png) [@Arnel](https://forum.katalon.com/u/Arnel)\ **Post date:** [October 5, 2018, 1:23pm UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/5 "2018-10-05T13:23:02Z")
Hi Test, Do the code I provided. As I can see in your code, you didn’t define the excelFile and the index of it. ---
**Author:** ![Abhishek\_Kumar\_Gupta](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/abhishek_kumar_gupta/32/4844_2.png) [@Abhishek\_Kumar\_Gupta](https://forum.katalon.com/u/Abhishek_Kumar_Gupta)\ **Post date:** [October 5, 2018, 6:00pm UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/6 "2018-10-05T18:00:36Z")
You want to deal with excel file with data in multiple rows then use the XlsReader class available on the internet. Import the jars include with it . Create the object of this class and pass the excel file path and call the getCell Value and SetCell value, getRowCount… etc. It will be convenient for every situation. ---
**Author:** ![test19](https://avatars.discourse-cdn.com/v4/letter/t/e79b87/32.png) [@test19](https://forum.katalon.com/u/test19)\ **Post date:** [November 1, 2018, 6:03am UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/7 "2018-11-01T06:03:28Z")
Hi [Abhishek Kumar Gupta](http://forum.katalon.com/profile/11684/Abhishek%20Kumar%20Gupta) Instead of adding any external jar file or third party files can I deal with excel file with data in multiple rows using katalon inbuild methods or functionalities? ---
**Author:** ![Arnel](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/arnel/32/6360_2.png) [@Arnel](https://forum.katalon.com/u/Arnel)\ **Post date:** [November 1, 2018, 2:32pm UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/8 "2018-11-01T14:32:28Z")
Hi Test, You can deal with that. Let me provide you some link you can refer to: > **[(TEST DATA) Define specific Excel Sheets to get Excel Values](http://forum.katalon.com/discussion/8594/test-data-define-specific-excel-sheets-to-get-excel-values#latest)** > > Hi there, I'm going to share something that might help you when it comes to automation which involves excel file as your test data and defining the "SHEETS" you prefer to make your test data even more dynamic. > **[Loading test data file programmatically (Dynamic Test Data)](http://forum.katalon.com/discussion/7523/loading-test-data-file-programmatically-dynamic-test-data#latest)** > > Hi Guys, Just wanted to share some ideas about test data. Hope that helps. . 🙂 ---
**Author:** ![rainzetroc](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/rainzetroc/32/13034_2.png) [@rainzetroc](https://forum.katalon.com/u/rainzetroc)\ **Post date:** [June 17, 2020, 3:10pm UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/9 "2020-06-17T15:10:34Z")
> [@Arnel](#): > > import com.kms.katalon.core.testdata.reader.ExcelFactory Hi @Arnel can you help me solve my issue here: This is the result ![image](https://europe1.discourse-cdn.com/katalon/original/2X/c/c21846bcbe5f330cba3d019a13f08d9c926291fc.png) and this is the expected result, Ijust manually put Server Name , Read and Write as label for those colums ![image](https://europe1.discourse-cdn.com/katalon/original/2X/1/11f8dd20bfd446858834369db0468e4c2d559e76.png) this is my code for keyword: ![image](https://europe1.discourse-cdn.com/katalon/original/2X/e/e43836fb2669e4b2a3b54d28853e52ada3dee923.png) ![image](https://europe1.discourse-cdn.com/katalon/original/2X/7/7505d6a54074d71d9bd2e93e00fad5cf6077262a.png) thank you so much ---
**Author:** ![Arnel](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/arnel/32/6360_2.png) [@Arnel](https://forum.katalon.com/u/Arnel)\ **Post date:** [June 18, 2020, 3:06am UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/10 "2020-06-18T03:06:32Z")
Hi @rainzetroc, Could you elaborate what are you trying to achieve here?? Explain the scenario or something?? Thanks. ---
**Author:** ![rainzetroc](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/rainzetroc/32/13034_2.png) [@rainzetroc](https://forum.katalon.com/u/rainzetroc)\ **Post date:** [June 18, 2020, 3:46am UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/11 "2020-06-18T03:46:18Z")
Hi @Arnel I dont know how to elaborate it well. but im doing case to case testing like this is the only details i need from appdynamics: WebUI.setText(findTestObject(‘Page\_Databases - AppDynamics/input\_Sort\_ads-search-box-input pull-left n\_5d09ff’), serverName) CustomKeywords.‘excel.exportedData.getBrowerFile’(‘C:\Users\laarni.s.cortez\Katalon Studio\AppDynamics7\Excel FIles\customKeywords.xlsx’, ‘Sheet1’) CustomKeywords.‘excel.exportedData.writeExcel’(1, 0, serverName) WebUI.click(findTestObject(‘Page\_Databases - AppDynamics/div\_2764\_SCOM\_VRTVD25752\_PRD’)) not\_run: WebUI.click(findTestObject(‘Page\_2764\_SCOM\_VRTVD25752\_PRD - AppDynamics/OpenTheSearchServer’)) Result = WebUI.getText(findTestObject(‘Object Repository/Page\_2764\_SCOM\_VRTVD25752\_PRD - AppDynamics/div\_14140 KBsec’)) println(Result) CustomKeywords.‘excel.exportedData.getBrowerFile’(‘C:\Users\laarni.s.cortez\Katalon Studio\AppDynamics7\Excel FIles\customKeywords.xlsx’, ‘Sheet1’) CustomKeywords.‘excel.exportedData.writeExcel’(1, 1, Result) Result0 = WebUI.getText(findTestObject(‘Object Repository/Page\_2764\_SCOM\_VRTVD25752\_PRD - AppDynamics/div\_68330 KBsec’)) println(Result0) CustomKeywords.‘excel.exportedData.getBrowerFile’(‘C:\Users\laarni.s.cortez\Katalon Studio\AppDynamics7\Excel FIles\customKeywords.xlsx’, ‘Sheet1’) CustomKeywords.‘excel.exportedData.writeExcel’(1, 2, Result0) ![image](https://europe1.discourse-cdn.com/katalon/original/2X/4/46b86e8ff54b1792359733cd08c1d49297cb0fff.png) and i have total of 4 total servers need to lookup into appdynamics. this is actually just part of the end of day report im trying to automate. i have to access appdynamics, three splunk dashboards, and service now. i cant find way to automate the gathering of those data so i comeup with i will just write the get text into excel file. ---
**Author:** ![Arnel](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/arnel/32/6360_2.png) [@Arnel](https://forum.katalon.com/u/Arnel)\ **Post date:** [June 22, 2020, 2:51am UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/12 "2020-06-22T02:51:43Z")
Hi @rainzetroc , Are you trying to write the data in your appdynamics into your excel?? like are you trying to loop it? since I notice your code is repeating its steps. Do you encounter any errors? … correct me if I’m wrong. Thanks. ---
**Author:** ![jpereyra](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/jpereyra/32/16104_2.png) [@jpereyra](https://forum.katalon.com/u/jpereyra)\ **Post date:** [December 11, 2021, 2:36am UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/13 "2021-12-11T02:36:16Z")
Hi Arnel, I know this is an old post, your proposed solution would be useful for me, but seems that attribute “size” doesn’t work anymore. Do you know if it is deprecated? what can we use instead? Thanks, Ruben B ---
**Author:** ![kazurayam](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/kazurayam/32/333_2.png) [@kazurayam](https://forum.katalon.com/u/kazurayam)\ **Post date:** [December 11, 2021, 3:11am UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/14 "2021-12-11T03:11:37Z")
ExcelFactory.getExcelDataWithDefaultSheet()" returns an instance of `com.katalon.core.testdata.ExcelData`. You can read its API doc : [https://docs.katalon.com/javadoc/com/kms/katalon/core/testdata/ExcelData.html](https://docs.katalon.com/javadoc/com/kms/katalon/core/testdata/ExcelData.html) As you can see, there is no `size()` method or the equivalent supported. I do not know if @Arnel 's code worked years ago or not. At least it does not work in the recent versions (v8.2.0) of Katalon Studio. * * * People may suggest [https://store.katalon.com/product/34/Excel-Keywords](https://store.katalon.com/product/34/Excel-Keywords) . This keyword is good if you want to write data into an Excel file. However I think this keyword is too difficult to use If you just want read-only access to an Excel file. I checked it and found that the API of this plugin does not provide the “size()” or its similar either. I know the reason why. All of libraries in Java that deals with Excel depend on Apache POI, which does not provide the “size()”. It is because of the special nature of Excel’s data model. A text file naturally has the maximum number of lines. On the other hand, a _spreadsheet_ is not like a text file. A spreadsheet has conceptually limitless size of rows and columns. A spreadsheet model does not give you the numbers of rows. Therefore the API of Apache POI (and Katalon’s plugin) is not intuitive for people who expects an Excel sheet to be something like a text file. ---
**Author:** ![kazurayam](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/kazurayam/32/333_2.png) [@kazurayam](https://forum.katalon.com/u/kazurayam)\ **Post date:** [December 11, 2021, 9:12am UTC](https://forum.katalon.com/t/loop-rows-in-exel-sheet/13557/16 "2021-12-11T09:12:19Z")
See this: > **[GitHub - kazurayam/OliverHowardsDataDrivenTestingTutorial](https://github.com/kazurayam/OliverHowardsDataDrivenTestingTutorial)** > > Contribute to kazurayam/OliverHowardsDataDrivenTestingTutorial development by creating an account on GitHub. I think you would find the following code interestiing: > ```auto TestData data = findTestData("Demo_Account") for (def index : (1..data.getRowNumbers())) { ... ``` The `getRowNmbers()` method here is something like `size()` in Armel’s code. Katalon’s “Data-driven testing” feature wraps the POI API and provide a psuedo read-only view as if an Excel sheet to be a bunch of rows. `com.kms.katalon.core.testdata.TestData` class implements `getRowsNumbers()`, which is simiilar to the “size()” method.