How to import excel data to SQLite DB using java — Devstringx Technologies

What does it cover?

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


SQLite, Java, Database

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. Use prepare statement to execute statement multiple times.
  2. To create schema, we are generating total number of maximum columns having values from excel.
  3. Using Apache POI to Read excel data and importing data in batch using prepared statement methods.
  4. Closing workbook and database connection

Code :

// Assign variablesConnection connection = null;PreparedStatement statement = 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
FileInputStream inputStream = new FileInputStream(excelFilePath);Workbook workbook = new XSSFWorkbook(inputStream);Sheet firstSheet = workbook.getSheetAt(0); // first sheet
// Generate maximum number of column in sheet
int max=0;Iterator<Row> rowIterator = firstSheet.iterator();while (rowIterator.hasNext()) {Row nextRow =;Iterator<Cell> cellIterator = nextRow.cellIterator();int totalNoOfRows = firstSheet.getLastRowNum(); // To get the number of rows present in sheetwhile (cellIterator.hasNext()) {Cell nextCell =;int col1 = nextCell.getColumnIndex();for (int row = 1; row <= totalNoOfRows; row++) {if (col1 >= max)max = col1;}}}
// creating schema
ArrayList sch = new ArrayList();ArrayList val = new ArrayList();for (int first = 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 create statement
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 statementString insert = "INSERT INTO " + dbName + schema + "VALUES" + values + ";";statement = connection.prepareStatement(insert);

// Read excel data
Iterator<Row> rowIterator = firstSheet.iterator();while (rowIterator.hasNext()) {int cellCount = 0;Row nextRow =;ArrayList<String> data = new ArrayList<String>();data.clear();Iterator<Cell> cellIterator = nextRow.cellIterator();while (cellIterator.hasNext()) {Cell nextCell =;int col = nextCell.getColumnIndex();if (nextCell.getCellType() == CellType.STRING) {data.add(cellCount, nextCell.getStringCellValue());}else if (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
// Closing workbook and database connectionworkbook.close();connection.commit();connection.close();

Originally published at on Oct 07, 2021.




Devstringx Technologies is highly recommended IT company for custom software development, mobile app development and automation testing services

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Trie Data Structure

7 Benefits of Custom Software Development

5 essential values to keep in mind when looking for a software developer position.

Getting Started with GitLab


NullReferenceException you wouldn’t expect

Establishment of Defibox Development Fund

So, You Want to Make Games — Now What?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Devstringx Technologies

Devstringx Technologies

Devstringx Technologies is highly recommended IT company for custom software development, mobile app development and automation testing services

More from Medium

Wait for a Page to Load in Selenium

XPath in Selenium

Selenium and PowerShell

Views in Databases