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

    Question (Excell To MySQL) In java How to correct the fallowing error

    Happy To Meet You All!

    In my previous forums by yours help, i did successfully MySQL to Excell coversion in java

    Now i want's to reverse process for transferring data from Excell to MySQL in java

    My Java Code is..,

    Java Code:
    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    // TODO add your handling code here:
               Statement stmt;
               String query;
         try
                    {
                         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 fn="C:/Users/read/Desktop/hai.xls";
                         query = "LOAD DATA INFILE '"+fn+"' INTO TABLE aprvlev (ApprovalDate,ApprovalReason);";
                         stmt.executeUpdate(query);
                         JOptionPane.showMessageDialog(null, "Sceeceefully Stored From Excell TO MySQL");
                    }
                    catch(Exception r)
                    {
                       System.out.println(r);
                    }
    }
    The Above code is running
    and
    Showing the Error of..,

    Java Code:
    com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: 'ࡱ' for column 'ApprovalDate' at row 1
    But in my Excell Sheet the values is in correct format only

    My Excell Format is..,

    Java Code:
    ApprovalDate	ApprovalReason
    2012-12-31  	Newyear eve
    2013-01-01	        newyear
    2012-06-01	        Test leave

    what mistake i did, and how to correct it..,
    Guide me please sir..,
    Thank you..,

  2. #2
    wsaryada is offline Senior Member
    Join Date
    Jun 2007
    Location
    Bali, Indonesia
    Posts
    757
    Rep Power
    8

    Default Re: (Excell To MySQL) In java How to correct the fallowing error

    I don't think you can load data in an excel file directly into MySQL. The file should be a plain text in a CSV format.

  3. #3
    raj.mscking@gmail.com's Avatar
    raj.mscking@gmail.com is offline Senior Member
    Join Date
    Jan 2012
    Location
    TamilNadu
    Posts
    274
    Rep Power
    3

    Default Re: (Excell To MySQL) In java How to correct the fallowing error

    Thank you for your response sir!
    i will try by as you said,

    But! one more doubt sir,
    By the blow code, i can read the Excell and get out put in command prompt..,
    is it possible to upload in MySQL sir?..,

    Java Code:
    
    /**
     *
     * @author Raj
     */
    import java.io.FileInputStream;
    import java.util.Iterator;
    import java.util.Vector;
    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 ReadExcelFile 
    {
        public static Vector ReadCSV(String fileName) 
        {
            Vector cellVectorHolder = new Vector();
    
            try {
                FileInputStream myInput = new FileInputStream("C:/Users/read/Desktop/praise the lord.xls");
    
                POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
    
                HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
    
                HSSFSheet mySheet = myWorkBook.getSheetAt(0);
    
                Iterator rowIter = mySheet.rowIterator();
    
                while (rowIter.hasNext()) {
                    HSSFRow myRow = (HSSFRow) rowIter.next();
                    Iterator cellIter = myRow.cellIterator();
                    Vector cellStoreVector = new Vector();
                    while (cellIter.hasNext()) {
                        HSSFCell myCell = (HSSFCell) cellIter.next();
                        cellStoreVector.addElement(myCell);
                    }
                    cellVectorHolder.addElement(cellStoreVector);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return cellVectorHolder;
        }
    
        private static void printCellDataToConsole(Vector dataHolder) {
            //LinkedList<String> row = new LinkedList<>;
            Vector row = new Vector();
            for (int i = 0; i < dataHolder.size(); i++) {
                Vector cellStoreVector = (Vector) dataHolder.elementAt(i);
                for (int j = 0; j < cellStoreVector.size(); j++) {
                    HSSFCell myCell = (HSSFCell) cellStoreVector.elementAt(j);
                    String stringCellValue = myCell.toString();
                    System.out.print(stringCellValue + "\t");
                }
                System.out.println();
            }
        }
        public static void main(String[] args) {
    
            //String fileName = "C:/Users/read/Desktop/praise the lord.xls";
            Vector dataHolder = ReadCSV("C:/Users/read/Desktop/praise the lord.xls");
            printCellDataToConsole(dataHolder);
        }
    }

    Can you guide me sir..,

  4. #4
    wsaryada is offline Senior Member
    Join Date
    Jun 2007
    Location
    Bali, Indonesia
    Posts
    757
    Rep Power
    8

    Default Re: (Excell To MySQL) In java How to correct the fallowing error

    You can read it because you are reading it using Apache POI, and I don't think MySQL will do the same. So you need to convert your excel file into cvs first. You can start by modifying your previous program. Instead of printing out the excel data to the screen, write them to a text file in a csv format. And then you can load this csv into MySQL which MySQL will gladly accept your file.

  5. #5
    raj.mscking@gmail.com's Avatar
    raj.mscking@gmail.com is offline Senior Member
    Join Date
    Jan 2012
    Location
    TamilNadu
    Posts
    274
    Rep Power
    3

    Default Re: (Excell To MySQL) In java How to correct the fallowing error

    Sir i Try in different way, it is working well

    Code for Export

    Java Code:
     try
                    {
                         Class.forName("com.mysql.jdbc.Driver");
                         Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/payslip", "root", "root");
                         String mnd="C:/Users/read/Desktop/data.CSV";
                         String dd="jesus";
                         PreparedStatement ps = conn.prepareStatement("SELECT * INTO OUTFILE '"+mnd+"' FIELDS TERMINATED BY ',' FROM attents where UserName ='"+dd+"';");
                         conn.close();
                          JOptionPane.showMessageDialog(null, "File saved As CSV");
                    }
                    catch(Exception r)
                    {
                        JOptionPane.showMessageDialog(null, r);
                    }


    Code for Import

    Java Code:
     Statement stmt;
           String query;
         try
                    {
                         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 fn="C:/Users/read/Desktop/data.CSV";
                         //query = "LOAD DATA INFILE '"+fn+"' INTO TABLE aprvlev (ApprovalDate,ApprovalReason);";
                         query = "LOAD DATA INFILE '"+fn+"' INTO TABLE attents FIELDS TERMINATED BY ',';";
                         stmt.executeUpdate(query);
                         JOptionPane.showMessageDialog(null, "Sceeceefully Stored From CSV TO MySQL");
                    }
                    catch(Exception r)
                    {
                        JOptionPane.showMessageDialog(null, r);
                    }

    Thank you For You All Sir..,

Similar Threads

  1. How to Correct Error In Desktop Application
    By raj.mscking@gmail.com in forum NetBeans
    Replies: 3
    Last Post: 03-20-2012, 11:57 AM
  2. Replies: 4
    Last Post: 03-04-2012, 11:26 PM
  3. Mysql Error Password Field
    By Candesco in forum New To Java
    Replies: 2
    Last Post: 12-28-2011, 09:09 AM
  4. Replies: 0
    Last Post: 09-15-2011, 09:20 AM
  5. error with connection Mysql
    By ahmadn in forum Java Applets
    Replies: 5
    Last Post: 06-17-2011, 10:54 AM

Tags for this Thread

Posting Permissions

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