Forum Stats

  • 3,817,359 Users
  • 2,259,322 Discussions
  • 7,893,760 Comments

Discussions

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

1001916
1001916 Member Posts: 7
edited May 10, 2013 12:18PM in New To Java
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

Answers

  • EJP
    EJP Member Posts: 32,920 Gold Crown
    You need to take a good look at using PreparedStatements instead of concatening SQL strings yourself.
  • DUPLICATE THREAD!

    Please don't create duplicate threads. Mark this thread ANSWERED and continue using your other one.

    This is basically the same question you still have open in your other thread where you are already getting help.
    11008824
  • 939520
    939520 Member Posts: 186
    In an unrelated observation, it appears you are creating new database tables to hold each document. I don't think this is a good idea. Your database tables should be created using the database's utility program and not programmatically. The database schema should hardly ever change once the project is complete.

    As a design approach: One database table can hold your document names, versions, and date they were uploaded. Another table will hold the column names and data types. Another table can hold the data (type for all data = String). This way, you can join the three tables to retrieve a document. Your design will only consists of those three tables no matter how many unique documents you have. You probably should seek the advice of a DBA or experienced Java developer on exactly how structure those tables. My design is a rough layout.
This discussion has been closed.