Selenium Framework for Beginners 19 | Selenium TestNG How to get data from Excel using DataProvider

more about Raghav - automationstepbystep.com/
Today we will learn How to get data from Excel using DataProvider of TestNG
REFERENCES
How to use excel for getting data
• Selenium Framework for...
Today we will learn
1. How to get test data from excel
2. How to create excel data provider function
3. How to use testNG @DataProvider
4. How to refer test data from DataProvider
5. Run a sample data driven Selenium Test
helpful tips
Prerequisite
Add excel reading libraries (Apache POI)
Create functions to read data from excel
Watch the video - • Selenium Framework for...
How to get test data from excel
Step 1 -Create a function to get Row Count
Step 2 - Create a function to get Column Count
Step 3 - Create a function to get String cell data
Step 4 - Create a function to get Numerical cell data
How to create Excel Data Provider function
Step 1 - Create a new class ExcelDataProvider
Step 2 - Create a function and create object for ExcelUtils
ExcelUtils excel = new ExcelUtils(“excelpath”, “sheetname”);
Step 3 - Get row and column count
int rowCount = excel.getRowCount();
int colCount = excel.getColCount();
Step 4 - Run a loop to get all data in an Object Array
How to use testNG @DataProvider
Step 1
Create a function and provide testNG annotation
@DataProvider(name = “testData”)
Step 2
Call the excel data provider function with excel and sheet name
Step 3
Return the data object
How to get test data from excel
Step 1 Create a function and provide testNG annotation
@Test(dataProvider = “testData”)
Step 2 In the function arguments, get input parameters equal to the parameters in the excel file
Step 3 Use the values in your test
#SeleniumDataDrivenFramework #SeleniumTestNGFramework #SeleniumTraining #SeleniumBeginnerTutorials
PLAYLISTS
SELENIUM JAVA FRAMEWORK BEGINNERS
• Selenium Java Framewor...
KATALON STUDIO
• Katalon Studio
SELENIUM BEGINNERS
• Selenium Beginner Tuto...
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
Like on FACEBOOK :
/ automationstepbystep
Follow on TWITTER:
/ automationsbs
Subscribe on KZbin :
/ automationstepbystep
---------- Connect with Raghav -----------
Website - automationstepbystep.com/
LifeCharger - lifecharger.org/
Udemy Courses - www.udemy.com/user/raghav-pal-3/
Facebook - / automationstepbystep
Twitter - / automationsbs
KZbin - / automationstepbystep
Like, Subscribe & Share
You can support my mission for education by sharing this knowledge and helping as many people as you can
_______ ONLINE COURSES TO LEARN _______
automationstepbystep.com/
Never Stop Learning
Raghav

[@AsanAhmadli]

It took 4 years to find instructor like you. Thanks Raghav!!!

[@RaghavPal]

So happy and humbled to see this Asan. All the best

[@gurpreetkaur-bx3vw]

You are a life saver. I follow your selenium videos and honestly they are so helpful. Thanks to you for the new job I got and I recommend your videos to all my friends.

[@RaghavPal]

So happy to hear this Gurpreet

[@RajuDas-zn3gx]

Hi Raghav, this is excellent explanation. Video is extremely helpful. Thank you for this video

[@RaghavPal]

Great to know this Raju

[@karimasahli7278]

Thank you so much, your videos are very helpful!

[@RaghavPal]

You're very welcome Karima

[@rajendrakumarnayak5103]

Hi Raghav sir, Thank you so much for such a beautiful class you've provided for TestNG dataprovider. An beginner would understand easily the core concepts you've explained. I am readily able to run my dataprovider tests properly :) Thank you thank you so much Raghav sir.

[@RaghavPal]

most welcome Rajendra

[@kenzomarkus4888]

Instablaster

[@swethaputti]

Hi Raghav very neatly explained with points. Thanks you

[@RaghavPal]

You're most welcome Swetha

[@omkardandwate8922]

This is simply awesome...Great teaching..I am a fan of your teaching..This concept is greatly explained...Thanks Sir...🙏

[@RaghavPal]

You're most welcome Omkar

[@deepuvsoman1985]

The Best available in KZbin

[@RaghavPal]

Happy and Humbled to see this Deepu

[@priyadarshiniMG]

This is an awesome explanation for a really difficult concept. Thanks a ton sir for making my understanding very easy about DataProvider with TestNG.

[@RaghavPal]

You are most welcome

[@alurivijayarekha9840]

Any beginner can work just by keeping your videos aside Raghav. really helpful

[@RaghavPal]

So happy to see your message Aluri

[@sabuhiiqbal848]

Very Well Explained

[@RaghavPal]

Thanks for watching Sabuhi

[@jgraja2846]

excellent

[@RaghavPal]

Many many thanks

[@DebunkSensibly]

Thank you so much!

[@RaghavPal]

You're welcome!

[@neelanjanbasu7585]

@ Automation Step by Step - Raghav Pal : Excellent lesson... specially for the persons like us who are from manual testing background & quite new in automation. In my case I have to put from my excel sheet the Mobile no. (as username), Password, Confirm Password.... So while using the for loop I can't get how to pass excel.getCellDataNumber along with excel.getCellDataString... My first column is carrying the mobile number... for(int i=1; i

[@RaghavPal]

Hi Neelanjan, this video will help - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@sidb2023]

Thanks!

[@RaghavPal]

Thanks for the support Siddharth

[@sararamadan1907]

thanks

[@RaghavPal]

You're welcome Sara

[@bimlesh81]

Hi Raghav - Both your videos 18 and 19 on reading data from Excel are very informative and great work as usual! In my project, we use single excel input file to store test data for the various test cases. For example, my excel input file will have 10 different rows containing test data for the different test cases - each test case row will have column names like Test Case Name, Test Data1, Test Data2....Test Data5...and finally there is a column called Execute with values 'Yes' or 'No'. How do I extract the data to my DataProvider only based on the Test Case Name and when Execute column has the value 'Yes'. The criteria here is not to extract all the data from Excel input file but only to extract data based on the test case name and execute column = Yes. Appreciate if you can give your guidance here.

[@RaghavPal]

Hi Bimlesh, you can create scripts to read your excel and get data corresponding to the column that has Execute=Yes. This is help you - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@karishmabolke716]

Hi raghav, good explanation Kindly know which method we can used for alphanumeric values while fetching the data from xlxs

[@RaghavPal]

Hi Karishma, this can help - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@vamshivishwanath]

At 12:07 facing null point exception with the same code followed previoius vedio working fine

[@RaghavPal]

Vamshi will need more details.. error message, logs, code etc to troubleshoot

[@Vikram.G]

Thank you for making such videos... can u make video upon the how to execute nigative test... like when we enter worng id,pass then it should display error ... like this

[@RaghavPal]

Hi Vikram, I will plan

[@keerthichandrac2507]

Hi Raghav: It is a good video for DataProvider. One question what if our requirement is reading cell data from all rows instead from first row? How to dynamically give the row counter for for getColNumber method? or Can we assume that the no of columns are equal to all the rows as it was for first row? Is this good approach?

[@RaghavPal]

Hi Keerthi, this will help - stackoverflow.com/questions/41503897/how-do-i-retrieve-a-specific-row-of-data-from-a-testng-dataprovider

[@bharathirajsundaram3546]

Hi Raghav, Excellent Tutorial!! Excellent Effort!! Raghav how to handle empty cell(blank data) in dataprovider it throws org.testng.internal.reflect.MethodMatcherException:

[@RaghavPal]

Hi Bharathi, hope this can help - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@AnilKumar-sf4em]

Hi Raghav, Thanks, this video has good information. I have a 10 excel sheets in a workbook, each excel having one testcase(20rows). How to get all sheets data and execute 10 testcases, Please help.

[@RaghavPal]

Hi Anil, you will have to give the logic in your code. You can either name the sheets same as the test cases and in the code switch to the sheet by test case name and then get the data.

[@taibouchmohamed2154]

Hello Raghav , Could you please do for us a training about UFT and Ranorex please ?

[@RaghavPal]

Hi Taibouch, I will add to my to do list

[@CPLNet1]

@raghav well done mate. another clear instruction set.i paid for wisdom training (qtpselenium)and its a waste. only after watching your videos i was able to understand selenium/java concepts. i have question related to SOAPUI(Free version) groovy script..What version of POI we need to use with soap? I am try to use same excel sheet for soaui and selenium to run bother in go. please advice..

[@RaghavPal]

Hi Chan, glad to know it helped. You should be able to use latest poi jars. Pls check.

[@anithal9736]

Hi sir, plz make a video on how to write particular syso to excel using selenium testng

[@RaghavPal]

I will plan on this Anitha, this can also help, kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@canal_vhs]

Great job but please activate automatic subtitles, thanks

[@RaghavPal]

Hi Canal, mostly videos get the subtitles automatically. Some videos do not get due to audio issues. I will check on this again

[@annabelharland5416]

Hi Raghav, sometimes it could be very helpfull to compare my actions and code with your. Do you have the whole prodject somewhere on github?

[@RaghavPal]

You can get from github github.com/Raghav-Pal/SeleniumJavaFramework1 There is a video in the playlist on git

[@sathyasenthil3283]

Clear explanation.. I am able to login into my application with username and password but I m not able to get the next sheet inputs .. how to include more than one sheets?

[@RaghavPal]

Hi Sathya, you will need to call the sheet in your code and then get the contents This can help kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@satyawandhuri3359]

Hello Raghav, can you please guide on how to print this data based on the execution flag i.e yes or no ,means printing only the set of data we require?

[@RaghavPal]

Hi Satyawan, you will need to create a custom function for this

[@satyawandhuri3359]

@@RaghavPal is is possible for you to give a short idea on how it is possible? Would be very helpful

[@divyangjani7600]

Rather than creating new method for @Dataprovider can we also use that below method which is already returning object array of data??

[@RaghavPal]

Hi Divyang, You can make any method as TestNG DataProvider that is returning the data you require.

[@monicagoyal8225]

Hi Raghav, I am learning alot by referring your videos.Thankful to you for this.I followed your code and was able to print 2 sets of data but I entered 2 rows in my TestData and 3 test results ,cosole is showing with 1 set of "Null data" .Plz help me in understanding where did I go wrong?

[@RaghavPal]

Hi Monica, most probably if you are using a loop, it is going until a row or cell with no data, you can try printing the row, col number to confirm

[@monicagoyal8225]

@@RaghavPal I will retry it Raghav Sir ,One more help I need, you have demonstrated using this method in the same class ie testutils but if I am using it in some other class in a framework. There are 2 classes basically we have created and I am looking for one utility code which we can keep in my testutil file and then call the method by creating its object.

[@RaghavPal]

This can help Monica - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@franciscaparas78]

Hi Raghav, I'm a fan of your Selenium tutorial. Thank you very much for your lessons. By the way, I'm having a problem with your DataProvider lesson. I also wanted to get the empty cell but selenium is throwing me "java.lang.NullPointerException" i appreciate your help. Your code and my code are the same. Thank you. public String getCellDataString(int rowNum, int colNum) { String cellData = null; try { cellData = sheet.getRow(rowNum).getCell(colNum).getStringCellValue(); }catch(Exception exp){ System.out.println(exp.getMessage()); System.out.println(exp.getCause()); exp.printStackTrace(); } return cellData; }

[@RaghavPal]

Hi Francis, this can help - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@franciscaparas78]

@@RaghavPal Hi Raghav, Thank you for the link that you that you gave me, I watched it but it doesn't have how to get an empty/blank cell. :(

[@RaghavPal]

Okay, for now pls try online help

[@user-hm9cu3tl9y]

hello sir its really this made me to learn very easily ..can you help me to where is should declare file path without hardcoding..i am making my own framework

[@RaghavPal]

Manish Absolutely! Declaring file paths without hardcoding in your Selenium Java project for a custom framework is crucial for maintainability and flexibility. Here are some options to consider: *1. Environment Variables:* * Store file paths in environment variables like `TEST_DATA_DIR` or `CONFIG_FILE_PATH`. * Access them within your code using `System.getenv(variableName)`. * This approach is platform-independent and easily configurable during execution. *2. Resource Files:* * Place configuration files like `data.properties` or `config.yaml` inside your project's `resources` directory. * Access them using `ClassLoader.getSystemResourceAsStream(fileName)`. * This keeps your code clean and files readily available at runtime. *3. Classpath Scanning:* * Annotate your configuration files with a custom annotation like `@TestDataFile`. * Use a custom scanner to identify all files with that annotation at runtime. * This allows for dynamic discovery of configuration files without hardcoding paths. *4. Dependency Injection:* * Define an interface for fetching file paths and inject its implementation during runtime. * Different implementations can provide paths based on environment or configuration files. * This promotes loose coupling and allows for easy switching between different file sources. *Additional Tips:* * Use relative paths within your code whenever possible to avoid hardcoding absolute paths. * Implement a utility class to handle file access and path resolution for consistent usage across your framework. * Consider using libraries like Apache Commons IO or Jasypt for advanced file handling and decryption. By choosing the approach that best suits your needs and coding style, you can ensure your Selenium Java framework is flexible and adaptable, with file paths declared dynamically without hardcoding. Remember to choose an approach that balances readability, maintainability, and security based on your specific project requirements.

[@user-hm9cu3tl9y]

Here i completd the entire series of TestNg i dont know how to appreciate all your efforts i have no words to praise you....Thank you soo much for Valuable replay and your informative words...sir i want to show my framework which i have designed by taking all your informative videos. .i want to share my framework . i want to check wth whether i am follwing the right way sir...sir can you share me your contact @@RaghavPal

[@RaghavPal]

I may not get enough time to do a personal review, but if you have followed all the steps and understood the concept. you are good to go,.. can also try more examples online.. Best wishes Manish

[@user-hm9cu3tl9y]

Thank you sir...@@RaghavPal

[@dasarianveshitha7386]

When i run the dataprovider class getting index 1 out of bounds for length 1

[@RaghavPal]

Ammu The "index 1 out of bounds for length 1" error typically occurs when you're trying to access an element at an index that doesn't exist in an array or collection. Let's troubleshoot this issue in the context of TestNG and Selenium. Here are some common reasons for encountering this error and potential solutions: 1. Data Provider Mismatch: - The error message you provided mentions a data provider mismatch. This usually happens when the number of parameters in your test method doesn't match the number of data provider arguments. - Ensure that the number of parameters in your test method matches the number of columns (arguments) returned by your data provider. - Verify that the data provider method returns the correct number of arguments for your test method. 2. Data Provider Arguments: - Check the data provider method (`dpMethod` in your case). It should return a 2D array (or an iterable collection) with the correct number of columns. - Each row in the array corresponds to a set of arguments for your test method. - Make sure the data provider method returns the expected number of columns (parameters) based on your test method signature. 3. Test Method Signature: - Your test method (`myTest` in your example) should have the same number of parameters as the data provider method. - Verify that the test method signature matches the data provider arguments. 4. Example Fix: ```java import org.testng.annotations.DataProvider; import org.testng.annotations.Test; public class DProvider { @DataProvider(name = "data-provider") public Object[][] dpMethod() { return new Object[][] { {2, 3, 5}, {5, 7, 9} }; } @Test(dataProvider = "data-provider") public void myTest(int a, int b, int result) { int sum = a + b; // Your test logic here // ... // Assert.assertEquals(sum, result); } } ``` - In this example, the data provider returns three columns (arguments), and the test method `myTest` accepts three parameters. 5. Check Excel Data: - If you're reading data from an Excel file, ensure that the data is correctly formatted and matches the expected number of columns. - Verify that the Excel sheet contains the right number of columns for your test method. Remember to validate the data provider, test method, and data source (Excel, CSV, etc.) to ensure consistency in the number of arguments. .

[@keshavdwivedi5192]

Raghav thanks for the easiest approach this was what i wanted but i have a doubt that if we have to test suppose by entering only username, so in this case we will keep password cell as blank but on keeping cell blank nullpointer is thrown so can we use like " " in password field instead of leaving it as blank will it work properly???

[@RaghavPal]

Hi Keshav, thanks for the message. You can try looking at how to get empty string from excel in java

[@keshavdwivedi5192]

Hi Raghav, I will try and let u know whether it will work or not

[@keshavdwivedi5192]

Raghav, i was also facing one difficulty in reading numeric values from cells as if in testdata method we write getstringcelldata() method so for cells having numeric values it gives us error that " cannot read string value from numeric cell" so in this case do we have to check the cell data type in the function getnumericcell() in the excel reading class or we need to check this condition in testdata function and secondly please tell how to check the datatype of cell?? Please help

[@RaghavPal]

yes for numeric values you will need to use getNumericCellValue(). It is explained in the previous video - kzbin.info/www/bejne/eYeWhIKlp6usmqc For doing action as per the data type of cell you can see a solution here - stackoverflow.com/questions/6508203/when-getting-cell-content-using-apache-poi-library-i-get-both-cannot-get-a-num

[@puneetoist1987]

Hi Raghav, Thanks for responding to other query. However I am stuck at another place now. What i am trying to do is . Using data provider - can i run multiple test methods? Lets say if i have 3 test methods in my test case. And i want to read data provider then go to first test run it , move to second test and then third and after that i want to read 2nd row from data provider. However it is looping all the rows for first test and then second and then third. What will be the solution for this?

[@RaghavPal]

Hi Puneet, I will need to confirm this with a hands-on, Can check some online examples too

[@azammousavian5211]

Hi Raghave, Is this the same for appium?

[@RaghavPal]

Yes, it will work on appium projects too

[@raviteja5029]

@RaghavPal could you explain me if Excel sheet having string and numerical data using data provider concept with data formatter.

[@RaghavPal]

Hi Ravi **Data provider** is a concept in automation testing that allows you to store test data in a centralized location. This makes it easy to reuse test data and to keep track of changes to the data. **Data formatter** is a tool that can be used to format test data. This can be useful for converting data types, removing special characters, and other tasks. To use data provider and data formatter with Excel sheet having string and numerical data, you can follow these steps: 1. Create an Excel sheet that contains the test data. 2. Identify the columns in the Excel sheet that contain string and numerical data. 3. Create a data provider that reads the data from the Excel sheet. 4. Create a data formatter that formats the data from the data provider. 5. Use the data provider and data formatter in your automation scripts. Here is an example of how to use data provider and data formatter with Excel sheet having string and numerical data: ``` import { DataProvider, DataFormatter } from 'some-data-provider-library'; const dataProvider = new DataProvider('my-excel-sheet.xlsx'); const dataFormatter = new DataFormatter(); const testData = dataProvider.getData(); const formattedData = dataFormatter.formatData(testData); // Use the formattedData in your automation scripts. ``` I hope this helps

[@raviteja5029]

@@RaghavPal thanks for the information

[@nitinbhagat9389]

Hi Raghav, I want to pass different Dropdown values for different sets using Dataprovider from excel sheet, but it is always taking the values of first row. Could you please suggest how to input different dropdown values using dataprovider. Scenario: 5 users having different birth_dates which will be filled using dropdown and values are coming from excel.

[@RaghavPal]

Hi Nitin, pls check this stackoverflow.com/questions/41503897/how-do-i-retrieve-a-specific-row-of-data-from-a-testng-dataprovider

[@puneetoist1987]

Hi Raghav, I keep getting an error - cannot get a string value from numeric cell. what is the best way to avoid that error?. I thought if i change the format in excel to text it will consider the value as string. But after changing that i am still getting that error. IS there a way to change the cell format to string and not worry about this error?

[@RaghavPal]

Hi Puneet, if your data is numeric, you will need to use a diff function to read excel. Check this - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@puneetoist1987]

@@RaghavPalThank you. i was able to proceed with this.

[@justinlambert7109]

Hi Ragav in this video (26.42 sec) you are entering 3 different data sets but without click Login button , how can we validate those data sets without click login, we should click and validate right ? what is way to improve code more

[@RaghavPal]

Yes, this was just for example to show how to get data from csv

[@justinlambert7109]

@@RaghavPal Thanks and how can I do that click and validate?

[@Deniz-ss1sv]

:)

[@nistan5404]

Hi Raghav , I m getting this error after duration 12:24 ------------------------------------------------------------------------------------------------------------------------- No of rows:2 No of columns:2 Admin Admin Cannot get a STRING value from a NUMERIC cell null null java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1035) at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:390) at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(XSSFCell.java:342) at Utils.ExcelUtils.getCellDataString(ExcelUtils.java:66) at Utils.ExcelDataProvider.testData(ExcelDataProvider.java:23) at Utils.ExcelDataProvider.main(ExcelDataProvider.java:12)

[@RaghavPal]

Hi, this should help you - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@vamshivishwanath]

@nistan5404 AT 12.24 iam getting null point exception please help buddy

[@kshipra4504]

Hi Raghav!! If I have say 20 columns, then instead of passing 20 parameters, can't we use Hashmap? Taking all the data from Excel to Hashmap and then using it in our TC. Please suggest if you can a video supporting such example.

[@RaghavPal]

Hi Kshipra, yes you can use

[@kshipra4504]

@@RaghavPal Thanks for prompt reply. A video supporting this alternative/scenario is much awaited!!

[@sanjibsarkar9565]

One query: If I declare a dataProvider in a test class, then all the @test method must use that dataprovider?? If not, then how to ignore it? I have tried but those @test get failing. If possible, please suggest. Thanks

[@RaghavPal]

Hi Sanjib, will need to check on this, can try some online examples meanwhile

[@9745754904]

Hi Raghav...if I HV testng, cucumber,POM, data driven using excel in my framework...wat type of framework will it be called

[@RaghavPal]

More likely a hybrid framework

[@9745754904]

@@RaghavPal thanks Raghav ,still.have doubt hw we called it as a hybrid as hybrid is data driven + keyword .. Correct me if I am wrong

[@RaghavPal]

Yes, so in your framework you are using cucumber can better call it BDD cucumber Data driven.

[@yuktibaheti621]

Hello, hope you’re doing good I have a query how can we process the data if the columns have string as well as numerical values I tried different ways but always get an error cannot get a NUMERIC value from string cell

[@RaghavPal]

Hi Yukti, pls check this kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@manikandana4599]

Hi Bro, How can we use the @DataProvider in the large data set?

[@RaghavPal]

Hi Mani, the process should be the same. Just to avoid a lot of I/O operations with file, I will suggest to get data in memory using collections like maps, lists etc and then use it.

[@salimlakhani3701]

Hello Raghav, i went through video selenium framework for Beginner 18 and it work perfectly for me. i went and tried beginner 19 and my for loop doesnt work. it doesnt gives me error but when i checked by debugging the script it shows me it goes to for loop and than skips it out what could be possible problem?

[@RaghavPal]

Hi Salim, will need to check logs for this

[@salimlakhani3701]

@@RaghavPal Thanks a lot Raghav for your help it did workout.

[@ahsanch3192]

Can we Input the data from dataprovider directly into methods calls in @Test methods,

[@RaghavPal]

Hi Ahsan, the process shown in the video is the ideal way, I have not tried other ways and will need to check, You can give it a try and see what happens

[@divyangjani7600]

can we use testdata method as dataprovider? because we just calling that method in getdata method so

[@RaghavPal]

You can use

[@jgraja2846]

Excellent but explanation is very fast please try to reduce Thanks

[@RaghavPal]

Noted, for now you can decrease the speed from the player settings

[@lakshmikanthlucky5559]

Hi Raghav, I followed your way to read the data from excel and its working fine, but i am facing issue only when I am trying to read numeric values. could you provide the solution for it.

[@RaghavPal]

Hi Lakshmi, Pls check this - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@lakshmikanthlucky5559]

@@RaghavPal Thanks Raghav, It helped me to resolve the issue

[@chitray01]

Hi Sir, I had the requirement similar to the one explained in the video. I just want to understand how do we integrate this data provider and method with the extent report as when we integrate the same with Listener Class then for each row same method will be running and in extent report same method name will be printed for every row execution. I wanted to know how do we print any parameter name which we are getting through excel as a Test Name. Thanks in advance. Your video was really helpful for my development but I am stuck at Extent Report. I am very new to framework development.

[@RaghavPal]

Hi Chitra, I will plan a session on this, for now can try some online examples

[@chitray01]

@@RaghavPal I have tried lot of options but I am not finding any solutions. For now I am just adding random number along with method name

[@RaghavPal]

I will get some info, will update here

[@chitray01]

​@@RaghavPal Sir did you get any information for the same ?

[@lifeindubaiwithtarang1804]

Hi Raghab, i am trying to run data provider as per ur video but i added below in xcel_reader utility Object data; if (cell.getCellTypeEnum()==CellType.NUMERIC){ data=sheet.getRow(rownum).getCell(columnnum).getNumericCellValue(); System.out.println(data); } else { data=sheet.getRow(rownum).getCell(columnnum).getStringCellValue(); System.out.println(data); } return data; } } *****************8888 in the main class from which i am trying to run , public class xceldatautility { public static Xls_Reader r; public static void main(String[] args) throws InterruptedException { xceldatautility x=new xceldatautility(); x.getdataexcel(); } public Object[][] getdataexcel(){ r=new Xls_Reader("mysheet", "C:/Users/Abhij/Documents/mydata.xlsx"); int row=r.getRowCount(); int col=r.getColCount(); Object [][] data= new Object[row][col]; for(int i=0;i

[@RaghavPal]

Hi, can you pls check and compare with this - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@rashmikataria3642]

Hi , If i create an Object [][] fun in the base class and but I want to fetch the data from excel in another class using page factory, how to do it? please help

[@RaghavPal]

Hi Rashmi, will do a session, can check some examples too

[@rajatshandilya1203]

Hi, Raghav After trying your code, I am getting " NegativeArraySizeException " in Test Data method on line Object data[][] = new Object[TotalRowCount -1][TotalColumnCount]; Can you help me with this ?

[@RaghavPal]

Hi Rajat, Sorry for late reply, NegativeArraySizeException occurs whenever an application tries to create an array with negative size. For example, whenever we assigned the negative size to an array. int[ ] arr=new int[-10]; , Here the array size is -10 i.e., negative size. You can try to print the TotalRowCount. I doubt you are getting a 0 value there

[@sandyblogs4568]

how to make a framework for selenium testng(using dataprovider) for keyword driven framework? same thing as in the vedio but for keyword driven

[@RaghavPal]

Hi Sandy, the testNG concepts and methods remain same there too. You can try and check some examples online

[@sandyblogs4568]

@@RaghavPal i have multiple test cases under single excel sheet. i am finding difficult in reading excel by test cases and executing using testng(parallel execution)

[@RaghavPal]

Ok, I will plan a session, for now try to see some online examples

[@sandyblogs4568]

@@RaghavPal Thank you very very very much bro can you please plan a session as early as possible at last your videos' are awesome bro

[@RajuDas-zn3gx]

Towards the end of video u had written selenium program. For three data there may be few valid login credentials and few invalid. In the same script how to implement different assertion statement for different scenarios?

[@RaghavPal]

In case the scenario is different than of course you will need different Test Cases for it.

[@RajuDas-zn3gx]

Do you have any video for this or r u planning to make some video for the same scenario? It will help a lot to understand to approach to achieve this scenario

[@RaghavPal]

will do it soon

[@RajuDas-zn3gx]

Thank you so much. Waiting for your video

[@raviteja5029]

Could you please help me how to read numeric and string Excel data using data provider concept

[@RaghavPal]

Hi Ravi To read numeric and string Excel data using data provider concept in TestNG, you can use the following code: ```java import org.testng.annotations.DataProvider; import org.testng.annotations.Test; public class DataProviderTest { @DataProvider(name = "data") public Object[][] getData() { return new Object[][] { {1, "one"}, {2, "two"}, {3, "three"}, }; } @Test(dataProvider = "data") public void testDataProvider(int number, String text) { System.out.println("number = " + number); System.out.println("text = " + text); } } ``` This code will read the data from the Excel file and pass it to the test method as a 2-dimensional array. The first element in the array is the numeric data, and the second element is the string data. To run the test, you can use the following command: ``` mvn test -Dtest=DataProviderTest ``` This will run the `DataProviderTest` class and the `testDataProvider` method. The `testDataProvider` method will be executed three times, once for each row in the Excel file. Here are some additional details: * The `@DataProvider` annotation is used to define a data provider. * The `@Test` annotation is used to define a test method. * The `getData()` method is used to get the data from the Excel file. * The `testDataProvider()` method is used to run the test. I hope this helps

[@raviteja5029]

@@RaghavPal thanks for the information

[@pratheepkumar4218]

Hai I'm facing an issue , how to register a company using JSON request in beta. I want to register ten company with different roles. How to register. Here beta means production environment. Is it possible to do a API or selenium how register 10times quickly .. can you please which approach I have follow.

[@RaghavPal]

Hi Pratheep, so basically you need to run a test or scenario with diff test data. You can do it through excel or directly, Can use testng. This will help - kzbin.info/www/bejne/enW0k4ZvmN5smLs

[@LiveAtTheMoment]

Why you create getData function?. We can provide @DataProvider annotation above Object [][] test Data() function itself right? Please answer

[@RaghavPal]

Hi, I believe have used @DataProvider in this or some other video, I will check

[@pratheepkumar4218]

Hi bro, i'm trying to automate using DDT. I got an error here. when i am using invalid user name and password its working fine. but when i'm using an valid email its getting an error. i found an error but i don't know how to fix. Boolean data type is capturing the error Modal is Displaying or not, if displayed the flow is working. if the modal is not display , its not going to logout. boolean is showing null. another problem is Testng is not working Before and After methods. package com.tg.Testcases; import java.awt.AWTException; import java.io.IOException; import java.util.IllegalFormatException; import java.util.concurrent.TimeUnit; import org.openqa.selenium.WebDriver; import org.openqa.selenium.WebElement; import org.openqa.selenium.chrome.ChromeDriver; import org.openqa.selenium.support.PageFactory; import org.openqa.selenium.support.ui.ExpectedConditions; import org.openqa.selenium.support.ui.WebDriverWait; import org.testng.Assert; import org.testng.annotations.*; import com.tg.Pages.LoginPage; import com.utility.ConfigReader; import io.qameta.allure.Description; import io.qameta.allure.Severity; import io.qameta.allure.SeverityLevel; import io.qameta.allure.Story; public class LoginValidation { public static WebDriver driver; ConfigReader config; LoginPage login; ListofRoleSelection roleSelection; @BeforeMethod @Severity(SeverityLevel.BLOCKER) @Description("Test Case Description: Verify Base Setup") @Story("Story Name: To check Base Setup File Configuration") public void setUp1() throws IOException, InterruptedException { config = new ConfigReader(); System.setProperty("webdriver.chrome.driver", config.getChromePath()); driver = new ChromeDriver(); driver.manage().timeouts().pageLoadTimeout(50, TimeUnit.SECONDS); driver.manage().timeouts().implicitlyWait(50, TimeUnit.SECONDS); driver.manage().window().maximize(); driver.get(config.getUrl()); String actualUrl = config.getUrl().trim(); String expectedUrl = driver.getCurrentUrl(); System.out.println("The Expected URL is : " + " " + expectedUrl + "... And... " + "The Actual URL is : " + " " + actualUrl); Assert.assertEquals(expectedUrl, actualUrl); } @DataProvider(name = "getDataFromExternalFile") @Severity(SeverityLevel.BLOCKER) @Description("Test Case Description: Verify getting UserName and Password from Excel Sheet ") @Story("Story Name: To check Excel Sheet Configuration") public static Object[][] getExcelData() throws IllegalFormatException, IOException, InterruptedException { ReadLoginCredentials getLoginData = new ReadLoginCredentials(); return getLoginData.readXLSFile("C:\\Users\\Pradeep\\Documents\\New_Excel.xlsx", "Sheet2"); } @Test(priority = 1, dataProvider = "getDataFromExternalFile") @Severity(SeverityLevel.BLOCKER) @Description("Test Case Description: Verify getting UserName and Password from Excel Sheet is Inserted or Not ") @Story("Story Name: To check User able to Login Successfully") public void verifyValidLogin(String UserEmailId, String userPassword) throws IOException, InterruptedException, AWTException { login = PageFactory.initElements(driver, LoginPage.class); login.enterEmailData(UserEmailId); login.enterPasswordData(userPassword); System.out.println("---->" + UserEmailId); System.out.println("---->" + userPassword); login.loginButton(); // Thread.sleep(1000); try { boolean loginPageErrorModal = login.invalidUserNamePasswordModalAlert().isDisplayed(); System.out.println("loginPageErrorModal" + loginPageErrorModal); if (loginPageErrorModal == true) { System.out.println("~~~~~~Invalid Credential~~~~~~"); String InvalidLogin = login.invalidUserNamePasswordErrorMessage().getText(); if (InvalidLogin.contains("Invalid Username/Password")) { System.out.println("Login Error Message is :" + " " + InvalidLogin); login.invalidUserNamePasswordOKButton().click(); } else { System.out.println("test"); } } else { System.out.println("~~~~~~Login Successfully~~~~~~"); logOut(); } } catch (Exception e) { } } @Test(priority = 2, enabled = true, description = "LogOut the Page") @Severity(SeverityLevel.NORMAL) @Description("To Verify the Logout Scenario : LogOut the tgWebApplication ") public void logOut() throws InterruptedException { @SuppressWarnings("deprecation") WebDriverWait wait = new WebDriverWait(driver, 7); wait.until(ExpectedConditions.elementToBeClickable(login.selectMenu())).click(); System.out.println("->>> Select the Menu Bar Logout the Page Successfully

[@RaghavPal]

Hi Pratheep, Its a bit difficult to study and troubleshoot code in youtube comments. Will need to look into your setup and logs.

[@emadrawashdeh4226]

What if I define the data provider in a class and place the test in other class. what I should do? could you show me a sample, please?

[@RaghavPal]

Hi Emad, you should be able to import and use it.

[@yogeshnarayanupadhyay7451]

sir I'm facing this issue [public void ExcelWork.ExcellDataProvider.test1(java.lang.String,java.lang.String) throws java.lang.Exception] has no parameters defined but was found to be using a data provider (either explicitly specified or inherited from class level annotation). Data provider mismatch Method: test1([Parameter{index=0, type=java.lang.String, declaredAnnotations=[]}, Parameter{index=1, type=java.lang.String, declaredAnnotations=[]}])

[@RaghavPal]

Hi Yogesh, Pls check the video again, if you are using data provider you need to provide the parameters in the function. Also check the spelling and syntax again

[@wasimakram-nz5gq]

Hi Raghav i have written 5Testcases and iam reading username and password from excel first time iam taking correct credentials second time iam taking wrong credentials , while running first time only one test case is executing remaining not running, can you please suggest anything overcome this problem. While i am giving only one data in excel then it is executing all 5 testcases

[@RaghavPal]

Hi Wasim, not sure why exactly this is happening, Pls check your loop again and refer all the steps again with the video

[@wasimakram-nz5gq]

Thanks for your reply

[@wasimakram-nz5gq]

I need your help like i have username and password in excel i need to fetch only valid user and password from Excel that to both should be in same row ,and send those value in proper webelement, i try many times not getting exact logic can you please help me to overcome this. Thanks in advance

[@RaghavPal]

You can do that and code accordingly. Can check this too kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@wasimakram-nz5gq]

Automation Step by Step - Raghav Pal Thanks for reply

[@rajwalad8471]

Hi Raghav, I have data.xlsx with 3 rows including header, but when i run my code, I get the below error --- Could you help me here No of columns: 2 No of rows: 3 FAILED: test1 org.testng.internal.reflect.MethodMatcherException: [public void utils.ExcelDataProviderMain.test1(java.lang.String,java.lang.String)] has no parameters defined but was found to be using a data provider (either explicitly specified or inherited from class level annotation). Data provider mismatch While this runs correctly when i have 2 rows including the header. Below is the result No of columns: 2 No of rows: 2 janisha--admin234 PASSED: test1("janisha", "admin234")

[@RaghavPal]

Hi Raj, check this stackoverflow.com/questions/45998857/data-provider-mismatch-in-selenium-with-testng

[@rajwalad8471]

@@RaghavPal Thank you Raghav Sir, definitely this will help me. Once again thanks for quick reply.

[@manjushagonjari554]

Hello, How to fetch data from excel which contains both string and numerical value....(Example : admin123)....because I am getting error... java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell

[@RaghavPal]

Hi Manjusha, you can use the function geNumericCell value, it is shown here kzbin.info/www/bejne/eYeWhIKlp6usmqc

[@201101120]

Hello Raghav, I am getting error Cannot get a STRING value from a NUMERIC cell . Could you please help us how to get a STRING value from a NUMERIC cell in

[@RaghavPal]

Hi Sanket, you will need to use a diff func. Pls check this stackoverflow.com/questions/30125465/cannot-get-a-text-value-from-a-numeric-cell-poi stackoverflow.com/questions/27688250/how-to-read-numeric-values-from-excel-in-java

[@CPLNet1]

@sanket i had same issue. easiest way is change data type at excel sheet. set everything to text.

[@wasimakram-nz5gq]

Hi Raghav How to find that the data from Excel is String Interger or Blank space

[@RaghavPal]

Hi, this can help - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@wasimakram-nz5gq]

Automation Step by Step - Raghav Pal if u have do any Mini project with all Concepts can u please share me link so it is very useful for me

[@wasimakram-nz5gq]

Automation Step by Step - Raghav Pal For(int i=0; i

[@RaghavPal]

because the count starts from 0 and not 1

[@ashishbhendey886]

How to get URL from From Single row row have more than 100 URLs one by one open this url and perform our test case

[@RaghavPal]

Hi Ashish To get URLs from an Excel sheet and open each URL one by one for testing in Selenium Java framework with TestNG, you can follow these steps: 1. Read the Excel sheet: Use a library like Apache POI to read the Excel sheet and extract the URLs. Here's an example code snippet to read URLs from an Excel file: ```java import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream; import java.io.IOException; public class ExcelUtils { public static Object[][] getURLsFromExcel(String filePath, String sheetName) throws IOException { FileInputStream fis = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(fis); Sheet sheet = workbook.getSheet(sheetName); int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum(); Object[][] data = new Object[rowCount][1]; for (int i = 1; i

[@gilsonsouza8159]

Hi Raghav pal, how are you? I have another question to you : ) Scenario - This is how my excel looks like: 1° column - Customer name 2° column - Customer type So there are many lines with this information! ------------------------------------------------------------------------- My automation will not need "ALL" the excel information. lets say that the automation will find the exact customer name in the web application. Then with this information (Customer name) I need to read the excel to discover the Customer type. My question is: - How can I search for the exact meet customer name in the excel (using Apache POI ore any other API). - After search, discover the index from the cell in the column 1 - And look to the same index but in the column 2 - Bring the content from the cell - column 2- and print it than I will be able to decide what to do E.g. If ( CustomerName == partner) { Send this email }Else{ Send a different email. } Can you help me with that ??? Thank you so much, your videos always help me a lot!!!!

[@RaghavPal]

Hi Gilson, once you are able to read and get the content from excel, you can add your conditions, for example if the customer name is found, record the row number and get value from the corresponding email column. This can help you - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@anhtester]

If data have 20 fields. How to handle it??

[@RaghavPal]

You can create 20 columns in your file and use them as needed in your script

[@dasarianveshitha7386]

When I run the tesng excel dataprovider I am getting Launching excel dataprovider has encountered a problem reference to undefined variable name

[@RaghavPal]

Ammu 1. Undefined Variable Name Error: - This error typically occurs when TestNG tries to resolve a variable name (such as an environment variable or system property) but cannot find it. - It might be related to how TestNG handles environment variables or system properties during test execution. 2. Possible Solutions: - Here are some steps you can take to resolve this issue: - 1. Disable System Property Variables: - In Eclipse, navigate to Preferences → TestNG → Maven. - Uncheck the option for systemPropertyVariables. - This step ensures that TestNG doesn't rely on system properties during test execution¹. - 2. Enable Environment Variables: - Check if the option for environmentVariables is enabled in your TestNG configuration. - Navigate to Preferences → TestNG → Maven and ensure that environmentVariables is checked. - If it's disabled, enable it and try running your tests again¹. - 3. Clear Cached Data: - Sometimes cached data can cause unexpected behavior. - Clear any cached responses or temporary files related to your TestNG project. - Restart Eclipse and try running your tests again. - 4. Verify Excel Data Provider: - Double-check your Excel data provider method. - Ensure that the method name matches the one specified in your TestNG test class. - If the method name is not explicitly specified, TestNG uses the method name as the data provider name by default³. - 5. Check Excel Formulas and References: - If your Excel file contains formulas or named ranges, verify that they don't have errors (such as #N/A, #REF!, etc.). - Incorrect formulas or references can cause issues with data providers². ..

[@dasarianveshitha7386]

Thank you 🙏

[@faisalsakib9586]

Where we used TestNg here ???..all the time we are using Main method. Is there any reason ???

[@RaghavPal]

Hi Faisal, the main method is the starting point and for demo, using a sep class I used it. You can follow the steps and can implement it anywhere you need in your framework.

[@AnilKumar-nq5lp]

You only used string data in the excel, what about the numeric data. If we have combination of numeric columns and as well as string columns, how todo that?

[@RaghavPal]

Hi Anil, pls check this - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@riteshmool9372]

sir, excel sheet me data alag position m ho to kaise likhenge loop m. maine bohot videos dekhe hai sab me same position me hi username passwword dikhaya hai. usko agar different position me rakh k koi video nahi mila

[@RaghavPal]

Hi Ritesh, show me an example, how data is stored

[@riteshmool9372]

@@RaghavPal if we store data in 10 number row and 8 number column in any middle of cell . Then how we can adjust the loop

[@RaghavPal]

I may not be able to understand exactly what you need here. Can get some help from here - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@iffatjahan9983]

it can't read numeric data from excell .how can solve!

[@RaghavPal]

Iffat Pls check this: kzbin.info/www/bejne/eGWqY6eDeb6diLMsi=OoKtCiP-qZQX1Dc2

[@sumityadav-zu2gu]

One problem is coming in your code. Cannot get a string value from numeric cell. Please help

[@sumityadav-zu2gu]

??

[@RaghavPal]

Hi Sumit, sorry for late reply. This will help - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@vpmani2659]

Hello All, [Error] java.lang.ArrayIndexOutOfBoundsException could some one please help me...

[@RaghavPal]

HI Mani, will need to see the logs and code where it is getting thrown from

[@vpmani2659]

@@RaghavPal Thank you so much Raghav i fixed it by watching your videos. Am big Fan of you...

[@vpmani2659]

@@RaghavPal Hi Raghav, I created data driven based on your video. where it contains one Data Provider and two test cases. I excel i have two column of data. When i execute the Test Case It runs 2 times of each method because it running based on the number of column, Could you please help me...

[@RaghavPal]

I will need to look into the setup

[@vpmani2659]

@@RaghavPal How can i share..Could you please tell me..

[@AnilKumar-sf4em]

My requirement is, dataprovider having {1}{2} data and I have 2 methods in a class, I want to pass {1} to first method and {2} to second method, Is it possible, Plz provide the solution. public void Dataread{ @DataProvider (name = "data"){ public Object[][] dataMethod(){ return new Object[][] {{"1"}, {"2"}}; } @Test (dataProvider = "data") public void test1 (String val) { System.out.println("Test1 : " + val); } @Test (dataProvider = "data") public void test2 (String val) { System.out.println("Test2 : " + val); } } Expected output: Test1 : 1 Test2 : 2

[@RaghavPal]

Hi Anil, i will need to check this

[@AnilKumar-sf4em]

@@RaghavPal Thanks