Do not over-complicate your code. Since your tests need different data, use two different data providers. One for each test method. Each data provider would provide its own set of columns. A bit of details now.. Assume you have this sheet: And you know how to read the entire data. Say you have the method private Object[][] readSheet(). Now you can create two providers: Taking the only first column: @DataProvider(name = "first") public Object[][] getFirstCol(){ Object[][] entireSheet = readSheet(); Object[][] part = new Object[entireSheet.length][1]; for (int i = 0; i < entireSheet.length; i++){ part[i][0] = entireSheet[i][0]; } return part; } ``` Taking the rest of columns: @DataProvider(name = "others") public Object[][] getOtherCols(){ Object[][] entireSheet = readSheet(); Object[][] part = new Object[entireSheet.length][TOTAL_COLUMNS - 1]; for (int i = 0; i < entireSheet.length; i++){ for(int j = 1; j < entireSheet[i].length; j++) { part[i][j - 1] = entireSheet[i][j]; } } return part; } ``` Having two providers would let you prepare more "clean" and reliable data for your tests. For example the length of different columns could be different, they might have different types and probably might need different pre-processing before feeding your test.

The solution that POI API implies is to use DataFormatter class. Using data formatter you can convert cell value to a string independently of what cell type is. Approximate code should look like (no switch block is required): Cell cell = row.getCell(vColumn); DataFormatter dataFormatter = new DataFormatter(); return dataFormatter.formatCellValue(cell) + "\t\t\t"; ``` Theoretically it should take the format that the cell in your spreadsheet has. Give this a try and let me know if it works.

I used to have a similar job; pulling down reports from different vendors in Excel and doing a pseudo-ETL. Excel Macros were great and I had one macro for each vendor. Still, I saved countless hours each week by learning macros.

As the attached image shows that user needs to browse the excel sheet.I was also faced the same situation. So, Assuming that request for the same has been recorded. Follow below instructions: Use beanshell post processor and write code to read the excel file. {It is similar to Java IO file read code} like this import org.apache.jmeter.threads.JMeterVariables; import java.io.File; import java.io.IOException; File data = new File("/path/to/your/file.xlsx") In that code assign "data" object to the JMeter variable like this vars.put("A1", data.getAbsolutePath()); Pass variable "A1" to the request recorded for browsing the file. Taken reference from How to Extract Data From Files With JMeter This might helps you.

First of all you have to understand that UI stands for just an User Interface. So this is some way that allows a user to interact with your application (so, when you were interacting with your tests using Excel - Excel was a sort of User Interface that time). There are few ways which have their own advantages and disadvantages: Command Line Interface (a.k.a. CLI). There are two main advantages here: it is the most easiest interface to implement and it will run on the system without graphics environment (that basically means you will be able to run it in any command line processor). It also has a disadvantage: It does not allow you to well structure a user input. Desktop GUI. There are some pro's for this choice. It allows you to structure your input data for the user, use complex controls like combo-boxes, radio-buttons, etc, make user flow more representative. It also allows to integrate your GUI with the applications which are running within the same environment your GUI is. For example you can make your GUI app call your tests using the data a user has provided via the interface. There are some disadvantages. The main disadvantage is that it requires some programming skills which are beyond the basic ones. Since GUI apps require understanding of multi-threading conceptions and inter-threading communications. Web-GUI. Pro's: Web GUI unlike the desktop one is usually more standardized and probably a bit more easy for implementation. It also allows you to share your app for different users across the network. So several people would be able to provide data for your tests. The disadvantage for this approach is that you will be running your app remotely (unlike the desktop app when you are running your app on your local machine) and this means that the test code has to be running remotely as well. There is a corner case when you run your web app on your machine but this is not the idea of web applications. Here is the simple tutorial for creating Java-based GUI More complicated tutorial on creating a simple web form app for web The idea is basically the same for all UI types. You provide the fields for the user, user fills them in, you get the values (almost like you did with Excel) and propagate them to your test code.

Not clear what "[Bunch of test data]" looks like so this is hard to answer. I'd likely have each document include the other items you mention as keys and index those fields so you can query against them quickly. If "Bunch of test data" is JSON, you could achieve similar results using a modern DB that supports JSON (e.g. Postgres or SQL Server) Whether you shuld use SQL or NoSQL is a much bigger question.

It would be better if you write scenarios for each column at least one. The reason beyond is the export file contains the banking pieces of information so there must be some condition and rules to display the data format in the excel. Ex: Amount columns may contain the decimal values so it needs to be represented in such format. Cover some multiple test cases for most highlighted fields like account no, amount etc.. Sometimes the special characters will display in the format of junk

" ` " . Add this special character present in the quotes. Since the mobile number field is an integer this error is throwing. If you add that special character then the cell will became as string.

If you would like to return only 1 specific row then you have to pass the row number as a parameter to the function and also stop using the first loop and your object[][] array will be of size object[1][columnSize]. please check this code. public Object[][] realExcel(String file , String sheetName, int columnSize, int rowNumber) throws IOException { @SuppressWarnings("resource") XSSFWorkbook excel = new XSSFWorkbook(file); XSSFSheet sheet = excel.getSheet(sheetName); int rowSize = sheet.getLastRowNum(); String xslData1; double xslNumericData; XSSFCell checkData; if(rowNumber <= rowSize){ Object[][] data = new Object[1][columnSize]; for (int j = 0; j <= columnSize; j++) { checkData = sheet.getRow(rowNumber).getCell(j); if (checkData != null) { CellType type = sheet.getRow(rowNumber).getCell(j).getCellTypeEnum(); if (type == CellType.STRING) { xslData1 = sheet.getRow(rowNumber).getCell(j).getStringCellValue(); data[0][j] = xslData1; } else if (type == CellType.NUMERIC) { xslNumericData = sheet.getRow(rowNumber).getCell(j).getNumericCellValue(); data[0][j] = xslNumericData; } else { } } } }else { // report that the selected row number is out of the range } return data; } This function will return a object[][] with specific row number, So after getting the required object[][], Apply your validations if it meets your needs or no, Then if no select the other row.

You don't seem to be checking the values from your spreadsheet import or if you have successfully opened it , this would then result in you searching for null or unexpected values which will cause selenium to return service errors. You could put some print statements or asserts in to help you check but really I'd suggest putting the code that does the data extraction in a separate class and getting that working so it can fetch the values you expect. You could write some simple unit tests to display that data , then when your class works enough to pass it's tests you can make your original code use the new class. You could also polish that class a little more so it can be used as a test fixture in other tests that might need to read from Excel. Excel is a very complex and changeable format , if you are only reading one sheet of data from it you might find it easier to export to CSV and parse that instead.

This may be helpful to you.. Open the Excel sheet and select all (Ctrl+a) then right click and click Format cells then in number tab change the category to Text and click Ok...

This is the answer working fine for me public class LineMatcher { static Set<String> linkedHashSet = new LinkedHashSet<>(); public void findBadLines(String aFileName) { Path path = Paths.get(aFileName); try ( BufferedReader reader = Files.newBufferedReader(path, ENCODING); LineNumberReader lineReader = new LineNumberReader(reader); ) { String line = null; while ((line = lineReader.readLine()) != null) { line = line.concat("&"); Pattern pattern = Pattern.compile("=[^&]*&"); Matcher m = pattern.matcher(line); while (m.find()) { String str = m.group(); String cc = str.substring(1, str.length() - 1); if (cc.isEmpty()) break; line = line.replace(cc, ""); line = line.replace("=&", "+"); m = pattern.matcher(line); } linkedHashSet.add(line); } } catch (IOException ex) { ex.printStackTrace(); } } public static void main(String... arguments) { LineMatcher lineMatcher = new LineMatcher(); lineMatcher.findBadLines("C:\\Users\\Desktop\\New folder\\api.csv"); for (String i : linkedHashSet) { System.out.println(i); } } Out Put(removing duplicates): http://abc.com/XMLFeed?clientkey+type+ProductID+location+ http://ghy.com/XMLFeed?clientkey+type+ProductID+

Based on your question, the answer is to inform your superiors that their choices are for you or someone else to spend some huge amount of time manually entering the results (assuming you have been able to import test cases), or they can have someone with the proper programming knowledge uses the HP API to generate the import(which also could be a huge amount of time depending on how much data mangling and programming you need). If this is a one-time data entry, there's a fair chance someone is going to find themselves manually entering a lot of data. If you're going to need to import test result data on a regular basis, you have a better chance that programming resources will be available. Regardless, at this point the decision needs to be made by someone further up the hierarchy than you because you don't have the access to make the decision.

Here is the Attributes: Data Source Types and Attributes Excel, ".xlsx" format [DataSource("System.Data.Odbc", "Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\data.xlsx;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true", "Sheet1$", DataAccessMethod.Sequential), TestMethod] where "data.xlsx" is the Excel file you want to use as Data Source, "Sheet1$" is the worksheet you are using in the Excel file. Using this to replace the normal [TestMethod] attribute before the test method. If this is not working, check the following: Add "data.xlsx" Excel file to your CodedUI Project: Right Click Project -> Add -> Existing Item... -> Add Check "data.xlsx" Properties in VS, make sure to set "Copy to Output Directory" Property to "Copy always" or "Copy if newer" If you get a connection error, ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified it's most likely due to the fact that you don't have the 2007 Office System Driver installed for the OLEDB provider. (Thanks to blog.danbrust.net and incyclesoftware), you can download it from the following Microsoft link: Link 2007 Office System Driver: Data Connectivity Components This will install the Office 2007 drivers. Since the Access and Excel file formats haven’t changed since 2007, these drivers work just fine with Office 2010, 2016, etc. Another comment for CSV file: if CSV file is not working, check the encoding. It should be ANSI or UTF-8 without BOM.

Use file extension to handle WorkSheet Type String inputFilename = new File(path).getName(); switch (inputFilename.substring(inputFilename.lastIndexOf(".") + 1, inputFilename.length())) { case "xls": return readXLS(path); case "xlsx": return readXLSX(path); default: Log.e(TAG, "No XLS file chosen"); return "Please select valid \"Excel\" File\""; } For XLSX file: use XSSFWorkbook & XSSFSheet XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(path))); XSSFSheet sheet = workbook.getSheetAt(0); For XLS file: use HSSFWorkbook & HSSFSheet HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(path))); HSSFSheet sheet = workbook.getSheetAt(0); Refer the link for a more detail