Inserting values from Excel to access database using Java

Question

I am using this code to import values from an Excel file to my Access database. Everything is working well except for the fact that the code would read all the cells but won't copy everything into access table.

(Eg.-200 rows are read successfully but only 97 are entered into the database and the code stops without any error.)The description which is being entered here is about 100 words per row.

public class selector extends javax.swing.JFrame {

    final JFileChooser fc = new JFileChooser();
    File file;
    static String filename, query2, item;
    static String[][] itemss = new String[10000][10000];
    static double xxx;
    static datacon dc = new datacon();
    public static Statement st;

    static int i, j, rows = -1, p;

        private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        try {
            try {
                // TODO add your handling code here:
                fileChose();
            } catch (SQLException ex) {
                Logger.getLogger(selector.class.getName()).log(Level.SEVERE, null, ex);
            } catch (FileNotFoundException ex) {
                Logger.getLogger(selector.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (InvalidFormatException ex) {
            Logger.getLogger(selector.class.getName()).log(Level.SEVERE, null, ex);
        }

    }                                        
    public static String fileChose() throws InvalidFormatException, SQLException, FileNotFoundException {
        JFileChooser fc = new JFileChooser();
        FileNameExtensionFilter filter = new FileNameExtensionFilter("XLS files", "xls", "XLSX files", "xlsx");
        fc.setFileFilter(filter);
        int ret = fc.showOpenDialog(null);

        if (ret == JFileChooser.APPROVE_OPTION) {
            File file = fc.getSelectedFile();

            filename = file.getAbsolutePath();
            System.out.println(filename);

            work();
            return filename;
        } else {
            return null;
        }
    }

    static void work() throws InvalidFormatException, SQLException, FileNotFoundException {
        try {
            FileInputStream file = new FileInputStream(new File(filename));

            org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(file);
            org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            dc.connect();

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                rows++;
                i = 0;
                //For each row, iterate through all the columns 
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell;
                    cell = cellIterator.next();

                    //Check the cell type and format accordingly 
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            xxx = cell.getNumericCellValue();
                            itemss[rows][i] = Double.toString(xxx);
                            break;
                        case Cell.CELL_TYPE_STRING:
                            item = "" + cell.getRichStringCellValue();
                            itemss[rows][i] = item;
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            item = "" + cell.getRichStringCellValue();
                            itemss[rows][i] = item;
                            break;
                    }
                    System.out.println("coloumn " + i + " : " + itemss[rows][i]);
                    i++;
                }
            }
            file.close();

            for (j = 0; j < rows; j++) {
                String query = " INSERT INTO schedule ([counter],[description]) VALUES ('" + j + "','" + itemss[j][1] + "') ";
                st.executeUpdate(query);
                System.out.println(query);
            }

        } catch (Exception e) {
        }
    }
public static class datacon {

        public Connection con;
        // public Statement st;
        public ResultSet rs;

        public void connect() {
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                con = DriverManager.getConnection("jdbc:odbc:database4");
                st = con.createStatement();

            } catch (Exception t) {
                System.out.println(t.toString());
            }

        }
    }
}

Answer 1

Your Insert query may be throwing exceptions. You are not logging them:

    for (j = 0; j < rows; j++) {
            String query = " INSERT INTO schedule ([counter],[description]) VALUES ('" + j + "','" + itemss[j][1] + "') ";
            st.executeUpdate(query);
            System.out.println(query);
        }

    } catch (Exception e) {
        //no logging
    }

Also work() has declared exceptions that it can throw and yet the whole implementation is in try catch

Answer 2

Probably you have a problem in your switch code.

Add a default value and check if the code pass in this part and what is the cell type.

something like this:

default:
    System.out.println("Default case: "+ cell.getCellType());