Thread: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

HI FRIENDS,

I want to import multiple coulmns from xls file into mysql my code is showing nullpointer exception give me the solution

import java.util.*;
import java.lang.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class ImportData {

public static void main(String[] args) {

try{
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection) DriverManager.getConnection

("jdbc:mysql://localhost/test","root","abhi1402");
con.setAutoCommit(false);
PreparedStatement pstm = null ;
FileInputStream input = new FileInputStream("D:/New

Folder/apnakhata1.xls");
POIFSFileSystem fs = new POIFSFileSystem( input );
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Row row;
for(int i=1; i<=sheet.getLastRowNum(); i++){
row = sheet.getRow(i);
String CountryName = row.getCell(1).getStringCellValue();
String CountryCode = row.getCell(2).getStringCellValue();
String BankName = row.getCell(3).getStringCellValue();
String Website = row.getCell(4).getStringCellValue();
String Name = row.getCell(5).getStringCellValue();
String SMS = row.getCell(6).getStringCellValue();
int SMSNumber = (int) row.getCell(7).getNumericCellValue();
int CustomerCare = (int) row.getCell(8).getNumericCellValue();

String sql = "INSERT INTO Bank VALUES('"+CountryName+"','"+CountryCode

+"','"+BankName+"','"+Website+"','"+Name+"','"+SMS +"','"+SMSNumber

+"','"+CustomerCare+"')";

pstm = (PreparedStatement) con.prepareStatement(sql);
pstm.execute();
System.out.println("Import rows "+i);
}
con.commit();
pstm.close();
con.close();
input.close();
System.out.println("Success import excel to mysql table");
}catch(ClassNotFoundException e){
System.out.println(e);
}catch(SQLException ex){
System.out.println(ex);
}catch(IOException ioe){
System.out.println(ioe);
}

}

}

What does the stacktrace say? In your catch statements, change them to

e.printStackTrace();

It will tell you where the null pointer is occuring.

i do like that also but its showing nullpointer exception is there is any wrong in this code?

We can't help you without the full stacktrace. A stacktrace doesn't just say 'nullpointer exception' it tells you exactly where it is occuring.

i m trying to import the data from xls to mysql but showing nullpointer exception what to do please help


import java.util.*;
import java.lang.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;


public class ImportData {

public static void main(String[] args) {
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection) DriverManager.getConnection

("jdbc:mysql://localhost/test","root","abhi1402");
con.setAutoCommit(false);
PreparedStatement pstm = null ;
FileInputStream input = new FileInputStream("D:/New

Folder/apnakhata1.xls");
POIFSFileSystem fs = new POIFSFileSystem( input );
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Row row;
for(int i=1; i<=sheet.getLastRowNum(); i++){
row = sheet.getRow(i);
String CountryName = row.getCell(1).getStringCellValue();
String CountryCode = row.getCell(2).getStringCellValue();
String BankName = row.getCell(3).getStringCellValue();
String Website = row.getCell(4).getStringCellValue();
String Name = row.getCell(5).getStringCellValue();
String SMS = row.getCell(6).getStringCellValue();
int SMSNumber = (int) row.getCell(7).getNumericCellValue();
int CustomerCare = (int) row.getCell(8).getNumericCellValue();
String sql = "INSERT INTO Bank VALUES('"+CountryName+"','"+CountryCode
+"','"+BankName+"','"+Website+"','"+Name+"','"+SMS +"','"+SMSNumber
+"','"+CustomerCare+"')";

pstm = (PreparedStatement) con.prepareStatement(sql);
pstm.execute();
System.out.println("Import rows "+i);
}
con.commit();
pstm.close();
con.close();
input.close();
System.out.println("Success import excel to mysql table");
}catch(ClassNotFoundException e){
e.printStackTrace();
System.out.println(e);
}catch(SQLException ex){
ex.printStackTrace();
System.out.println(ex);
}catch(IOException ioe){
ioe.printStackTrace();
System.out.println(ioe);
}

}

}

showing an error -- Exception in thread "main" java.lang.NullPointerException at ImportData.java

i want to import multiple colmns from xls to mysql is there any wrong in my code

yaa on Exception in thread "main" java.lang.NullPointerException at (ImportData.java 40)

--- Update ---

if there is any correction on line number 40

i made it simple but still getting an error


import java.util.*;
import java.lang.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;


public class ImportData {

public static void main(String[] args) {
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection) DriverManager.getConnection

("jdbc:mysql://localhost/test","root","abhi1402");
con.setAutoCommit(false);
PreparedStatement pstm = null ;
FileInputStream input = new FileInputStream("D:/import/users.xls");
POIFSFileSystem fs = new POIFSFileSystem( input );
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Row row;
for(int i=1; i<=sheet.getLastRowNum(); i++){
row = sheet.getRow(i);
System.out.println(i);
String user_id = row.getCell(1).getStringCellValue();
System.out.println(user_id);
String first_name = row.getCell(2).getStringCellValue();
System.out.println(first_name);
String last_name = row.getCell(3).getStringCellValue();
System.out.println(last_name);

String sql = "INSERT INTO Bank VALUES('"+user_id+"','"+first_name
+"','"+last_name+"')";

pstm = (PreparedStatement) con.prepareStatement(sql);
pstm.execute();
System.out.println("Import rows "+i);
}
con.commit();
pstm.close();
con.close();
input.close();
System.out.println("Success import excel to mysql table");
}catch(ClassNotFoundException e){
e.printStackTrace();
System.out.println(e);
}catch(SQLException ex){
ex.printStackTrace();
System.out.println(ex);
}catch(IOException ioe){
ioe.printStackTrace();
System.out.println(ioe);
}

}

}





error is:

ImportData.java:32: error: incompatible types
row = sheet.getRow(i);
^
required: Row
found: HSSFRow




???

According to the documentation getRow(int index) returns a HSSFRow. Change:

Row row;

to

HSSFRow row;

Also, take a real good look at the documentation. There are a few nice functions like rowIterator() which is a cleaner way of parsing through rows. From the looks of it there may be situations where the physical row differs from the logical one which could cause problems like nullpointers. Use this iterator instead of the for loop.