''SQLException Column count doesn't match value count at row 1'' in large files and no in small files

Question

I am making a program where I would read data from excel files and store them in tables. In my files some cells could be blank and I would store them in my table as null values. In my program I am using LinkedHashMap to read and store each cell as string. I have made the program and check it for a small excel file and works fine. But when I test it for a larger file I get this Exception:

SQLException: Column count doesn't match value count at row 1
SQLState: 21S01
VendorError: 1136

The code for filling the table is the below:

private static void fillTable(Connection con, String fieldname,
            List<TableRow> allData) {
        for (int row = 0; row < allData.size(); row++) {
            LinkedHashMap<String, Integer> rowData = allData.get(row).tableFields;
            Iterator iter = rowData.entrySet().iterator();
            String str;
            String[] tousFields = new String[rowData.size()];
            int i = 0;
            while (iter.hasNext()) {
                Map.Entry pairs = (Map.Entry) iter.next();
                Integer fieldType = (Integer) pairs.getValue();
                String fieldValue = (String) pairs.getKey();
                switch (fieldType) {
                case Cell.CELL_TYPE_NUMERIC:
                    str = fieldValue;
                    break;
                case Cell.CELL_TYPE_STRING:
                    str = "\'" + fieldValue + "\'";
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    str = fieldValue;
                    break;
                case Cell.CELL_TYPE_BLANK:
                    str = "null";
                    break;
                default:
                    str = "";
                    break;
                }
                tousFields[i++] = str;
            }

            try {
                Statement stmt = con.createStatement();
                String all = org.apache.commons.lang3.StringUtils.join(
                        tousFields, ",");
                String sql = "INSERT INTO " + fieldname + " VALUES (" + all
                        + ")";
                stmt.executeUpdate(sql);
                System.out.println("Fill table...");
            } catch (SQLException e) {
                System.out.println("SQLException: " + e.getMessage());
                System.out.println("SQLState: " + e.getSQLState());
                System.out.println("VendorError: " + e.getErrorCode());
            }

        }


    }

The code to create the table is:

private static String getCreateTable(Connection con, String tablename,
            LinkedHashMap<String, Integer> tableFields) {
        Iterator iter = tableFields.keySet().iterator();
        Iterator cells = tableFields.keySet().iterator();
        String str = "";
        String[] allFields = new String[tableFields.size()];
        int i = 0;
        while (iter.hasNext()) {
            String fieldName = (String) iter.next();
            Integer fieldType = (Integer) tableFields.get(fieldName);

            switch (fieldType) {
            case Cell.CELL_TYPE_NUMERIC:
                str = fieldName + " INTEGER";
                break;
            case Cell.CELL_TYPE_STRING:
                str = fieldName + " VARCHAR(255)";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                str = fieldName + " INTEGER";
                break;
            case Cell.CELL_TYPE_BLANK:
                str = "null";
                break;
            default:
                break;
            }
            allFields[i++] = str;
        }
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Statement stmt = con.createStatement();

            try {
                String all = org.apache.commons.lang3.StringUtils.join(
                        allFields, ",");
                String createTableStr = "CREATE TABLE " + tablename + " ("
                        + all + ")";


                System.out.println("Create a new table in the database");
                stmt.executeUpdate(createTableStr);
            } catch (SQLException e) {
                System.out.println("Error: the table already exists. Please try again " );

            }
        } catch (Exception e) {
            System.out.println(((SQLException) e).getSQLState());
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return str;
    }

Could anyone help me with my problem?

Sorry, i couldn't understand your question!What exactly would yoy like to add? — dedmar, May 20 '13 at 13:02
The exact insert statement you try to execute, and the table columns. — BobTheBuilder, May 20 '13 at 13:06
Yes in this post the code work fine for this excel file. But for larger I get this exception. — dedmar, May 20 '13 at 13:11
Please add the **value** of `sql` and `createTableStr` that you execute. — BobTheBuilder, May 20 '13 at 13:14

score 0 · Answer 1 · answered May 20 '13 at 13:12

This error occurs when the number of fields in the insert clause doesn't match the number of values you pass between the parenthesis. Just print the SQL statement before the execution when you have an error and check the number of fields and the number of values.

score 0 · Answer 2 · answered May 20 '13 at 13:20

0

In your creation of table, you are using:

case Cell.CELL_TYPE_BLANK:
                str = "null";
                break;
            default:

You either create table column named "null" or you create no column at all. I am not sure right now, but I think no column is created. Hovewer, in your INSERT statement, you suggest, that there is fixed number of columns and that there exist null one.

answered May 20 '13 at 13:20

Martin Perry

9,232
8
46
114

I ve deleted it but nothing has changed :/ – dedmar May 20 '13 at 13:32
I dont know, how you have organized DB. But sometimes, if you cannt pass NULL to Integer or varchar, if you have selected that column as NOT NULL (not know, if its your case) – Martin Perry May 20 '13 at 13:42

''SQLException Column count doesn't match value count at row 1'' in large files and no in small files

2 Answers2