How to Import Excel Data to SQLite DB Using Java | Devstringx

Back to Blog
Import Excel Data to SQLite DB Using Java

How to Import Excel Data to SQLite DB Using Java | Devstringx

Prerequisites

SQLite, Java, Database

What Does It Cover?

  1. Creating and closing database connection (JDBC)
  2. Creating dynamic schema
  3. Creating a table and deleting table values
  4. Reading data from excel
  5. Importing excel data to DB

How does it work?

  1. To execute the SQL queries using java, we need to establish a connection with DB and then create a statement to execute the queries. Uses prepare a statement to execute a statement multiple times.
  2. To create a schema, we are generating a total number of maximum columns having values from excel.
  3. Using Apache POI to Read excel data and import data in batch using prepared statement methods.
  4. Closing workbook and database connection
<CODE>

// Assign variables

Connection connection = null;

PreparedStatementstatement = null;

Connection connection = null;

String dbName = "<DB Name>";

String excelFilePath = "<file path>";

String url = "jdbc:sqlite:" + <"dbPath"> + dbName+ ".db";

// create a connection to the database

connection = DriverManager.getConnection(url);

connection.setAutoCommit(false);

// Opening Worksheet

FileInputStreaminputStream = newFileInputStream(excelFilePath);

Workbook workbook = newXSSFWorkbook(inputStream);

Sheet firstSheet = workbook.getSheetAt(0);     // first sheet

//Generate maximum number of column in sheet

intmax=0;

Iterator<Row>rowIterator = firstSheet.iterator();

while (rowIterator.hasNext()) {

Row nextRow = rowIterator.next();

Iterator<Cell>cellIterator = nextRow.cellIterator();

inttotalNoOfRows = firstSheet.getLastRowNum(); // To get the number of rows present in sheet

while (cellIterator.hasNext()) {

Cell nextCell = cellIterator.next();

intcol1 = nextCell.getColumnIndex();

for (introw = 1; row<= totalNoOfRows; row++) {

if (col1>= max)

max = col1;

}

}

}

// creating schema

ArrayListsch = newArrayList();

ArrayListval = newArrayList();

for (intfirst = 1; first<= max + 1; first++) {

sch.add("'" + first + "'");

val.add("?");

}

String schema = sch.toString().replace("[", "(").replace("]", ")").trim();

String values = val.toString().replace("[", "(").replace("]", ")").trim();

// execute queries using createstatement

String table = "CREATE TABLE " + dbName + schema + ";";

Statement stmt = connection.createStatement();

stmt.executeUpdate(table);

String delete = "DELETE FROM " + dbName + ";";

Statement stmt = connection.createStatement();

stmt.executeUpdate(delete);

// execute query using prepare statement

String insert = "INSERT INTO " + dbName + schema + "VALUES" + values + ";";

statement = connection.prepareStatement(insert);

//Read excel data

Iterator<Row>rowIterator = firstSheet.iterator();

while (rowIterator.hasNext()) {

intcellCount = 0;

Row nextRow = rowIterator.next();

ArrayList<String>data = newArrayList<String>();

data.clear();

Iterator<Cell>cellIterator = nextRow.cellIterator();

while (cellIterator.hasNext()) {

Cell nextCell = cellIterator.next();

intcol = nextCell.getColumnIndex();

if (nextCell.getCellType() == CellType.STRING) {

data.add(cellCount, nextCell.getStringCellValue());

}

elseif (nextCell.getCellType() == CellType.NUMERIC) {

data.add(cellCount, NumberToTextConverter.toText(nextCell.getNumericCellValue()));

}

statement.setString(col + 1, data.get(cellCount).toString());

cellCount += 1;

}

// Import data in batch using prepared statement methods

statement.addBatch();

statement.executeBatch();

statement.clearBatch();

}

// Closing workbook and database connection

workbook.close();

connection.commit();

connection.close();        

 </CODE>

FAQs

  • How can I import excel to an SQLite database using Java?

To import an Excel spreadsheet into an SQLite database using Java, you will need to connect to the database using the SQLite JDBC driver, create a table in the database to store the data, read the data from the Excel file using a Java library such as Apache POI, and iterate through the rows of the Excel file, inserting the data into the SQLite table using standard SQL INSERT statements.

  • How to export data from an SQLite database to an Excel spreadsheet using Java?

To export data from an SQLite to an excel spreadsheet using Java, you will need to connect to the database using the SQLite JDBC driver, execute a SELECT statement to retrieve the data you want to export, use a Java library such as Apache POI to create a new Excel file and write the data to the file, and save the Excel file.

  • Is it possible to import and export data between Excel and SQLite using a third-party tool or library?

Yes, several third-party tools and libraries provide built-in support for importing and exporting data between Excel and SQLite import excel. These can often simplify the process and provide additional features and functionality.

  • Can we use the SQLite JDBC driver to connect to an SQLite database from a Java application?

Yes, the SQLite JDBC driver allows you to connect to an SQLite database from a Java application. You can download the driver from the SQLite website and include it in your project to access the database.

  • What Java library and how use to read and write data to an Excel file?

Several SQLite java libraries can be used to read and write data to an Excel file, such as Apache POI and JExcelAPI. These libraries provide a range of features and functionality for working with Excel files in Java, including support for different file formats and the ability to manipulate cells, rows, and columns.

If you are interested in even more software testing-related articles and information from us here at Devstringx, then we have a lot to choose from for you.

Share this post

Back to Blog