Automation Testing: Excel to SQL Query Creation
Objective
We’re looking to automate regression testing data preparation by populating data for different scenarios from Excel spreadsheets to a database table.
Suppose you need to verify HRA calculation of an employee based on her base salary with the following conditions:
- If the employee lives in State 1, the HRA is 50% of base salary
- If the employee lives in State 2, the HRA is 40% of base salary
- If the employee lives in State 3, the HRA is 30% of base salary
Normally, we would need to generate this data each time we wanted to verify different scenarios. Instead, we will create an Excel spreadsheet using this data and run a program to get insert queries. Generating data using this automated technique can save a lot of time.
Problem Statement
Automating data preparation for the regression process can be completed in the following four steps:
- The QA engineer inputs and updates various test case scenario data in an Excel spreadsheet
- The QA engineer places these Excel spreadsheet files into a pre-defined folder location, which is later processed with the Java program
- The Java program picks and processes the Excel spreadsheets and creates SQL input queries with the data in each spreadsheet
- The Java program writes SQL insert queries into a text file
A step-by-step solution
1. Write a simple Java program
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) {
// Write the logic to process excel
//spreadsheet.
}
}
2. Write functions to:
- Pick up the Excel file from a predefined location and read all of the data
- Map Excel columns to a defined table in a database
- Create SQL insert queries
//Read the data from Excel and Create Insert Query//
public String ReadExcel2010AndCreateQuery(String excelFilePathWithFileName, int excelColumnLength)
{
try {
//Load excel file content into memory
//excel processing start
FileInputStream file = new FileInputStream(new File(excelFilePathWithFileName));
//constructor call
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
String tableName = sheet.getSheetName(); //it's actually our table name
String columnNamesPlaceHolder = "£££";
String columnValuesPlaceHolder = "$$";
// insert into tablename(column1,column2) values (1,2)
String insertQueryTemplate = String.format("INSERT INTO " + tableName + "(" + columnNamesPlaceHolder + ") VALUES (" + columnValuesPlaceHolder + ");");
//To store each created insert query
StringBuilder allInsertQueriesList = new StringBuilder();
//to store our column name for ex: columnName1, column Name2…..so on
StringBuilder columnNamesBuilder = new StringBuilder();
//Iterate through each row in excel sheet one by one
Iterator<Row> rowIterator = sheet.iterator();
int currentRow = 1;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
String tempInsertQueryTemplate="";//only declaration
StringBuilder currentRowValuesBuilder=null;//only declaration
if (currentRow > 1) {
tempInsertQueryTemplate = insertQueryTemplate; /// Insert into tableName (columnName1, columnName2) values ( '$
3. Write functions to:
- Append these SQL queries in a text file
- Drop the text file with SQL insert queries into a folder location
//This functioon provides a unique file name //
public static String GetUniqueFileName() {
DateFormat dateFormat = new SimpleDateFormat("dd_MMM_yyyy-HH_mm_ss");
Date date = new Date();
return "Insert_Queries_" + dateFormat.format(date) + ".txt";
}
public void WriteToFile(String textFilePath, String contentToWrite) {
try {
String textFilePathWithFileName = textFilePath + GetUniqueFileName();
// Creates a link with text file
FileOutputStream fileOutputStream = new FileOutputStream(textFilePathWithFileName);
// Creates a link with fileOutputStream which internally has a link with file.
PrintStream out = new PrintStream(fileOutputStream);
out.println(contentToWrite);
out.close();
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
}
4. Update the main function to make a call to these functions
//Startup function for the program//
public static void main(String[] args) throws SQLException, ClassNotFoundException {
String excelFilePathWithFileName ="F:\\AutomationExcelToInsertQ\\ExcelFiles\\excel_data_2010.xlsx";
String textFilePath = "F:\\AutomationExcelToInsertQ\\ExcelFiles\\";//Only file path
int excelColumnLength = 57;
//Create object
ExcelUtil excelUtilInstance = new ExcelUtil();
//Call method ReadExcel2010AndCreateQuery for excel 2010 ( .xlsx format )
String AllInsertQueries= excelUtilInstance.ReadExcel2010AndCreateQuery(excelFilePathWithFileName, excelColumnLength);
excelUtilInstance.WriteToFile(textFilePath, AllInsertQueries);
Class.forName(DRIVER_CLASS);
//step2 create the connection object
Connection con= DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
}
Conclusion
Though it may be an endeavor to write this logic, once these functions are in place, your regression tests can be completed much more quickly. You only need to update your Excel spreadsheet with each new scenario and then run this program to complete the remainder of your testing.
The code is available here: https://github.com/kkapoor84/Automation_Excel_Query_Creation
);
currentRowValuesBuilder = new StringBuilder();
}
//For each row, iterate through all the columns
for (int j = 0; j < excelColumnLength; j++) {
Cell cell = row.getCell(j);
DataFormatter df = new DataFormatter();
//convert row to string
String currentCellValue = df.formatCellValue(cell);
if (currentRow == 1)//means it is our column name row
{
//column name retrieval start
if (j == excelColumnLength – 1) {
columnNamesBuilder.append(currentCellValue);
} else {
columnNamesBuilder.append(currentCellValue).append(", ");
}
} else {
//column values retrieval start
if (j == excelColumnLength- 1) {
currentRowValuesBuilder.append("'").append(currentCellValue).append("'");
}else {
currentRowValuesBuilder.append("'").append(currentCellValue).append("',");
}
}
}
if (currentRow == 1) {
/// Insert into tableName ( £££ ) values ( '$3. Write functions to:
- Append these SQL queries in a text file
- Drop the text file with SQL insert queries into a folder location
4. Update the main function to make a call to these functions
Conclusion
Though it may be an endeavor to write this logic, once these functions are in place, your regression tests can be completed much more quickly. You only need to update your Excel spreadsheet with each new scenario and then run this program to complete the remainder of your testing.
The code is available here: https://github.com/kkapoor84/Automation_Excel_Query_Creation
);
insertQueryTemplate = insertQueryTemplate.replace(columnNamesPlaceHolder, columnNamesBuilder.toString());
/// Insert into tableName (columnName1, columnName2) values ( '$3. Write functions to:
- Append these SQL queries in a text file
- Drop the text file with SQL insert queries into a folder location
4. Update the main function to make a call to these functions
Conclusion
Though it may be an endeavor to write this logic, once these functions are in place, your regression tests can be completed much more quickly. You only need to update your Excel spreadsheet with each new scenario and then run this program to complete the remainder of your testing.
The code is available here: https://github.com/kkapoor84/Automation_Excel_Query_Creation
);
}
else
{
//before replace : Insert into tableName (columnName1, columnName2) values ( '$3. Write functions to:
- Append these SQL queries in a text file
- Drop the text file with SQL insert queries into a folder location
4. Update the main function to make a call to these functions
Conclusion
Though it may be an endeavor to write this logic, once these functions are in place, your regression tests can be completed much more quickly. You only need to update your Excel spreadsheet with each new scenario and then run this program to complete the remainder of your testing.
The code is available here: https://github.com/kkapoor84/Automation_Excel_Query_Creation
);
String currentRowInsertQuery = tempInsertQueryTemplate.replace(columnValuesPlaceHolder, currentRowValuesBuilder.toString());
//after replace : Insert into tableName (columnName1, columnName2) values ( value1, value2 );
allInsertQueriesList.append(currentRowInsertQuery);
allInsertQueriesList.append(System.getProperty("line.separator"));
}
currentRow++;
}
System.out.println(allInsertQueriesList.toString());
//close excel file
file.close();
return allInsertQueriesList.toString();
}
catch (Exception e) {
e.printStackTrace();
return "Problem with query creation with error : " + e.getMessage();
}
}
3. Write functions to:
- Append these SQL queries in a text file
- Drop the text file with SQL insert queries into a folder location
4. Update the main function to make a call to these functions
Conclusion
Though it may be an endeavor to write this logic, once these functions are in place, your regression tests can be completed much more quickly. You only need to update your Excel spreadsheet with each new scenario and then run this program to complete the remainder of your testing.
The code is available here: https://github.com/kkapoor84/Automation_Excel_Query_Creation
Stay in Touch
Keep your competitive edge – subscribe to our newsletter for updates on emerging software engineering, data and AI, and cloud technology trends.
Thank you for submitting the form