3 Replies Latest reply: May 10, 2013 11:18 AM by 939520 RSS

    Column count doesn't match value count at row 1, unknown number of columns

    1001916
      Hi,
      I am making a program to read data from excel files as the above and store them in tables. I have managed to read all the data from excel files as a string and store them in a table.

      ID Name Salary

      50 christine 2349000

      43 paulina 1245874

      54 laura 4587894

      23 efi 3456457

      43 jim 4512878

      But in my project I have several other files that have same cell that are blank as the above example

      ID Name Salary

      50 christine 2349000

      43 paulina

      laura 4587894
      23 3456457

      43 jim 4512878

      and when i ran the same program i get this exception :
      SQLException: Column count doesn't match value count at row 1
      SQLState: 21S01
      VendorError: 1136
      The code for creating the table and inserting the values is above:
      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;
                          default:
                               str = "";
                               break;
                          }
                          allFields[i++] = str;
                     }
                     try {
                          Statement stmt = con.createStatement();
                          
                          try {
                               String all = org.apache.commons.lang3.StringUtils.join(
                                         allFields, ",");
                               String createTableStr = "CREATE TABLE IF NOT EXISTS "
                                         + tablename + " ( " + all + ")";
      
                               System.out.println("Create a new table in the database");
                               stmt.executeUpdate(createTableStr);
                          } catch (SQLException e) {
                               System.out.println("SQLException: " + e.getMessage());
                               System.out.println("SQLState:     " + e.getSQLState());
                               System.out.println("VendorError:  " + e.getErrorCode());
                          }
                     } catch (Exception e) 
                     {
                          System.out.println( ((SQLException) e).getSQLState() );
                          System.out.println( e.getMessage() );
                          e.printStackTrace();
                     }
                     return str;
                }
           
                private static void fillTable(Connection con, String fieldname,
                          LinkedHashMap[] tableData) {
                     for (int row = 0; row < tableData.length; row++) {
                          LinkedHashMap<String, Integer> rowData = tableData[row];
                          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;
                               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());
                          }
      
                     }
      To be more specific the error it in the second row where i have only ID and Name in my excel file and only these i want to store. The third row has only Name and Salary and no ID. How i would be able to store only the values that i have and leave blank in the second row the Salary and in the third row the ID? Is there a way for my program to skip the blanks as empty value?

      Edited by: 998913 on May 9, 2013 1:01 AM