Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 13 of 13

Thread: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

  1. #1
    Junior Member
    Join Date
    Nov 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post 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);
    }

    }

    }


  2. #2
    Forum VIP
    Join Date
    Jun 2011
    Posts
    317
    My Mood
    Bored
    Thanks
    47
    Thanked 89 Times in 74 Posts
    Blog Entries
    4

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

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

    e.printStackTrace();

    It will tell you where the null pointer is occuring.

  3. #3
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    25,042
    Thanks
    63
    Thanked 2,708 Times in 2,658 Posts

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

    Please edit your post and wrap your code with code tags:
    [code=java]
    YOUR CODE HERE
    [/code]
    to get highlighting and preserve formatting.
    If you don't understand my answer, don't ignore it, ask a question.

  4. #4
    Junior Member
    Join Date
    Nov 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

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

  5. #5
    Forum VIP
    Join Date
    Jun 2011
    Posts
    317
    My Mood
    Bored
    Thanks
    47
    Thanked 89 Times in 74 Posts
    Blog Entries
    4

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

    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.

  6. #6
    Junior Member
    Join Date
    Nov 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post whats wrong with my code

    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);
    }

    }

    }

  7. #7
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    25,042
    Thanks
    63
    Thanked 2,708 Times in 2,658 Posts

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

    is there is any wrong in this code?
    There is a variable with a null value. The location of the statement with the null variable will be shown in the stack trace.
    If you don't understand my answer, don't ignore it, ask a question.

  8. #8
    Junior Member
    Join Date
    Nov 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

    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

  9. #9
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    25,042
    Thanks
    63
    Thanked 2,708 Times in 2,658 Posts

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

    You need to copy the FULL text of the error message and paste it here.


    Please edit your post and wrap your code with code tags:
    [code=java]
    YOUR CODE HERE
    [/code]
    to get highlighting and preserve formatting.
    If you don't understand my answer, don't ignore it, ask a question.

  10. #10
    Junior Member
    Join Date
    Nov 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

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

    --- Update ---

    if there is any correction on line number 40

  11. #11
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    25,042
    Thanks
    63
    Thanked 2,708 Times in 2,658 Posts

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

    What code is on line 40? What variable on that line has the null value? Then backtrack in the code to see why that variable does not have a valid value.

    Please edit your post and wrap your code with code tags:
    [code=java]
    YOUR CODE HERE
    [/code]
    to get highlighting and preserve formatting.
    If you don't understand my answer, don't ignore it, ask a question.

  12. #12
    Junior Member
    Join Date
    Nov 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

    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




    ???

  13. #13
    Forum VIP
    Join Date
    Jun 2011
    Posts
    317
    My Mood
    Bored
    Thanks
    47
    Thanked 89 Times in 74 Posts
    Blog Entries
    4

    Default Re: JAVA CODE FOR IMPORTING XLS FILE INTO MYSQL

    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.

Similar Threads

  1. Convert DOC,XLS to PDF with Java
    By comm in forum File I/O & Other I/O Streams
    Replies: 3
    Last Post: July 2nd, 2013, 04:10 AM
  2. Replies: 10
    Last Post: April 7th, 2013, 05:42 PM
  3. Replies: 1
    Last Post: March 15th, 2013, 10:28 AM
  4. [SOLVED] i cant make a java jar file which can search data from mysql
    By talha07 in forum JDBC & Databases
    Replies: 6
    Last Post: January 20th, 2011, 06:09 AM
  5. Replies: 8
    Last Post: January 6th, 2010, 09:59 AM

Tags for this Thread