How to insert rows from excel into mysql db?

Question

I have googled and googled but could not find a single proper working example, finally I converted this to mysql import for my case and here is the code:

import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import java.util.*;
import java.sql.*; 


public class Import {  

    public static void main(String[] args) throws Exception{                
            /* Create Connection objects */
           // Class.forName ("oracle.jdbc.OracleDriver"); 
            Properties connectionProps = new Properties();
            connectionProps.put("user", "myUSERNAME");
            connectionProps.put("password", "myPASSWORD");
            Connection  conn = DriverManager.getConnection("jdbc:mysql://"
                    + "serverIP" + ":" + "3306" + "/" + "myDB",
                    connectionProps);
            PreparedStatement sql_statement = null;
            String jdbc_insert_sql = "INSERT INTO myTABLE"
                            + "(LastName, FirstName) VALUES"
                            + "(?,?)";
            sql_statement = conn.prepareStatement(jdbc_insert_sql);
            /* We should now load excel objects and loop through the worksheet data */
            FileInputStream input_document = new FileInputStream(new File("insert_old.xls"));
            /* Load workbook */
            HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document);
            /* Load worksheet */
            HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
            // we loop through and insert data
            Iterator<Row> rowIterator = my_worksheet.iterator(); 
            while(rowIterator.hasNext()) {
                    Row row = rowIterator.next(); 
                    Iterator<Cell> cellIterator = row.cellIterator();
                            while(cellIterator.hasNext()) {
                                    Cell cell = cellIterator.next();
                                    switch(cell.getCellType()) { 
                                    case Cell.CELL_TYPE_STRING: //handle string columns
                                            sql_statement.setString(1, cell.getStringCellValue());                                                                                     
                                            break;
                                    case Cell.CELL_TYPE_NUMERIC: //handle double data
                                            sql_statement.setDouble(2,cell.getNumericCellValue() );
                                            break;
                                    }

                            }
            // Add the row to a batch processing - standard batch processing example
            sql_statement.addBatch();
            }
            //We are now ready to perform a bulk batch insert
            //our Excel has only 4 records, you should set this suitably
            int[] totalRecords = new int[4];
            try {
                    totalRecords = sql_statement.executeBatch();
            } catch(BatchUpdateException e) {
                    //you should handle exception for failed records here
                    totalRecords = e.getUpdateCounts();
            }
            System.out.println ("Total records inserted : " + totalRecords.length);
            /* Close input stream */
            input_document.close();
            /* Close prepared statement */
            sql_statement.close();
            /* COMMIT transaction */
           // conn.commit();
            /* Close connection */
            conn.close();
    }
}

This is the output: "Total records inserted : 4"

The excel file has 4 rows and 2 columns filled with simple text. I look at the database unfortunately nothing is imported, when I make this simple insert working I would like to later modify it for more inserting image files, the file paths will be stored in xls files, also this version is for xls I need to adopt xlsx too. I would be glad if you can help me find the error in my code or give a better working example for xls and xlsx. Im using Apache POI.

Answer 1

You can simply do this: Assuming the file is located at C:\filename.xlsx

Connection con = //...Complete connection initialization
PreparedStatement pre = con.prepareStatement("load data infile 'C:\\filename.xlsx' into table tablename");
pre.executeUpdate();

This code would work perfectly once you avoid the following errors: 1. Make sure the file is located at the root of the directory and not in another directory (that can lead to an exception) 2. Make sure you quote the path with a single quote