I am trying to upload a CSV format txt file into a mySql database table using LOAD DATA LOCAL INFILE.......sql query in JAVA. Though the databse table has as many columns as the file under consideration is having fields separated by comma in each row, the program is giving the output that there aren't enough data in the starting row. Some of the fields in the rows are null values(represented by null string) though. Any clues?
My code to upload the CSV format text file:
package app;
import java.sql.*;
public class UploadTestDb {
public static void main(String args[]){
int result = uploadData();
System.out.println(result);
}
public static int uploadData() {
int res1 = 0;
String filename = "C:\\\\TestDbParse.txt";
String tablename = "testParse";
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test_dbapp","root","vinsonmassif");
Statement stmt = con.createStatement();
res1 = stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE " + tablename + " FIELDS TERMINATED BY ','" + "LINES TERMINATED BY '\\r\\n'" );
}
catch(Exception e){
e.printStackTrace();
}
return res1;
}
}
I created the table testParse into the databse test_dbapp. the table is as shown below:
FieldType NullKey DefaultExtra
S_NoVarchar(30) Yes NULL
ItemCodeVarchar(30) YesNULL
ItemVarchar(30) YesNULL
Category Varchar(30)YesNULL
Qty Varchar(30) YesNULL
PriceVarchar(30) YesNULL
The table i created is a very simple one. I made the datatype of every column as Varchar(30) as it can hold any type of value(as a string) though i could have included different data types wherever necessary. The output of the parsing program is a CSV format text file and it as given below:
1.0,101.0,Pen,Stationery,2.0,1000.0
2.0,102.0,Book,Stationery,2.0,1000.0
3.0,103.0,Calculator,Electronics,3.0,5000.0
But i am getting this error as output:
java.sql.SQLException:Row 2 doesn't contain data for all columns.
Why is it so?
Could anybody help me out?
Message was edited by:
subhashmedhi
My code for parsing the excel file.
/*Additional requirements needed in this program:
*1. some minor changes in the code to check that the initial few lines 1-16 are parsed properly
*2. to enable the creation of the table dynamically by the program itself with column haeders being the cell-values of the first row being parsed
*3. to upload the output text file into the database.
*/
package app;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class TestDbParse{
public static void main( String []args){
try {
POIFSFileSystem pf = new POIFSFileSystem(new FileInputStream("C:\\Test_DbApp.xls"));
File f = new File("C:\\TestDbParse.txt");
FileOutputStream os = new FileOutputStream(f);
PrintWriter pw = new PrintWriter(os);
HSSFWorkbook wb = new HSSFWorkbook(pf);
HSSFSheet hs = wb.getSheetAt(0);
int first_row_num = hs.getFirstRowNum();
int last_row_num = hs.getLastRowNum();
System.out.println("The first row's number is " + first_row_num);
short first_cell_num = (short)0;
short last_cell_num = (short)0;
short temp = 0;
int type_cell = 0;
int cell_num_counter = 0;
System.out.println("The last physically defined row in the excel sheet is numbered" + last_row_num);
try {//***//
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/verify","root","vinsonmassif");
PreparedStatement ps = con.prepareStatement("INSERT INTO testdb VALUES( ? )");
//***//
for( int i = first_row_num; i <= last_row_num; i++){
HSSFRow r = hs.getRow(i);
HSSFCell c = null;
if(i == 0){
first_cell_num = r.getFirstCellNum();
last_cell_num = r.getLastCellNum();
temp = last_cell_num;
System.out.println("The last cell's number in the first row is " + last_cell_num);
continue;//************//
}
if(r == null){
cell_num_counter = 0;
}
else {
System.out.println("the first cell's number in the row is" + first_cell_num + "The last cell number in the row is" + last_cell_num);//*test*//
for(int j = 0; j <= (int)last_cell_num - 1; j++){
if( (c = r.getCell((short)j))== null){
type_cell = 3;
}
else {
c = r.getCell((short)j);
type_cell = c.getCellType();
}
switch(type_cell){
case 0:// NUMERIC TYPE
System.out.println("Cell contains numeric value");
double value0 = c.getNumericCellValue();
System.out.println("The value contained is" + " " + value0);
ps.setDouble(1, value0); //***//
ps.executeUpdate();
String value = String.valueOf(value0);
if(cell_num_counter == (int)temp - 1){// replace 59 by last_cell_num
System.out.println("LAST CELL NUMBER!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! " + cell_num_counter); // test, should be deleted later
pw.println(value);
pw.println();
pw.println();
pw.flush();
cell_num_counter = 0;
}
else {
pw.print(value + ",");
pw.flush();
cell_num_counter++;
}
break;
case 1:// CELL_TYPE_STRING
System.out.println("Cell contains string value");
String value1 = c.getStringCellValue();
ps.setString(1, value1);//***//
ps.executeUpdate();
if(cell_num_counter == (int)temp - 1){// replace 59 by last_cell_num
pw.println(value1);
pw.println();
pw.println();
pw.flush();
cell_num_counter = 0;
}
else {
pw.print(value1 + ",");
pw.flush();
cell_num_counter++;
}
System.out.println("The value contained in the cell is" + " " + value1);
break;
case 2:// CELL_TYPE_FORMULA
System.out.println("Cell contains formula type value");
String value2 = c.getCellFormula();
ps.setString(1,value2);//***//
ps.executeUpdate();
if(cell_num_counter == (int)temp - 1){// replace 59 by last_cell_num
System.out.println("LAST CELL NUMBER " + cell_num_counter);// test, should be deleted later
pw.println(value2);
pw.flush();
pw.println();
pw.println();
pw.flush();
cell_num_counter = 0;
}
else{
pw.print(value2 + ",");
pw.flush();
cell_num_counter++;
}
System.out.println("The value contained in the cell is" + " " + value2);
break;
case 3:// CELL_TYPE_BLANK
System.out.println("Cell contains blank value");
if(cell_num_counter == (int)temp - 1){
pw.println("null ");
pw.flush();
pw.println();
pw.println();
pw.flush();
cell_num_counter = 0;
}
else {
pw.print("null " + ",");
pw.flush();
cell_num_counter++;
}
break;
case 4:// CELL_TYPE_BOOLEAN
System.out.println("Cell contains boolean type value");
boolean value3 = c.getBooleanCellValue();
ps.setBoolean(1,value3);//***//
ps.executeUpdate();
System.out.println("The boolean value contained in the cell is" + " " + value3);
if(cell_num_counter == (int)temp - 1){
pw.println(value3);
pw.flush();
pw.println();
pw.println();
pw.flush();
cell_num_counter = 0;
}
else{
pw.print(value3 + ",");
pw.flush();
cell_num_counter++;
}
break;
case 5: // CELL_TYPE_ERROR
System.out.println("Cell contains error code");
byte value4 = c.getErrorCellValue();
System.out.println("The error code contained in the cell is" + " " +value4);
if(cell_num_counter == (int)temp - 1){
cell_num_counter = 0;
}
else {
cell_num_counter++;
}
break;
default: ;
}
}
}
}
}catch(Exception e1){
e1.printStackTrace();
}
}catch( IOException e){
e.printStackTrace();
}
}
}
