Results 1 to 2 of 2
  1. #1
    raj.mscking@gmail.com's Avatar
    raj.mscking@gmail.com is offline Senior Member
    Join Date
    Jan 2012
    Location
    TamilNadu
    Posts
    290
    Rep Power
    3

    Question in java (csv into mysql) making problem in date formate

    Good Morning Sir!

    in my java project, i converted mysql into csv file formate for date transferring purpose,

    By default the csv file store date as dd/mm/yyy from yyyy/mm/dd

    now i try to upload the csv into mysql.

    But the exception say about the date format problem..,

    This is my code

    csv into mysql



    Java Code:
    private void jLabel126MouseClicked(java.awt.event.MouseEvent evt) {                                       
    // TODO add your handling code here:
        Statement stmt;
        String query;
        try {
            JOptionPane.showMessageDialog(null, "You Have To Select Employee & Employee Familly Files To Compleate This Entry");
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/payslip", "root", "root");
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            String dds = JOptionPane.showInputDialog(null, "Enter FileName To Load Employee Dtails", "");
            if(!dds.equals(""))
            {
            String fn = "C:/" + dds + ".csv";
            query = "LOAD DATA INFILE '" + fn + "' INTO TABLE newemp FIELDS TERMINATED BY ',' (EmployeeID,Name,"
                    + DateOfBirth,"
                    + "DateOfJoin,Designation,Guardian,MailID,ContactNo,TotalExperience,CompanyDetails,"
                    + "IdMark,Qualification,UserName,PassWord,Photo,BasicPay,InTime,OutTime,LandLineNo,"
                    + "ResignedDate,Department,Branch)";
            stmt.executeUpdate(query);
            JOptionPane.showMessageDialog(null, "Uploaded Sceeceefully");
            }
            else
            {
                JOptionPane.showMessageDialog(null,"Enter Valid File Name");
            }
    
        } catch (Exception r) {
            JOptionPane.showMessageDialog(null, r);//"Check Your File Please");
        }
    }

    i try with DATE_FORMAT(DateOfBirth, '%Y/%m/%d') too. But also making the same problem sir..,


    Java Code:
    private void jLabel126MouseClicked(java.awt.event.MouseEvent evt) {                                       
    // TODO add your handling code here:
        Statement stmt;
        String query;
        try {
            JOptionPane.showMessageDialog(null, "You Have To Select Employee & Employee Familly Files To Compleate This Entry");
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/payslip", "root", "root");
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            String dds = JOptionPane.showInputDialog(null, "Enter FileName To Load Employee Dtails", "");
            if(!dds.equals(""))
            {
            String fn = "C:/" + dds + ".csv";
            query = "LOAD DATA INFILE '" + fn + "' INTO TABLE newemp FIELDS TERMINATED BY ',' (EmployeeID,Name,"
                    + "DATE_FORMAT(DateOfBirth, '%Y/%m/%d') as DateOfBirth,"
                    + "DateOfJoin,Designation,Guardian,MailID,ContactNo,TotalExperience,CompanyDetails,"
                    + "IdMark,Qualification,UserName,PassWord,Photo,BasicPay,InTime,OutTime,LandLineNo,"
                    + "ResignedDate,Department,Branch)";
            stmt.executeUpdate(query);
            JOptionPane.showMessageDialog(null, "Uploaded Sceeceefully");
            }
            else
            {
                JOptionPane.showMessageDialog(null,"Enter Valid File Name");
            }
    
        } catch (Exception r) {
            JOptionPane.showMessageDialog(null, r);//"Check Your File Please");
        }
    my csv file format is

    Java Code:
    102	selva	8/6/2012	6/2/2012	Teacher	christ	raj@gmail.com	994460858
    what change i have to do with this code sir guide me please..,

    Thank you..,

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default Re: in java (csv into mysql) making problem in date formate

    First off, that is tab separated and yet you are telling the LOAD DATA command that your fields are comma separated, which is going to cause problems.
    Next, what version of MYSQL are you using? If it's post-5.0.3 then you can use the SET command:
    LOAD DATA INFILE 'yourfile.csv'
    INTO TABLE your_table
    (column1, @dateval1)
    SET column2 = str_to_date(@dateval1, '%m/%d/%Y');
    Note str_to_date...you are converting from a string to a date, not formatting an existing date.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. From MySQL's UNIX_TIMESTAMP() value to Date
    By Ace in forum New To Java
    Replies: 3
    Last Post: 12-10-2011, 04:59 PM
  2. Java 7 java.util.Date vs java.sql.Date problem
    By pavel_skala in forum Advanced Java
    Replies: 0
    Last Post: 11-08-2011, 12:26 PM
  3. Replies: 4
    Last Post: 08-01-2011, 11:29 AM
  4. Compare Mysql date with current date
    By gioarvan in forum JavaServer Pages (JSP) and JSTL
    Replies: 2
    Last Post: 04-24-2011, 06:19 PM
  5. Making an Applet use a MySQL Database
    By Bomber_Will in forum Java Applets
    Replies: 5
    Last Post: 11-30-2008, 05:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •