Katalon Studio | How To Get Data From Excel

automationstepbystep.com/
Katalon Studio
How to get data from excel | csv files
Today we will learn:
1. How to refer values from excel/csv
2. How to iterate all the data from test data file
Code to get data from data file (findTestData)
Code to loop data source (getRowNumbers)
Step 1 : Create your test case
Step 2 : Create data file (excel/csv) and add your data
Step 3 : Create variables and refer in Test Case
Step 4 : Add your data file in Katalon Studio
Step 5 : Add your test case in a Test Suite
Step 6 : Click on Show Data Binding and add data
Step 7 : Save and Run
for(def rowNum=1; rowNum =findTestData("data1").getRowNumbers() ; rowNum++)
{
//WebUI.setText(findTestObject('Object Repository/Variables/Page_OrangeHRM/input_txtUsername'), Username)
WebUI.setText(findTestObject('Object Repository/Variables/Page_OrangeHRM/input_txtUsername'), findTestData("data1").getValue(1,rowNum))
//WebUI.setText(findTestObject('Object Repository/Variables/Page_OrangeHRM/input_txtPassword'), Password)
WebUI.setText(findTestObject('Object Repository/Variables/Page_OrangeHRM/input_txtPassword'), findTestData("data1").getValue(2,rowNum))
Thread.sleep(3000)
}
docs.katalon.com/display/KD/M...
PLAYLISTS
KATALON STUDIO
• Katalon Studio
SELENIUM BEGINNERS
• Selenium Beginner Tuto...
SELENIUM JAVA FRAMEWORK BEGINNERS
• Selenium Java Framewor...
SELENIUM TIPS
• Selenium Tips
SELENIUM PYTHON
• Selenium Python
SELENIUM BUILDER
• Selenium Builder
JAVA
• Java
JAVA TIPS & TRICKS
• Java Tips and Tricks
JENKINS
• Jenkins
JENKINS TIPS & TRICKS
• Jenkins Tips n Tricks
JMETER BEGINNER
• JMeter Beginner
JMETER INTERMEDIATE
• Jmeter Intermediate
JMETER ADVANCED
• JMeter Advanced
JMETER TIPS & TRICKS
• JMeter Tips n Tricks
SOAPUI
• SoapUI
POSTMAN
• Postman Beginner Tutorial
GIT & GITHUB
• Git and GitHub
DOCKER
• Docker
ROBOT FRAMEWORK
• Robot Framework Beginn...
WEB SERVICES (API)
• API Web Services
REDIS BEGINNER TUTORIALS
• Redis Beginner Tutorials
MAVEN
• Maven
MISC
• Misc Tutorials
TOOLS & TIPS
• Tools and Tips
QnA FRIDAY
• QnA Friday
SUNDAY SPECIAL
• Sunday Special
ALL PLAYLISTS
/ @raghavpal
Follow on TWITTER:
/ automationsbs
Subscribe on KZbin :
/ automationstepbystep
_____

[@HarpreetSingh-be2ls]

Your tutorials are one piece of gem Raghav, hats off for easy to understand tutorials and result driven videos. Cheers from New Zealand :)

[@RaghavPal]

Thanks Harpreet, humbled

[@allaboutcars4]

Thanks for this video, its really helpful and the way you explain all the things are commendable.

[@RaghavPal]

Glad it was helpful Azhar

[@brandonhogg4544]

Super useful video , recommend the full Katalon series, glad you showed the various different ways to do this and how to loop the test steps. Thanks

[@RaghavPal]

Glad it was helpful Brandon

[@OtRatsaphong]

Thanks Raghav, Very helpful. Just what I was looking for. 🙏

[@RaghavPal]

Happy to know this.

[@TheLexi2304]

Thank you, that helped me very much :)

[@RaghavPal]

Glad to know this Ines

[@waniya-huzzu]

As usual, you are awesome... learning so many things from you..... keep doing help us.....

[@RaghavPal]

thanks for your message Azharuddin

[@TheUltimateTrainJourney]

Thanks you I was not adding variables to my test case hence not getting excel data u are a saver

[@RaghavPal]

Happy to know this helped

[@divijashankar3959]

Hi Raghav, I am on Katalon 5.8.3 and trying to implement the steps from this tutorial. After creating the variables and when I try to refer these variables in manual mode, I do not see it in dropdown. Also do we need to refer the excel file somewhere? Otherwise how will the system know where to refer the data from?

[@RaghavPal]

Hi Divija, Yes, you need to refer the excel file in Data Files. This is shown in the video. Pls check the video again, You should get the solution for your other issue too.

[@shankarkadimella]

Thanks Raghav its very helpful for every automation engineer.pls keep updated vedios

[@RaghavPal]

You're welcome Shiva

[@axellovemouele742]

You're a genius of the explanation. Thanks a lot

[@RaghavPal]

Most welcome Axel

[@trupti.............4585]

Amazing work.. it really helped me while using Katalon for first time

[@RaghavPal]

Thanks for watching

[@nomudajez7398]

Another brilliant tutorial. Thank you very much Raghav. Jez

[@RaghavPal]

Always welcome

[@ayanami2109]

i love you man. You save my life, really

[@RaghavPal]

So happy to know this

[@sabrinazavala736]

Hello, I would like to know if I can take the values ​​from the web and save it in an excel file? How is it done? Is there a video that explains this? Thank you. Greetings from Argentina.

[@RaghavPal]

Hi Sabrina, you can do that, will have to add some code. Here are few links to help docs.katalon.com/katalon-studio/docs/write-data-into-excel-file-at-runtime-during-test-execution.html codebun.com/read-and-write-data-from-excel-in-katalon-studio/

[@sunandarmyokalaw]

It is very helpful. Thank you so much.

[@RaghavPal]

Glad it was helpful

[@flotypus]

when to using data from other source like excel.. does in first place of testing we should already have that credentials input?

[@RaghavPal]

Yes, if you are using Katalon Studio to automate test cases that require login credentials or other dynamic data from external sources like Excel, it's generally recommended to have the credentials or data stored and retrieved before the actual testing steps begin. This approach ensures that the test cases have access to the necessary data throughout the execution and can proceed smoothly. There are two primary approaches to handling data in Katalon Studio: 1. *Data Files:* Katalon Studio provides a Data Files feature that allows you to store and manage test data in various formats, including Excel spreadsheets. You can then utilize the `findTestData` keyword to access and reference the data during your test case execution. 2. *Global Variables:* Global variables can be used to store common data values that are accessible throughout your test suite. You can define global variables directly in your test suite or load them from external sources like Excel using custom keywords or plugins. Both methods have their advantages. Data files offer a more organized and centralized approach to managing large amounts of test data, while global variables are convenient for storing frequently used data values. In general, it's advisable to handle credentials and other sensitive data using data files to maintain security and protect the integrity of your test cases. For data that needs to be shared across multiple test cases, global variables can be an efficient solution. Ultimately, the choice between data files and global variables depends on the specific requirements of your test cases and the structure of your test suite. Consider factors like data volume, data access patterns, and security concerns when deciding on the most appropriate approach.

[@faigasgarov9746]

Thank you very much man you are the best

[@RaghavPal]

most welcome

[@ashokchoudhary5716]

Thank you raghav for the video, i have one question when i run the script the second row data is not considering. Only first row data( valid data ) from the excel file is taken and script is pass, but second row data(invalids data)is not executed.

[@RaghavPal]

Hi Ashok, pls check in the Test Suite, Test Data section, There is a colum Data Iteration where you can set the rows from where data has to be taken. Also check your excel, whether there is any gap in the rows. Any rows hidden etc

[@perumalkalathi4973]

Very helpful. Thank You

[@RaghavPal]

Glad to hear that Perumal

[@irina30ic86]

Hello! I have a question.. so I have a web site, I login with facebook in order to create an anouncement. The 'create anouncement' page, has multiple fields that i must complete and several of them are by choosing the given option. I've imported the data from excel, but I don't know how to make the test suite work. I tried as you did in the video, but I am kinda lost because I have multiple fields and in some of them you must choose an option by the given ones. (Such as you have to choose between 2 given sections)

[@RaghavPal]

Hi, first try running the scenario using hard coded values and check if everything works properly. After this you just need to parameterize the values that you need from data file and then refer it from excel as shown in the video

[@astiwijaya849]

hi, it was very helpful tutorial. I have question, is it possible if we want to put selection object under data excel, for example, in registration where we have to select gender or datebirth? Thanks before :)

[@RaghavPal]

Hi Asti, you can use select by value function and the value you can refer from excel

[@aniketwaikar2520]

Thanks Sir.... Very good tutorial

[@RaghavPal]

You're welcome Aniket

[@ChiragPatel-rb9lz]

Hello Raghav, Thanks for sharing such a useful video

[@RaghavPal]

You're welcome Chirag

[@chubbyman2564]

Hello sir i want to ask a question, lets say i made a test case with katalon data driven for the username and password in a login test, then i send the project with github to my senior, do they need to specify the path or make a new excel file again? or is there a way so the excel file for the data driven testing is integrated automatically on others pc katalon studio, hope you can help cheers :)

[@RaghavPal]

Hi Yes, you can keep the excel file in a folder within the project folder and in the Data File where you have added the excel use Relative path rather than absolute path

[@chubbyman2564]

@@RaghavPal OMG THANK YOU SO MUCH, CHEERS DUDE

[@vitorcarvalho5106]

Thanks Raghav, It's possible to get a random value from the column every time I execute the test case?

[@RaghavPal]

Yes Vitor, you can check this - forum.katalon.com/t/random-select-value-from-data-file/12299 Also when you do Data Binding in Test Suite, under Test Data section you will find column Data Iteration and Type, Can check that as well.

[@vitorcarvalho5106]

@@RaghavPal Thanks a lot.

[@mysubhashini]

Hello Raghav, after data binding is done at test suite level, I am able to run the suite and get results . But when I close and reopen the project ,the data binding which was done before disappears . I have to do the binding again to run the suite . Is there any way to save the data binding for future use?

[@RaghavPal]

Hi Subha, what ver are you using, This looks like a issue, Did you check with any other ver. Else put this on katalon forum - forum.katalon.com/

[@Sener]

Great video course, very helpful, but I keep getting an error that says "please set data source for test data" when I try to attribute the value in the variable binding. Can anyone help? I have no idea what to do, all previous steps seem accurate.

[@RaghavPal]

Hi, in the above section of test data, have you added your test data and saved the project

[@vibhorkumar1008]

Hi raghav, Is there any way to delete rows from an excel file. I tried FileInputStream readFile = new FileInputStream (new File(filePath)) XSSFWorkbook workbook = new XSSFWorkbook(readFile) XSSFSheet sheet = workbook.getSheetAt(0) int index = sheet.getLastRowNum() Iterator rowIte = sheet.iterator() while(rowIte.hasNext()) { rowIte.next(); sheet.createRow(index) But this isn't working. The loop is working fine but it isn't deleting the row. Can you suggest some way to handle this?? Everytime I run my script, first I need to clear that excel and again write it.

[@RaghavPal]

Hi Vibhor, Usually we do a read from excel, What is your scenario, What do you need to write into excel.

[@andresd.rojasherrera6119]

Thanks for video, i got a question is possible do filters to columns with katalon and add columns and registers?

[@RaghavPal]

Hi Andres, I did not get your exact scenario, Can you give an example

[@andresd.rojasherrera6119]

@@RaghavPal Of course, I have an Excel with 3 columns, where I need one of them to activate the filter and filter a value and can work with the filtered data of that column, additionally for each recording row needed to add an observation in a new column

[@RaghavPal]

Hi Andres, I do not see any in-built feature to handle filtered data in Katalon Studio.

[@cleytoncosta5975]

Great Video!

[@RaghavPal]

Thanks Cleyton

[@Janhitha_Janisha]

Hi Raghav it's a nice tutorial. Do you have any training sessions going on Katalon Studio with Groovy Script ? Please let me know. Thank you

[@RaghavPal]

Not right now Nagesh, you can subscribe on the channel and my website to get notified when I plan - automationstepbystep.com/

[@qweldl]

Very helpful

[@RaghavPal]

Happy to know this.

[@hemantsinha2730]

Thank you so much

[@RaghavPal]

You're most welcome Hemant

[@hazimabdoh6303]

hellow mr Raghaf , one question pls , did you create input_txtusername and input_txtpassword and input_submit from object repository ? thanks

[@RaghavPal]

Hi Hazim, Can you point the time in the video to refer, In general, this must be coming from test data

[@hazimabdoh6303]

@@RaghavPal its appeares at the beginning of the video thats already shown and pre-prepared , i create those elements from object repository and every things done correctly , thanks alot

[@ovp1774]

Hi i want to ask, when the tools tried to read the password value from the excel, it said that 'Unable to set encrypted text for object' and 'Input length must be multiple of 8 when decrypting with padded cipher' appreciate for the help regarding this..

[@ovp1774]

solved using import com.kms.katalon.util.CryptoUtil thanks..

[@RaghavPal]

Great to know

[@anucoonjan3348]

Hello Raghav, Can you please advise how i pass the variable of excel sheet data when calling a test case? Thank you.

[@RaghavPal]

Hi Anu, I did not exactly got your scenario, we are already using variables here and referring the values from excel.

[@STD2580]

Raghav, i have a question. First you showed at Test Suite level and after that you have modified at Testcase level. Now if we run at Testsuite level, will it work? Because we have removed the variable in testcase.

[@RaghavPal]

No, for TestSuite you have to follow the initial steps. I showed at TestStep level for people who want to check, validate and troubleshoot before adding to TestSuite

[@STD2580]

Automation Step by Step - Raghav Pal So very first time we need to do for test case and if everything is working we need to modify as per test suite if we want to run through test suite. Is that correct?

[@RaghavPal]

So before adding scripts at test level just see if you are able to run from test suite as shown in the initial section. If that works you need not do the section of adding scripts to test case

[@gowthamgr1280]

Thanks for this video.. this is really helpful... if i want to read data from 2nd worksheet in excel file.. How would i do that ? below code, reading data from sheet1 default. String strval= findTestData("TESTDATA_XYZ").getValue(1,1) i have many worksheets(sheet1, sheet2, sheet3). i need to read data from sheet2.. is there any method present to recognize the worksheets?

[@RaghavPal]

Hi Gowtham, pls watch next video where you can use DataFiles option and select the sheet

[@brandonhogg4544]

Really helpful video, can I ask how you have the intelisense so it can predict what function you are trying to enter?

[@RaghavPal]

Brandon As you type, if you do not get the autosuggestion box automatically, can try pressing Ctrl+Space

[@brandonhogg4544]

@@RaghavPal Thanks that works perfect

[@hippiestafarii]

Thanks a lot :-)

[@RaghavPal]

You're welcome

[@marzookrehana5226]

Hi Raghav, Thanks for your all your effort. Can you please create tutorial on how to connect Katalon with SQL server and Oracle DB and also how to do Data driven testing using DB instead of Excel. Thanks Again.

[@RaghavPal]

Hi Marzook, I will do

[@hseokxi3136]

if the form type such as dropdown list or option value, how to input the data in excel?

[@RaghavPal]

Hi, the process of data driven will remain same, you will need to parameterize the values with variables and then get the value of the variables from data file as shown

[@upendrasethi9186]

For an Input field , i am getting the data from the excel file but after getting value from excel , the data is loading but how to select the value after loading because when using spy, that part is getting disappearing, user can Press Enter to select that.How i will select that value after data getting loaded via script or in manual mode Please advise on this,

[@RaghavPal]

Hi Upendra, I did not get your query, Can you tell the time in the video, that you are not able to follow

[@santooveluri7128]

Thanks Raghav for the video. Could you please create video for how to perform data driven testing for Webservice testing using WSDL file in Katalon studio. Also, please let us know how to add attachment / images (png, jpeg)/ pdf's while performing CreateContent Request / Ingetion service for Webservice testing using WSDL in Katalon studio

[@RaghavPal]

Hi Santoo, I will look into this in some time

[@himanisaxena392]

Hi Raghav, Thanks for the video. Could you please help me . I have n number of test cases in Test suite and I have created a single data file with n numbers of sheets. Now, If I want to run test suite with each test case referring different sheet, than how to do this?

[@RaghavPal]

Hi Himani, you can do data binding at test case level too now

[@devrajpatel1169]

If I am adding data from csv file for test case and on submitting I get a output in the form of a number and I have let's say 1000 test cases, is There a way I can get the output number that is generated recorded in a csv file?

[@RaghavPal]

Hi Devraj, you can capture the text of the object/element showing the number and then use it as you need

[@devrajpatel1169]

@@RaghavPal so if I am playing the test case 1000 times, will all those 1000 outputs(number format) be stored at some place?

[@RaghavPal]

you will need to script for that

[@supriyasubramani4979]

Hi Raghav, how to fetch data from different sheets of one single excel file ? here by default it will select first sheet. So if i want to retrieve data from sheet 2 for other cases how to use that here ? please help. Thank you in Advance

[@RaghavPal]

Hi Supriya, when you create a data source in Katalon and select excel, there is option to select the sheet as well, You can create multiple data files and select diff sheets for them and use in your test accordingly

[@pradnya22021985]

hello.. I was trying to automate my application with excel file. I did all the steps mentioned in video but My First name field is not getting from file on my web application. I am getting below error in logs Text '' is set on object 'Object Repository/Page Your personal information/input_First name_primary-firstName' Can you please help me to understand what I am doing wrong

[@RaghavPal]

Hi Pradnya, can you check the code of the step

[@josembass]

Raghav, have you ever encountered the "splash screen never started" issue? The one that's dependant on the manifest of the apk. It's causing inconsistent failures on my tests (sometimes it works, sometimes it fails with the Cannot create remote sessions exception). Great videos, just suscribed. Best regards

[@RaghavPal]

Hi Jose, never faced this, I tried searching online but could not find much.You can ask this on katalon forum - forum.katalon.com/ OR gitter community chat - gitter.im/katalon-studio

[@maryamhinchi2403]

Hi, i have an problem in variable binding, when i try to add value it shows an error message : "please set data source for the test data: data files excel"

[@RaghavPal]

Hi Maryam, have you set the data source and saved. Pls check all the steps again.

[@maryamhinchi2403]

thank youuu so much !

[@christianwinarjo9836]

Hi Raghav, Your tutorial video was amazing and i have a question about data driven testing. let says we doing a login test with positive and negative case. then we also want to verify the combination user and pass with the notification message showed up. we have test data on excel like below: username | password | expectedResult Admin | admin123 | Login success! Adm | admin123 | Invalid credentials! Admin | adm123 | Invalid credentials! is it possible to make this on katalon, without making multiple Test Case with different user, pass and verify element text value?

[@RaghavPal]

Hi Christian, Yes, we can do that using data driven testing

[@pradipathennarasu7294]

Hi Raghav..Thanks for the video and it was very useful. One quick query on a issue I m facing while trying to get data from excel. I have an excel which has 2 columns and 2 rows of data( i.e 2 sets of username & Password) even after exhausting the 2 sets,Katalon attempts to run with the 1st set again and loop continues. Could you pls assist on what could be wrong?

[@RaghavPal]

Hi Pradipa, In the Test Suite , when you click on Show data binding. In Test Data section, there will be a column to set iterations, Check that

[@pradipathennarasu7294]

Thanks Raghav for the suggestion! But there was some looping issue and is fixed,Now the data is picked correctly and loop ends perfectly while running from test case mode,wherein when the same is added to the test suit collection,yet again the loop continues indefinetely irrespective of data being exhausted. Not sure if there is any specific setting to be made. It would be great if you could help here

[@RaghavPal]

I will need to see the setup

Thanks, Raghav. I have an issue: My CSV file has 2 rows(title, value) and many columns, so that How I can use getValue(a,b)? Thank you so much.

I understood and It worked for me. Thank you for your video.

[@RaghavPal]

Happy to know Ánh

[@TheUkr1244]

Hi Raghav, For me looping should happen without starting whole case again. only some steps of mine should loop with test data. Please help

[@RaghavPal]

Hi Uday, if you see the section from 13:25, i have added a loop, you can customise as per your need

[@TheUkr1244]

Thanks a lot Raghav for quick reply and also your solution helped me a lot.

[@bugnay9314]

Hi Raghav, what if I transferred my project to other computer. and your excel file data directory will not be detected. how we can handle that?

[@RaghavPal]

Hi, you can keep your excel in a folder within the project and provide relative path

[@Munjal_Sirsa]

Sir, By excel I need to verify the text by clicking on different elemts..Can we do that by katalon studio?

[@RaghavPal]

Hi Munjal, you will need to get the values from excel and then extract the value from webpage element, then use keyword - WebUI.verifyEqual(actualValue, expectedValue)

[@manojkansal02]

Hi Guys, I want to send the List variable through the excel instead of the String. Not able to do this as data comes in the string format. How to do this?

[@RaghavPal]

Hi Akash, did you select type as List from the dropdown. I will have to check on that separately.

[@manojkansal02]

@@RaghavPal Yes, Looking for cases List and Map

[@nagarajdevaraconda8022]

Thanks Raghav !!

[@RaghavPal]

You're welcome Nagaraj

[@AnilKumar-sf4em]

Thanks a lot Raghav. I have 10 test cases and respective data stored in 10 rows in excel sheet. I need to executed each testcase with 1 row of data from excel sheet. please help.

[@RaghavPal]

Hi Anil, You will have to add variables in your test cases and then add to a test suite. In test suite goto data binding section and under test data you will see a coloum for iteration where you can select specific rows and can also do one or many mapping. Pls see this - docs.katalon.com/katalon-studio/docs/combine-multiple-data-sources.html#test-data

[@AnilKumar-sf4em]

@@RaghavPal Thanks a lot Raghav, Information in above url is really helpful and solve my problem.

[@basavakeerthi4107]

Hi Raghav,Thanks for the great video. Can you please help me in how to run specific testcases with execute testcase column as 'Yes' in test data excel

[@RaghavPal]

Hi Rashmi, In Katalon Studio, you can do this from TestSuite or TestSuiteCollection

[@basavakeerthi4107]

@@RaghavPal can you please elaborate more on it. I noticed by using 'tag' we can run specific set of testcases like regression, release1, release2. But my scenario is run same test case flow with different testdata. So this testdata is an excel which is imported into katalon. So its like filter few columns in excel and run based on filter. Although we filter few columns and import the excel testdata sheet to katalon, I think it will still run all the hidden rows too. I know that we can mention specific row numbers in Data Iteration. But let's say I have 100's of testdata rows and i want to mark few random test data rows to 'Yes', then i can't manually mention all these row numbers in data Iteration which is tedious. So, can you please let me know if there is any easy approach to do it dynamically

[@RaghavPal]

Okay Rashmi, I believe this type of feature is not available directly, You may do some scripting for that

[@julianpatino8133]

Hi Raghav thank you for this course. I understand the logic of the loop. But what is the purpose of that? I don't understand the purpose of the loop. I see that the system runs the test, enters the information of the first row (user and password (corrects)) and without pressing still the button “send" write the information of the second row (the incorrect). I don't understand the purpose. The only way to know if the credentials are correct or not is pressing the button “Send" Why the system didn't press the button "send" when enter the credentials in the first iteration of the loop? Thanks for the clarification.

[@RaghavPal]

Hi Julian, will try to create a session to explain

[@julianpatino8133]

​@@RaghavPalThank you Raghav. By the way; you are a great teacher. God bless you. In this video I had that doubt. What is the purpose of entering the information of the rows first and only finally press the button "Send"?. I understand perfecty what is doing the loop but the concept still is not clear for me. Thanks again and GOD bless you!!!

[@RaghavPal]

that is the library syntax, you can avoid the scripting part and use the Katalon in built data file method

[@asadhaider1961]

Hello Raghav I hope you are doing fine. Raghav can you tell me if it is possible to validate all the text of web page like if we have to validate blogs of academic websites. Please if there is any tool which does this let me know. Thanks..

[@RaghavPal]

Hi Asad, what exactly you want to check, this can help kzbin.info/www/bejne/q4mVd2dpf7imgdU

[@123cheke]

Nice video. Don't you have one where you work data driven from Katalon plug in?

[@RaghavPal]

Not from Katalon Plugin as of yet

[@NagaRaju-se5ov]

Hi.. Do u know how to get data from different sheets? Let's suppose we have 3 sheets(sheet1 , sheet2, sheet3) in excel file .. below code will point to sheet1 bydefault findTestData("data1").getRowNumbers() How to get data from sheet2?

[@RaghavPal]

Hi Naga, we would normally use the approach where we add excel to Data Files section and in test cases add variables and in test suite map data. In this case in your data file you can specify the sheet

[@NagaRaju-se5ov]

​@@RaghavPal is any option to specify sheet name for below code findTestData("data1").getRowNumbers() Example : In UFT , if we want to retrieve the data from sheet2 , we write below code DataTable("Col", dtGlobalSheet) Please Let me know ... How to get data from sheet2 like in UFT? Could you please the code if we have option in katalon?

[@RaghavPal]

Usually here we specify sheet in the Data File section, where we add the excel file.

[@NagaRaju-se5ov]

@@RaghavPal - Boss, Tell me straight answer ?? Whether it is possible or not ? if possible then write the code..

[@maleshwarkinagi450]

@@NagaRaju-se5ov I am also facing the same issue, did you got any work around or solution? Kindly let me know

[@anandgudimanchi674]

Thank you very much Raghav, I followed and repeated a few times but I unfortunately do not see variables created in the variable bindings, Please may I know how to debug this. I have data file with variables, 2. Created Variables and changed to Data Variable as Only Variable is not there in my version"7.5.10 4".3. Added test case to Test suite 4. Clicked on Data Binding but I do not see variables listed.. what mistake am I doing?

[@RaghavPal]

Hi, Pls ensure that you have saved your project and also ensure the variables name is correct in test and data file

[@anandgudimanchi674]

@@RaghavPal Are you starting any course soon Raghav.

[@RaghavPal]

I will keep on adding online courses, For training, you can find on training page here - automationstepbystep.com/

[@anandgudimanchi674]

@@RaghavPal Thank you Ragav, I will take it further. I sent you an email.

[@yashpanchal5148]

Thank you Raghav. I am using second half of your video i.e. Test case level with iteration. Also, I have to write data in excel with every iteration. Can you please advise how can we do that?

[@RaghavPal]

Hi Yash In Katalon Studio, you can write data to an Excel file with every iteration by utilizing the Apache POI library, which is a popular Java library for working with Microsoft Office files. Here's a step-by-step guide on how to achieve this: 1. Add Apache POI dependencies: - Download the Apache POI library (poi-4.x.x.jar, poi-ooxml-4.x.x.jar, and poi-ooxml-schemas-4.x.x.jar) from the Apache POI website (poi.apache.org/). - Copy these JAR files to the `Drivers` folder in your Katalon Studio project. 2. Create a custom keyword: - Right-click on your project in the Katalon Studio Test Explorer and select New > Keyword. - Provide a name for your keyword, e.g., "ExcelUtils," and click OK. - In the newly created `ExcelUtils.groovy` file, write the following code: ```groovy import org.apache.poi.ss.usermodel.* class ExcelUtils { static void writeDataToExcel(String filePath, String sheetName, int row, int column, String data) { FileInputStream fis = new FileInputStream(new File(filePath)) Workbook workbook = WorkbookFactory.create(fis) Sheet sheet = workbook.getSheet(sheetName) Row excelRow = sheet.getRow(row) if (excelRow == null) { excelRow = sheet.createRow(row) } Cell cell = excelRow.createCell(column) cell.setCellValue(data) FileOutputStream fos = new FileOutputStream(new File(filePath)) workbook.write(fos) fos.close() fis.close() } } ``` 3. Use the custom keyword in your test case: - In your test case, import the custom keyword by adding the following line at the top: ```groovy import com.yourprojectname.ExcelUtils ``` - To write data to an Excel file, call the `writeDataToExcel` method with the required parameters: ```groovy String filePath = "path/to/your/excel/file.xlsx" String sheetName = "Sheet1" int row = 1 int column = 2 String data = "Test Data" ExcelUtils.writeDataToExcel(filePath, sheetName, row, column, data) ``` - Adjust the `filePath`, `sheetName`, `row`, `column`, and `data` variables according to your specific scenario. This custom keyword allows you to write data to an Excel file from within your Katalon Studio test cases. You can use it in combination with loops or data-driven testing to write data with each iteration. Just make sure to provide the correct file path, sheet name, row, column, and data values.

[@yashpanchal5148]

@@RaghavPal Awesome! Thank you for the quick reply.

[@prudhvip143]

Hi Raghav, How to read desktop application table/grid ? I am unable find this.. Thanks in advance:)

[@RaghavPal]

Hi Prudhvi, For desktop applications you will have to use Katalon ver 7. Check this - kzbin.info/www/bejne/f6Koi3Vqo7uYo5o

[@mbmferguson]

when I run a similar test case from the test suite, it tries to run my test case twice (Runs: 2/2 - your first run). How do I set it to only run the test case once (Runs: 1/1 - your second run)

[@RaghavPal]

It may be due to 2 sets (rows) of data. Check what is the data used in the 2nd run, You can keep a single row of data and try

[@mbmferguson]

@@RaghavPal Yes that was the issue. I set my worksheet with the data as Type "Many" in the Test Data window in the data binding section which made the suite think I wanted to run it twice once for each row but the for loop is already going through the data. thank you so much for this video very informative and helpful!

[@bugnay9314]

Hi Raghav, How we can run our test script based on the env so it can identify the credential that we need to use. Without changing manually the data path? Example In QA env - we have credential : abcd/1234 In PROD env - we have different credential : bcda/1111 Regards,

[@RaghavPal]

Hi Brylesu, You can use execution profiles for that

[@calvinli5464]

it seem like only can use encryption password if not it would be error would you tell me how to fix it please i see ur video on udemy u say can use Menu bar > Help and encrypt your text but if i have lot data to key-in might be too slow can i ask which version is this in the video please i'll try to degrade to fix this thank you!

[@RaghavPal]

Hi Calvin, use encrypted value when keyword is set encrypted text. If you use keyword set text , you can give raw value

[@calvinli5464]

@@RaghavPal oh i see if i don't want the encrypted value just change the item value i love you so much thank you!

[@waseemaq]

Hi Rag, in this video u said " u created a simple login test to save time" but everytime i web record this test it gives me setEncrypt() in login password line in script view why is this? Because it giving me error when i do read from excel file. Thank you. P.s. great video albeit one important omission of explanation mentioned in my question.

[@RaghavPal]

Hi Waseem, if the action is setEcryptedPassword then in the excel file also you will need to put the encrypted value of the password. You can encrypt from the menu Help > Encrypt Text Else use setText keyword

[@bhavpatel2196]

Thanks for another great video could you tell me how can I avoid using duplicate data plz through excel data. So basically I'm able use coloum 1 (username) and coloum 2 (password) but when I run test case again, I want to avoid same row next time and want katalon to pick next username and password ( not the one used before) plz. Hope that make sense.

[@RaghavPal]

Hi Bhav, you can do this in Data Binding

[@sagarkulkarni0301]

Need help on below installed katalon studio 7.3.1 and created test cases and test suite. Installed jenkins as well. I want to automate this test suite using jenkins ? I referred kzbin.info/www/bejne/mJyQk6WOiKiWetk this video but build CMD option is not visible in my studio, what are the steps to integrate Studio with Katalon runtime? I am looking quick help on this. thanks in advance

[@RaghavPal]

Hi Sagar, do you see a similar icon like console run. Few GUI have changed and also Katalon now is a commercial tool

[@arunmozhivarmen8249]

After I added the variable.. it’s not loaded in data binding and where as I save the test case it showing unable to save test case

[@RaghavPal]

Hi Arunmozhi, so I think that is the issue, unless you save the test case, it may not work, Try to see why exactly you are not able to save, Can try to create new one

[@arunmozhivarmen8249]

Thank you.. got it

[@kasarlanagaraju7780]

Hi Ragava, thanks for this vedio.but I need help write/export the results into Excel sheet with multiple columns and rows. Could you please share code for that

[@RaghavPal]

Hi Kasarla, I do not have the code handy now, You can find online

[@kasarlanagaraju7780]

Hi Ragava, Thanks for your reply. I have tried but not getting proper solution for suite level data.lets assume I am executing 20 test cases, after placing order each time I want to write order details into Excel sheet with multiple columns

[@sayanbhowmick6080]

Sir, It shows me an Error while browsing the Excel file. The error message is -> an internal error occurred during: "load excel file". file is unsupported . But sir I'm loading a .xlsx file. Can you help me out?

[@RaghavPal]

Hi Sayan, pls check you followed all steps Last option will be to try with a diff ver of katalon studio

[@sayanbhowmick6080]

Hi Sir, I have resolved it....actually it is not taking .xlsx but it takes .xls extension. now working....

[@tushargonjari5512]

is SQL data quality testing possible using Katalon ?

[@RaghavPal]

Hi Tushar, Katalon can connect with DB and can get data to use in test cases, Can also compare data to a previous state, If you checks comes in this process you can do it

[@tushargonjari5512]

@@RaghavPal thank you

[@harishl4881]

Hi raghav. How to read Key-value pair data from excel in katalon?

[@RaghavPal]

Hi Harish, katalon will get the cell value, in case you need any further processing or extraction, will need to code for it.

[@farihahossainmuna3395]

when i try to create data binding and add data files,the variable binding doesn't show anything

[@RaghavPal]

Check the variables are same in test case and excel file, check for caps, white spaces, Also save project before doing Binding

[@AnilKumar-dc6hv]

Hi Tried to run 15 testsuite parallelly but it keeps crashing .

[@RaghavPal]

Hi Anil, this depends on your system configuration and hardware

[@azminurul6325]

Hai, thank you for explanation. Next, please explain how to verify/validation if input field is empty

[@RaghavPal]

Will try on this Nurul

[@azminurul6325]

@@RaghavPal Sure. Thank you, sir. I cant wait !! Yeaah!!

[@markorasic6210]

Hello, great tutorial. Just one simple mistake in code in the description, it says rowNum =findTestData("data1").getRowNumbers() and it should be rowNum

[@RaghavPal]

Hi Marko, thanks for the feedback. I will check this.

[@NagaRaju-se5ov]

Hi.. Do u know how to get data from different sheets? Let's suppose we have 3 sheets(sheet1 , sheet2, sheet3) in excel file .. below code will point to sheet1 bydefault findTestData("data1").getRowNumbers() How to get data from sheet2?

[@smitsneek]

Thanks Marko, that was the thing that was failing my testcase!

[@wolkenharshitha5417]

How to store data from test into variable and fetch it?

[@RaghavPal]

can create local or global variables and extract the value at runtime and store, Can see some examples online

[@navvyanavelinanda5707]

How to sort products price in katalon please share this

[@RaghavPal]

Hi Navvya, I don't think there is some direct and in-built keyword for that, you can check plugins store.katalon.com/ , You will need to create the logic as per your need

[@islamlotfy1696]

good i want to verify for each step the wrong message (combination email and password )

[@RaghavPal]

Sure, try it out

[@Martin-sjv]

Code Example in the comments for the loop is wrong. The definition of the loop should be

[@RaghavPal]

I will check Martín. Thanks for the feedback

[@sandeepbandoju6785]

Hi team......... can you plz provide videos on 'window based operations in katalon and java with katalon' ASAP

[@RaghavPal]

Hi Sandeep, as of now Katalon can handle web, api and mobile, In case you need any automation on windows app, it is not yet supported

[@sandeepbandoju6785]

Ok but by using winium can't we automate by using winium tool...!? and im facing one problem while recording i'm getting exception like "Session not created: this version of chrome driver supprots only chrome version....." can u plz help me regarding this exception @@RaghavPal

[@RaghavPal]

Hi Sandeep, yes, you can use third party jars and can create custom keywords, Winnium or any other jar can work, An example is here - forum.katalon.com/t/katalon-studio-native-popup-window-and-upload-file/19386 Also there is an example with winnium - docs.katalon.com/katalon-studio/docs/katalon-with-winium-for-desktop-applications.html You can change chrome driver ver and can check. You can put your chrome driver.exe lie this - docs.katalon.com/katalon-studio/docs/update-or-replace-web-browser-drivers-and-selenium.html

[@mahimakaranam1504]

Hi Raghav Can you please create tutorial on how to write data into excel

[@RaghavPal]

Hi Mahima, you can use plugins from katalon store

[@clarencerector5404]

can you do this with the free version?

[@RaghavPal]

Yes, can check the comparison table here www.katalon.com/pricing/

[@masturachesulaiman2606]

I just wonder how to create the datadrivertest

[@RaghavPal]

You can just follow this and try some more examples. Let me know if you have any queries.

[@masturachesulaiman2606]

What is def actually.

[@RaghavPal]

Hi Mastura, it is a way to declare variables in groovy, Just in case you are interested you can look at my groovy course here - automationstepbystep.com/online-courses/

[@urszulag.1014]

I don't like such automatisation. It takes all the time and it has no sense! The programme should read that data authomatically, but not HUMAN would write such many data manually! IT'S NOT GOOD.

[@RaghavPal]

Hi Urszula, thanks for sharing your feedback. This is one way and we will have better options

[@gokulanathan6900]

You are an life saver..🫡

[@RaghavPal]

Glad to know this helped Gokul

[@waseemaq]

Hi Rag, in this video u said " u created a simple login test to save time" but everytime i web record this test it gives me setEncrypt() in login password line in script view why is this? Because it giving me error when i do read from excel file. Thank you. P.s. great video albeit one important omission of explanation mentioned in my question.

[@RaghavPal]

Hi Waseem, if the action is setEcryptedPassword then in the excel file also you will need to put the encrypted value of the password. You can encryp from the menu *Help > Encrypt Text* Else use setText keyword