How Do I load data from a CSV file to a table using java?

Question

I have a File called Customer_Info.csv which has sample data like :

CUST_ID FIRST_NAME LAST_NAME USER_TYPE REPORTS_TO_ID EMAIL FIRST_TIME_LOGGED_IN FIRST_TIME_LOGIN_DATE ADDRESS_LINE1 ADDRESS_LINE2 CITY STATE ZIP

10001 MICHELLE VILLEGAS3 Savings SRINATH MICHELLE.VILLEGAS3@NOBODY.COM Y 07-10-18 1050 WEST FIFTH STREET AZUSA IND 917023308

This is the table in which i need to store the data. I am storing everything in string format because it is just a staging table.

Create Table Customer_Info_ST
 (
 Cust_ID varchar(5),
 First_Name varchar(15),
 Last_Name varchar(15),
 User_Type varchar(10),
 Report_To_Id varchar(10),
 Email varchar(30),
 First_Time_Logged_In varchar(1),
 First_Time_Login_Date varchar(11),
 Address_Line1 varchar(30),
 Address_Line2 varchar(30),
 City varchar(15),
 State varchar(3),
 Zip varchar(12)
 );

I have written the following code to store it in the table.

public class CustInfoDataLoader {

static String sql, tableName;
private static PreparedStatement pstmt;
private static Statement stmt;
private static final String filePath = "Customer_Info.csv";

public static void main(String[] args) throws SQLException, FileNotFoundException, IOException, ParseException {

    readCsv("Customer_info_ST"); //Line 24 in my code 
    //readCsvUsingLoad();
}

private static void readCsv(String tableName) throws FileNotFoundException, IOException, SQLException, ParseException {
    CSVReader csvreader = null;

    try{
        Reader reader = Files.newBufferedReader(Paths.get(filePath));
        csvreader = new CSVReaderBuilder(reader).withSkipLines(1).build();
        sql = "insert into ? values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        ManageDBResource.createConnectionToDB();
        pstmt = ManageDBResource.conn.prepareStatement(sql);


        String[] rowData = null;

        while((rowData = csvreader.readNext()) != null) {

            pstmt.setString(1, tableName);
            int i = 2;
            String state = rowData[11];
            for (String data : rowData) {

                if(i == 9 && data != null && state != "IND") {
                    java.text.SimpleDateFormat source = new java.text.SimpleDateFormat("MM-dd-yyyy");
                    java.util.Date date = source.parse(data);
                    java.text.SimpleDateFormat target = new java.text.SimpleDateFormat("yyyy-MM-dd");
                    data = target.format(date).toString();
                }
                pstmt.setString(i++,data);
                //System.out.print(data + " ");
            }
            int result = pstmt.executeUpdate();
            //System.out.println();
            if(result == 1) {
                System.out.println("Data loaded Successfully.");

            }
        }
    }
    catch (Exception e) {
        System.out.println(e.getMessage());
    }
    finally {
        pstmt.close();   //Line 69 in my code file
        csvreader.close();
    }

}

and I get the following error:

Exception in thread "main" java.lang.NullPointerException
at CustInfoDataLoader.readCsv(CustInfoDataLoader.java:69)
at CustInfoDataLoader.main(CustInfoDataLoader.java:24)

oh I am so sorry line 24 - functioncall from main function Line 69 - pstmt.close() — vkp, Jul 19 '18 at 10:47
Sorry I mean can you point out which is line 69 and 24 in your question? A couple of comments in your code would do — Thum Choon Tat, Jul 19 '18 at 10:49
@ThumChoonTat added the comment to the exact line in the code. — vkp, Jul 19 '18 at 10:57
no when I commented out the code in finally block the whole code using csvreader object failed — vkp, Jul 19 '18 at 11:03
1) See [What is a stack trace, and how can I use it to debug my application errors?](http://stackoverflow.com/q/3988788/418556) & [What is a Null Pointer Exception, and how do I fix it?](http://stackoverflow.com/q/218384/418556) 2) For better help sooner, post a [MCVE] or [Short, Self Contained, Correct Example](http://www.sscce.org/). Hard code the CSV data in a `String` to remove the requirement for an input file. — Andrew Thompson, Jul 19 '18 at 11:48

score 2 · Answer 1 · edited Jul 21 '18 at 11:14

Can you close PreparedStatement after you executeUpdate()?

Like mentioned in the above example:

String sql = " INSERT INTO TABLE_(name,email,phone,id) VALUES(?,?,?,?) ";


try { 
    BufferedReader bReader = new BufferedReader(new 
    FileReader("1000rows.csv"));
    String line = ""; 
    while ((line = bReader.readLine()) != null) {
        try {

            if (line != null) 
            {
                String[] array = line.split(",+");
                for(String result:array)
                {
                    System.out.println(result);
//Create prepared Statement here and set them and execute them
            PreparedStatement ps = yourConnecionObject.createPreparedStatement(sql);
             ps.setString(1,str[0]);
             ps.setString(2,str[1]);
             ps.setString(3,str[2]);
             ps.setString(4,strp[3])
             ps.excuteUpdate();
             ps. close()
//Assuming that your line from file after split will follow that sequence

                }
            } 
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        finally
        {
           if (bReader == null) 
            {
                bReader.close();
            }
        }
    }
} catch (FileNotFoundException ex) {
    ex.printStackTrace();
}

why is that so? in your case the pstmt statement is created everytime in the loop. — vkp, Jul 19 '18 at 11:15
I commented out the pstmt.close() and csvreader.close() in the finally block. then the error rose from csvreader = new CSVReaderBuilder(reader).withSkipLines(1).build(); — vkp, Jul 19 '18 at 11:19
In of case of reading data from csv file i do not use any library. But in case of Xls and xlsx i use apache library. for example : List list = new File("file path").readLines() — Bharat Satija, Jul 19 '18 at 11:25

How Do I load data from a CSV file to a table using java?

1 Answers1