[KShare] How to insert data in Excel file to MySQL database with Katalon Studio

Product Insights
, , , , ,
1

Hi all, :wave:

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. :point_down:

Below is our data table on MySQL…

image
image2076×758 160 KB

And here is our Excel data and we store it in our project folder …

image
image1498×540 99.1 KB

Solution :white_check_mark:

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}"

}

:spiral_notepad: 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.

Reference

Video demos

https://drive.google.com/drive/folders/1nwuLUoEXMyDqG9hHBe3CY-AYUJRCbLLM?usp=drive_link

3 Likes
2

Thank you very much the Product Support team (@support.squad) and Caryln (@thi.thai) for another helpful article!

Caryln profile pic
Caryln (@thi.thai) - Product Support Specialist at Katalon
Caryln enjoys offering supporting and assisting our clients with technical issues and questions, as well as sharing her knowledge and experiences with the wider Katalon Community. She is always available to assist users if they have questions or concerns with Katalon products, and she strives to make sure our users have a positive testing experience.
3 Likes