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:

  1. The QA engineer inputs and updates various test case scenario data in an Excel spreadsheet
  2. The QA engineer places these Excel spreadsheet files into a pre-defined folder location, which is later processed with the Java program
  3. The Java program picks and processes the Excel spreadsheets and creates SQL input queries with the data in each spreadsheet
  4. 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 ( '$$$' );
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 ( '$$$' );
            insertQueryTemplate = insertQueryTemplate.replace(columnNamesPlaceHolder,    columnNamesBuilder.toString());
 
            /// Insert into tableName (columnName1, columnName2) values ( '$$$' );
                              }
         else
                             {
 
          //before replace : Insert into tableName (columnName1, columnName2) values ( '$$$' );
         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
//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

Khushboo Kapoor

Khushboo Kapoor

Module Lead Quality Assurance

Khushboo Kapoor is a Module Lead Quality Assurance for 3Pillar Global. She has over 6 years of experience in Agile methodologies, requirements analysis, software project management, and testing and test automation.

2 Responses to “Automation Testing: Excel to SQL Query Creation”
  1. Tanima Roy on

    This is an good article. Great Job !

    Reply
  2. Sachin Jain on

    Excellent Article. Very descriptive and “STEP-BY-STEP SOLUTION” is very helpful.
    Thanks & Regards

    Reply
Leave a Reply

Related Posts

Parallel Testing on Multiple Android Devices Using Appium an... Introduction Testing has become an important part in delivering a high quality product. In order to obtain a reliable application, it’s important to ...
An Introduction to Localized Testing The world we live in has hundreds of languages written and spoken. For many languages, there are multiple locale variants that are popular around the ...
Automated Mobile App Testing in Amazon Device Farm Amazon Device Farm (ADF) is an app testing service that facilitates testing and interaction with Android, iOS, and Web apps on real, physical phones a...
Understanding Data in Data Science – Statistical Infer... In my previous posts in this series, I wrote on the analysis of single variables and multiple variables. The measures described in the previous posts ...
How to Initialize a Postgres Docker with a Million-Plus Reco... Besides the official Docker documentation, there are several good sources on the internet where you can read about how to kick-start your Dockerized e...

SUBSCRIBE TODAY


Sign up today to receive our monthly product development tips newsletter.