TestNG Data Provider - How to get test data from Excel in Selenium using TestNG Data provider

TestNG Data Provider with Object Array Approach:
1. What is paramterization
2. What is data provider (@DataProvider)
3. What is two dimensional Object array (Object [][])
4. Test Case parameterisation with Excel
5. Get Data from Excel using APACHE POI API
~~~~Subscribe to this channel, and press bell icon to get some interesting videos on Selenium and Automation:
kzbin.info%20Au...
Follow me on my Facebook Page:
/ naveenqtpexpert
Let's join our Automation community for some amazing knowledge sharing and group discussion:
t.me/joinchat/COJqZQ4enmEt4JA... Subscribe to this channel, and press bell icon to get some interesting videos on Selenium and Automation:
kzbin.info%20Au...
Follow me on my Facebook Page:
/ naveenqtpexpert
Let's join our Automation community for some amazing knowledge sharing and group discussion on Telegram:
t.me/joinchat/COJqZUPB02r5sB7...
Paid courses (Recorded) videos:
Java & Selenium Course: www.naveenautomationlabs.com/p...
API Course: www.naveenautomationlabs.com/p... ➡️Get Our Courses✔️
📗 Get My Paid Courses at
Paid courses (Recorded) videos:
Java & Selenium Course: www.naveenautomationlabs.com/p...
API Course: www.naveenautomationlabs.com/p...
-------------------------------
✔️SOCIAL NETWORKS
Facebook: / naveenqtpexpert
Twitter: / naveenkhunteta
Blog: www.naveenautomationlabs.com
--------------------------------
Support My Channel✔️Or Buy Me A Coffee
Paypal: paypal.me/naveenkhunteta
Google Pay: [e-mail]
--------------------------------
✔️Thanks for watching!
देखने के लिए धन्यवाद
Благодаря за гледането
感谢您观看
Merci d'avoir regardé
Grazie per la visione
Gracias por ver
شكرا للمشاهدة

[@4malayali]

I am following your videos from june 2019.. covered topic core java ->selenium basics->POM->Testng->and interview question in Testng and Selenium . And i glad to announce that i got Job in Selenium Automation with java.Augest 2019. Once Again Thanks For Your All Videos. God Bless You..

[@chintanshah9532]

Thanks Naveen. This was very helpful in understanding Data Provider annotation and its implementation.

[@vrushaligholap6647]

Thank you very much for sharing very informative video on TestNG dataprovider

[@muzaffarraja4247]

Thank you so much Naveen you really done great job, it is very helpful

[@somashekar-5273]

Thanks a lot for this video.

[@Mr_TravelEatWorship_Official]

Superb naveen...tq

[@rohitpurandare3556]

Thanks mate!!

[@veganforlife4699]

30:55 the sequence that we need to remember

[@sriramnagarajanit]

thank you so much , this was the topic I'm looking for long back :)

[@ravik3759]

please provide solution Dataprovider trying to pass one parameter but method takes 3

[@shakelahmed9722]

Thanks a ton Boss.

[@DhivagarT]

Superb,it works.......Thanks

[@Jayakrishna-ly3ei]

tried udemy code from morning which has 500 lines of code. i typed this code , it did the work,. if possible please add a link which has code for each and every program. Thank u so much sir

[@mysterynaren]

Hi Naveen, I am following many of your videos and they are really helpful, easy to understand and very informative. Can you please provide me the basic idea on automation on guidewire policy center application asap.

[@GOKULKRISH2302]

Hi Naveen, your video is really helpful☺️, In this tutorial u hv only passed text values as parameters, Is it possible to use check box & radio buttons also, if so hw can we pass their values in excel. Thnx in advance ☺️☺️

[@vive121]

Hi Naveen, thanks for posting the tutorial. I am working on 2 scenarios. Could you please suggest or create a tutorial? Scenario - 1 : 1) Consider LOGIN details are common and needs to be loaded / run everytime. So I will have this details under LOGIN.xlsx 2) Now whenever I run TC1.xlsx this will be called from inside the TC1.xlsx Scenario - 2: 1) Have test cases placed in User Dir. Sequentially launch the xlsx. 2) Ex: When TC1.xlsx is completed it should load TC2.xlsx and so on.

[@Torkmstr]

Thanks man..It helped me !!

[@MsMainhoona]

Hi Naveen, I have a query, if the eXcel sheet have multiple type of data , why are we keeping all as string type in the data provider methid arguments, cant we keep them as they are, for ex , if its some percentage ,cant we keep the parameter as float?

[@3vedinishit]

Hi Naveen, your explanation is so simple that .. it is very easy to understand. I have doubt if i have more inputs say 7 Rows(inputs) and 11 columns, how to handle . Please explain i have tried one of your example having more inputs. but not working.

[@kalyanijoshi6884]

hi naveen .is it.possible to use multiple data provider in single class for different tests. I am getting a null pointer exception in case of 2nd data provider

[@curious6899]

Sometimes there are version conflict - for example, my old script won't work with Chrome because Chrome is updated. So what combination of versions we should use for Selenium WebDriver + Chrome + Firefox + Eclipse? Which version of Eclipse works best with all?

[@bhavinmistry9913]

Hi Naveen, Thank you for posting all the videos on Selenium and Core Java. I have gone through a lot of videos , but i found yours the best from basic to advanced methods. I needed your one help for this video. I have only 1 Column with multiple rows in the excel. How to read all that data and send it to Object. Should i change it to single dimensional object ?

[@kannykanny1]

I think you need to still use 2 dimension object array wherein your row will increase but column will stay 0

[@nehalnz1823]

Hi Naveen What if I don't want to fetch data repeatedly means I want to fetch particular cell value of excel then which method can be used?

[@srabaniadhikary318]

Hi Naveen. How can I read Particular cells and columns data using data provider?

[@greenpeace041]

Thanks Naveen : I want to pull URL also from Excel Sheet how Can I do that do I need to do 3 object array in place of two object array . Please help

[@mohamedmabrouki6267]

Hallo Naveen ,what if there are more than two columns in my Excel table ? Thank you very much

[@mahendratawade606]

Is possible to change dataprovider runtime or can we fetch the data from 2 sheet in single object ?

[@pranabn6755]

Thanks

[@nivedithareddy9319]

Hi navven, Thanks for sharing, can u share if one use case is failed in above program how to handle that kind of scenarios

[@naveenautomationlabs]

Just use the Dataprovider and pass the negative test data.

[@funnysides3898]

if i dont want to execute after method each and every time i.e. closing browser each and every time, and i want to enter all the test data in one shot one by one and that too after entering one batch clearing and again entering usin for loop untill the end of the last row so how can we do that

[@ashuforh35]

hi naveen.. really nice vdo & teachng is awsm as always.. 👌 but if we want to use same login method of login for +ve & -ve scnario with assertion how can we do it by putng assertion in it using dataprovider? can u giv a hint plz?

[@dhananjayapattnaik7428]

Whenever I type workbook factory.create() a lot of crate method is showing so which one should I use.

[@saikatdhar8689]

how to implement it in framework

[@bheemeshmanikonda8181]

Hello Naveen , if number of columns are huge , then there is a concept of store entire column data in hashmap. Anyone solution for this scenario

[@anjanpersonal]

what if we have more than 2 columns data in excel, Do we still need to give 2 dimension array? if so how it works?

[@durgaraotdr4784]

once we saved in notepad, is it required to update the version of apache dependencies...

[@gamingandfacts8164]

Hi,lets say the @dataprovider method is written in different class then how to handle it?

[@001jyothi]

If we want to pass combination string integer double value then how can object array handle

[@mariapeterchrisalagose2204]

Hi Naveen, if we have two testcases for login page and we have 2 different set of testdata(for ex 1st testcase contains testdata: Title, Name, Country and 2nd testcase contains testdata : Name, Address, PhoneNumber) then, can we access the data for these 2 testcases for login page from the same sheet Login within the excel or we need to create different sheet for the 2nd testcase ? Can you please help

[@iR0b005]

Naveen can you please share the link to where you have all these files and code. The video is very helpful thanks.

[@97Ryuuku97]

static WorkBook book; static Sheet sheet; public static String TESTDATA_SHEET_PATH = "" public static void getTestData(String sheetName){ FileInputStream file = null; try{ file = new FileInputStream(TESTDATA_SHEET_PATH); }catch (FileNotFoundException e){ e.printStackTrace(); } try{ book = WorkBookFactory.create(file); }catch(InvalidFormatException e){ e.printStackTrace; }catch(IOException e){ e.printStackTrace(); } sheet = book.getSheet(sheetName); Object[][] data = new Object[sheet.getLastRowNum()][shet.getRow(0).getLastCellNum()]; for (int i = 0; i < sheet.getLastRowNum();i++){ for(int k = 0; k < sheet.getRow(0).getLastCellNum();k++){ data[i][k] = sheet.getRow(i + 1).getCell(k).toString(); //System.out.printlt(data[i][k]); } } return data; }

[@atulyadav958]

Hello Sir, I am trying to use two dataprovider by this method by making the two arryalist with different static method in one class and the sheet name is different but the sheets are in same workbook but not able to pass the second dataprovider data in desired test without using dataprovider if i pass single value then the data is accepted by the webpage and the script is running by after providing the data provider the test is not executable showing the insertion error and also if i mam using the same in diffrent class again the test is not able to perform by second dataprovider but working with the firstone. Please give me some suggestion to come up with this. Thanks

[@yatindersingh2033]

Hi Naveen, i am using your tutorials for my self learning and they are very much helpful. i was trying working on the data provider, but i am getting null point exception. Could you please help me with the same.

[@HealthyYouHealthyLife]

I also get the same error have u got any solution

[@sanbethel1]

Hi Naveen, I have two sheets in my excel workbook. My test method is a loginTest. I am trying to use the same method and data provider for both the sheets. How can I do this? Right now I am using different data providers for different sheets @DataProvider(name="dealershipLoginData") public Object[][] getDealerLoginURLData() { Object[][] dealershipURLData = null; String excelPath=projectPath+"/src/main/resources/testData/CustomerLinks.xlsx"; ExcelUtils excelUtils = new ExcelUtils(); try { dealershipURLData = excelUtils.getTestData(excelPath, "Sheet3"); } catch (Exception e) { e.printStackTrace(); } return dealershipURLData; } Is there anyway I can make the sheet name as variable and then how I be able to use it in the test method?

[@kannykanny1]

Hi Naveen- 27:24 In interview yesterday I was asked How do you read a specific row from excel? I too didn't expect this to be asked. In future can I just show them how to fetch row or do I need to show passing it to data providers too. It seems like really long answer to explain everything. Can I just show how to fetched rows and use sendkeys() to populate values rather than through data providers.. Please advise.

[@riaaggarwal6993]

How to use one data provider for all test cases? And I want to pass the sheetname in data provider for all test cases.how to achieve that?

[@JagadeeshJayachandran]

HI Naveen How to avoid the first column from reading, for example, I have filled my first column as TC1, TC2, TC3 etc., should I remove them.

[@nishantbelpande4909]

Same case I have

[@userdeleted2420]

Say I have two suites, Test1.xml and Test2.xml and two Excel files, file1.xls and file2.xls. When I run test Test1.xml, it should pick file1.xls

[@kshipra4504]

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.

[@sharithadoss8245]

Thanks for the video! I'm getting some error in Testutil - sheet methods... "The method getLastRowNum() is undefined for the type Object".. Am I missing something? Plz help me.

[@sainane9138]

how can i get your selenium vedios (orderwise)

[@KartikPandaVlogs]

what happen if we have multi numbers of prioritized methods , then at that moment how we gonna give priority to data provider method . please explain.

[@Kranthi233]

By .toString() method you are converting the excel data into string to store in object array ..but I want to pass the numeric data to dataprovider ..

[@anbu8006]

did you find the solution for your question? plz help me

[@navjyot_ironman]

Thanks Naveen !! My Notes on this session are : Problem statement : User has to read data from Excel sheet using DataProvider in TestNG framework Steps: 1) Create Excel sheet to store Data 2) Create Excel Utility to read data from Physical Excel sheet. 3) Call Excel Utility in method of DataProvider 4) Give the name of DataProvider to @Test Method so that it can read data from Object Array of DataProvider. Also you can give name to DataProvider and if its not given then it will use the method name on which @DataProvider is written. TestUtil.java: It has Excel sheet name here to read data. It has logic to read data from Excel sheet and store that data in Object Array. DataProvider is calling method in TestUtil.java and reading data and storing it in Object Array. @Test Login Test Method is getting data from DataProvidder and then it is printing this data one by one. Make sure that @Test Login Tst method has same parameters as per dimensions of Object Array i.e. it should match with number of columns in excel sheet where actual data is stored.

[@nukarajubhuvana4041]

Sir i have done same as you did, but i am getting java.lang.String error..how to solve this

[@nikhildevakar9155]

hai sir, what if i have multiple sheets in the same excel like sheet1,sheet2,sheet3 .........and i have to traverse to the third or second sheet in the same excel and get the data then the code is not working for me, it is throwing an error... please help me in this scenario

[@munmunkundu1792]

Hi Naveen, Will the Logic Implemented in the Util File get all data if we have data in more than 2 columns?

[@naveenautomationlabs]

Yes same logic will work.

[@mohan201430]

Hi Naveen i am getting these type of error SKIPPED: plLoginPage org.testng.TestNGException: Method plLoginPage requires 2 parameters but 0 were supplied in the @Test annotation.

[@prabeshkumarbhagat2989]

Go through the whole video you will get to know the solution for Your error

[@roliapanna8097]

Hi Naveen! can you please share the link to where you have all these files and code. The video is very helpful thanks

[@97Ryuuku97]

static WorkBook book; static Sheet sheet; public static String TESTDATA_SHEET_PATH = "" public static void getTestData(String sheetName){ FileInputStream file = null; try{ file = new FileInputStream(TESTDATA_SHEET_PATH); }catch (FileNotFoundException e){ e.printStackTrace(); } try{ book = WorkBookFactory.create(file); }catch(InvalidFormatException e){ e.printStackTrace; }catch(IOException e){ e.printStackTrace(); } sheet = book.getSheet(sheetName); Object[][] data = new Object[sheet.getLastRowNum()][shet.getRow(0).getLastCellNum()]; for (int i = 0; i < sheet.getLastRowNum();i++){ for(int k = 0; k < sheet.getRow(0).getLastCellNum();k++){ data[i][k] = sheet.getRow(i + 1).getCell(k).toString(); //System.out.printlt(data[i][k]); } } return data; }

[@aliridhohabsyi1242]

Hi Naven I have follow your instruction but got the problem with java.lang.RuntimeException: java.lang.NullPointerException . I have debug it on TestUtil.java, for sheet it's null. Can you help me to fix this problem?? Thanks

[@hondahnesscb3505]

@naveen facing same issue

[@venkateshkv9654]

How to handle blank cells ??

[@vpmani2659]

Dear All, If we want to test the multiple test case with one data provider so that from excel one column for one test case could you please help me on this...

[@sangeethasm6576]

Hi i am getting error as cannot locate element as text when i use data providers

[@AnilKumar-up5qx]

Has anybody used it successfully. Hello Naveen Sir, I used this code but i am getting a null pointer exception consistently.

[@albertobetofrias]

I run it but as soon it gets to enter the data close and show skip on the results =/, any idea hehe thanks!

[@cooper6470]

I followed the same procedure as in the video..but when I took my password in Excel as 123456..it is taking it as 123456.0 during test execution...any help?

[@sagarsjdjfn]

I had 1 IMP question - how to use multiple dataprovider in one testsuit -so that i can cover multiple testcases in one. ?

[@sainiranjan40]

Hello Naveen, As I run on local I have no issues but when I upload to Azure DevOps and run remotely, build failed with below errors. I was wondering why Azure DevOps failing to download the packages? Any suggestion please! [INFO] Compiling 32 source files to d:\a\1\s\ExceleRATE\target\classes [INFO] ------------------------------------------------------------- [ERROR] COMPILATION ERROR : [INFO] ------------------------------------------------------------- [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[8,35] package org.apache.poi.ss.usermodel does not exist [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[9,37] package org.apache.poi.xssf.usermodel does not exist [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[10,37] package org.apache.poi.xssf.usermodel does not exist [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[18,9] cannot find symbol symbol: class XSSFWorkbook location: class com.qa.ExceleRATE.util.ReadExcelData [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[20,9] cannot find symbol symbol: class XSSFSheet location: class com.qa.ExceleRATE.util.ReadExcelData [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[31,34] cannot find symbol symbol: class XSSFWorkbook location: class com.qa.ExceleRATE.util.ReadExcelData [INFO] 6 errors

[@umaa2115]

Hi Naveen can you share the code for Testutil class?

[@97Ryuuku97]

static WorkBook book; static Sheet sheet; public static String TESTDATA_SHEET_PATH = "" public static void getTestData(String sheetName){ FileInputStream file = null; try{ file = new FileInputStream(TESTDATA_SHEET_PATH); }catch (FileNotFoundException e){ e.printStackTrace(); } try{ book = WorkBookFactory.create(file); }catch(InvalidFormatException e){ e.printStackTrace; }catch(IOException e){ e.printStackTrace(); } sheet = book.getSheet(sheetName); Object[][] data = new Object[sheet.getLastRowNum()][shet.getRow(0).getLastCellNum()]; for (int i = 0; i < sheet.getLastRowNum();i++){ for(int k = 0; k < sheet.getRow(0).getLastCellNum();k++){ data[i][k] = sheet.getRow(i + 1).getCell(k).toString(); //System.out.printlt(data[i][k]); } } return data; }

[@prashantsharma1242]

Naveen how can I get this tetsiutil.java code?

[@RKWORLD66]

Sir how to compare two CSV files First contains Payments with User UID second contains Sum of Payment with same User UID I need to compare Second CSV carries Sum of Amount is matched with first csv.

[@InduMusuvathiDevendran]

how to read only one row from the excel sheet

[@devshah8205]

can i get testutil code

[@ERDheerajmann]

I do not understand why we need TDD if we are expecting same results with all credentials there should be approach like: Usertype Email pass canlogin valid username1 pass1 true invalid username1 pass1 false Now each test iteration should have its own validation

[@anagosztonyi]

all the examples are simpre : email and´password. I have issues with numbers but there are no examples with them

[@naveenautomationlabs]

With numbers also concept will remain same. No change in the code.

[@nishantbelpande4909]

Hi Naveen I want to skip 2 column data from excel using data provider but don't want that column name in method as this parameter is not in use so want to skip this column is any way to handle this ?