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.

Elektron · Accepted Answer · 2015-02-04 15:20:12Z

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

answered Feb 4, 2015 at 15:20

Elektron

152 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anarkie Over a year ago

I had to use load data local infile because the file is on my local computer and I dont know why but \\ was not enough so I had to give \\\\ and then I noticed only an empty row is inserted to the DB nothing else.

Elektron Over a year ago

run the query against mysql from the shell and see the response but i'm sure the snippet i wrote above is working. I've used it severally

Collectives™ on Stack Overflow

How to insert rows from excel into mysql db?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related