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

  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
  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
// 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 = rowIterator.next();Iterator<Cell> cellIterator = nextRow.cellIterator();int totalNoOfRows = firstSheet.getLastRowNum(); // To get the number of rows present in sheetwhile (cellIterator.hasNext()) {Cell nextCell = cellIterator.next();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 = rowIterator.next();ArrayList<String> data = new ArrayList<String>();data.clear();Iterator<Cell> cellIterator = nextRow.cellIterator();while (cellIterator.hasNext()) {Cell nextCell = cellIterator.next();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
statement.addBatch();statement.executeBatch();statement.clearBatch();}
// Closing workbook and database connectionworkbook.close();connection.commit();connection.close();

--

--

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