Hi all, 
Tired of manually populating your MySQL database with test data? Streamline your testing process by leveraging the power of Katalon Studio!
In today’s article, we will guide you through a step-by-step process of seamlessly inserting data from an Excel file into your MySQL database to help you save time and effort. Read more below. 
Below is our data table on MySQL…
And here is our Excel data and we store it in our project folder …
Solution 
Include Libraries:
-
MySQL JDBC driver: Go to Project → Settings > External Library to include the mysql-connector-java.jar file. Download it here: https://dev.mysql.com/downloads/connector/j/
-
Apache POI: Download the JAR file from the official POI website (https://poi.apache.org/) and add it to your Katalon project library (Project → Settings > External Library)
Then I create the below script to read the data from the Excel file and insert it into my table database:
import java.sql.DriverManager
import org.apache.poi.ss.usermodel.WorkbookFactory
// Access database credentials from environment variables (replace with actual variable names)
def url = 'jdbc:mysql://sql6.freesqldatabase.com:3306/sql6692690'
def username = 'sql6692690'
def password = 'rH9JPqXRIh'
// Replace with the path to your Excel file (ensure it's accessible by Katalon)
def excelFilePath = 'DATABASE.xlsx' // Example path, replace with your actual file
def conn = null
try {
//Connect to the database
Class.forName('com.mysql.cj.jdbc.Driver')
conn = DriverManager.getConnection(url, username, password)
// Prepare the call to the stored procedure
def insertQuery = "INSERT INTO Employee (NAME, AGE, ADDRESS, STATUS) VALUES (?, ?, ?, ?)"
def callableStatement = conn.prepareCall(insertQuery)
// Open the Excel file
def workbook = WorkbookFactory.create(new File(excelFilePath))
def sheet = workbook.getSheetAt(0) // Assuming data is in the first sheet (index 0)
// Loop through rows (skip the header row)
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
def row = sheet.getRow(i)
// Extract data from cells (assuming first four columns contain data)
def data1 = row.getCell(0)?.getStringCellValue()
println data1
def data2 = row.getCell(1)?.getNumericCellValue()
println data2
def data3 = row.getCell(2)?.getStringCellValue()
def data4 = row.getCell(3)?.getNumericCellValue()
if (data1 != null && data2 != null) { // Skip rows with missing data
callableStatement.setString(1, data1)
callableStatement.setDouble(2, data2)
callableStatement.setString(3, data3)
callableStatement.setDouble(4, data4)
callableStatement.execute()
}
}
println "Data inserted successfully from Excel!"
} catch (Exception e) {
println "Error inserting data: ${e.message}"
}
Important notes:
- Ensure the Excel file format aligns with the script’s logic for data extraction (cell indexes, data types).
- Consider error handling within the script for potential issues during file reading, database operations, or handling missing data.
- This is a basic example.
