Results 1 to 10 of 10
  1. #1
    efoikonom is offline Member
    Join Date
    Apr 2013
    Posts
    10
    Rep Power
    0

    Default store data in mysql after reading from excel files

    i am trying to make o program where i will read the data from excel files and i will store them in a database. I am using eclipse as editor and mySQL. I am using APACHE POI to read the excel files and JDBC for the connection. The excel files have the structure as shown below:

    ID NAME SALARY STREET
    --- ---- ------ -------------
    321 TIM 1254 14 avenue
    121 PAUL 1265 28h oktovriou
    432 NICK 4521 papaflessa
    I have of course plenty of such files which contains many more rows and columns. The purpose of my program is to read the data, create table named as the name of the excel file and the fields of each table to be the first rows of the excel file. Afterwards the values will be the rest data of the excel file. In the code below i have managed to read them, show the data in the console. Afterwrds i am trying to call a database with JDBC but there i have problem when i create the table.

    Java Code:
    try
                {
                    String[] allFields;
                    String createTableStr = "CREATE TABLE" + createTableStr
                       + "(" + org.apache.commons.lang3.StringUtils.join(allFields,
                       ",") + ")";
    Could anyone help me?

    Thank you in advance!:)

    Java Code:
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.List;
    
    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.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.Cell;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class exam1 {
    
        @SuppressWarnings({ "unchecked", "unchecked" })
        static void main (String[] args) throws Exception {
    
            String filename = "C:\\Users\\Efi\\Documents\\test5.xls";
    
            List sheetData = new ArrayList();
            FileInputStream fis = null;
            try {
                fis = new FileInputStream(filename);
                HSSFWorkbook workbook = new HSSFWorkbook(fis);
                HSSFSheet sheet = workbook.getSheetAt(0);
    
                Iterator rows = sheet.rowIterator();
                while (rows.hasNext()) {
                    HSSFRow row = (HSSFRow) rows.next();
                    Iterator cells = row.cellIterator();
    
                    List data = new ArrayList();
                       while (cells.hasNext()) {
                       HSSFCell cell = (HSSFCell) cells.next();
                       data.add(cell);
                       }
                       sheetData.add(data);
                }
    
                       } catch (IOException e) {
                       e.printStackTrace();
                       } finally {
                       if (fis != null) {
                       fis.close();
                       }
                       }
    
        showExcelData(sheetData);
    
    
        @SuppressWarnings("unused")
        HashMap<String, String> tableFields = new HashMap();
              for (int i=0; i<sheetData.size();i++){
              List list = (List) sheetData.get(i);
                  for (int j=0; j<list.size(); j++){
                    Cell cell = (Cell) list.get(j);
                     if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.print(cell.getNumericCellValue());
    
                      }else if(cell.getCellType()==Cell.CELL_TYPE_STRING) {
    
    
                      System.out.print(cell.getRichStringCellValue());
                  } else if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN) {
                System.out.print(cell.getBooleanCellValue());
                        }
                    if (j < list.size() - 1) {
                System.out.print(", ");
                        }}}
                        }
    
        private static void showExcelData(List sheetData) {
    
            }
    
        @SuppressWarnings("unchecked")
        private HashMap parseExcelData (List sheetData){
    
                HashMap<String,Integer> tableFields = new HashMap();
                List list = (List) sheetData.get(0);
                for (int j=0; j<list.size(); j++){
                    Cell cell=(Cell) list.get(j);
                    tableFields.put(cell.getStringCellValue(),cell.getCellType());
            }
    
                return tableFields;
    
            }
    
    
    
        @SuppressWarnings({ "unchecked", "unchecked", "unchecked", "unchecked", "unused" })
        private String getCreateTable(String tablename, HashMap<String, Integer> tableFields){
            Iterator iter = tableFields.keySet().iterator();
            String str="";
            String[] allFields = new String[tableFields.size()];
            int i = 0;
            while (iter.hasNext()){
            String fieldName = (String) iter.next();
            Integer fieldType=(Integer)tableFields.get(fieldName);
    
            switch (fieldType){
            case Cell.CELL_TYPE_NUMERIC:
            str=fieldName + "INTEGER";
            break;
            case Cell.CELL_TYPE_STRING:
            str= fieldName + "VARCHAR(255)";
            break;
            case Cell.CELL_TYPE_BOOLEAN:
            str=fieldName + "INTEGER";
            break;
                }
            allFields[i++]= str;
            }
            return str; 
        }
    
        public Connection getConnection() throws SQLException {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/kainourgia","root", "root");
                Statement  stmt = con.createStatement();
                try
                {
                System.out.println( "Use the database..." );
                stmt.executeUpdate( "USE kainourgia;" );
                }
                catch( SQLException e )
                {
                System.out.println( "SQLException: " + e.getMessage() );
                System.out.println( "SQLState:     " + e.getSQLState() );
                System.out.println( "VendorError:  " + e.getErrorCode() );
                }
                try
                {
                    String[] allFields;
                    String createTableStr = "CREATE TABLE" + createTableStr
                       + "(" + org.apache.commons.lang3.StringUtils.join(allFields,
                       ",") + ")";
    
                    System.out.println( "Create a new table in the database" );
                    stmt.executeUpdate( createTableStr );
                    }
                catch( SQLException e )
                {
                System.out.println( "SQLException: " + e.getMessage() );
                System.out.println( "SQLState:     " + e.getSQLState() );
                System.out.println( "VendorError:  " + e.getErrorCode() );
                }
                }
                catch( Exception e )
                {
                System.out.println( ((SQLException) e).getSQLState() );
                System.out.println( e.getMessage() );
                e.printStackTrace();
                }
            return null;
                }
    
            }

  2. #2
    PhHein's Avatar
    PhHein is offline Senior Member
    Join Date
    Apr 2009
    Location
    Germany
    Posts
    1,430
    Rep Power
    7

    Default Re: store data in mysql after reading from excel files

    Quote Originally Posted by efoikonom View Post
    i have problem when i create the table.
    What problem do you have? Does it make your toaster explode? What is the SQL String to create the table?
    Math problems? Call 1-800-[(10x)(13i)^2]-[sin(xy)/2.362x]
    The Ubiquitous Newbie Tips

  3. #3
    efoikonom is offline Member
    Join Date
    Apr 2013
    Posts
    10
    Rep Power
    0

    Default Re: store data in mysql after reading from excel files

    store data in mysql after reading from excel files-untitled.png

    the problem is where the X is!
    What i have to change?

  4. #4
    PhHein's Avatar
    PhHein is offline Senior Member
    Join Date
    Apr 2009
    Location
    Germany
    Posts
    1,430
    Rep Power
    7

    Default Re: store data in mysql after reading from excel files

    What is the exact compiler error you get?
    Math problems? Call 1-800-[(10x)(13i)^2]-[sin(xy)/2.362x]
    The Ubiquitous Newbie Tips

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,842
    Rep Power
    19

    Default Re: store data in mysql after reading from excel files

    Please don't use screenshots.
    Post the relevant code, including the error message you are getting.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  6. #6
    efoikonom is offline Member
    Join Date
    Apr 2013
    Posts
    10
    Rep Power
    0

    Default Re: store data in mysql after reading from excel files

    I did't get any compiler error! When i am trying to run it it just show me this!

  7. #7
    efoikonom is offline Member
    Join Date
    Apr 2013
    Posts
    10
    Rep Power
    0

    Default Re: store data in mysql after reading from excel files

    Now i get this error: Error: Could not find or load main class connection

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,842
    Rep Power
    19

    Default Re: store data in mysql after reading from excel files

    Quote Originally Posted by efoikonom View Post
    I did't get any compiler error! When i am trying to run it it just show me this!
    That 'x' is an error.
    It's a compiler error.
    That's how Eclipse shows you tings that will not compile, which is also why it doesn't run.

    Hover over the underlined code and copy the error text.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  9. #9
    efoikonom is offline Member
    Join Date
    Apr 2013
    Posts
    10
    Rep Power
    0

    Default Re: store data in mysql after reading from excel files

    The error is in this code:

    try
    {
    String[] allFields;
    String filename;
    String createTableStr = "CREATE TABLE" + filename +"(" + org.apache.commons.lang3.StringUtils.join(allField s,
    ",") + ")";

    and it says that "The local variable filename / allFields may have not been initialized!
    But as filename i have initialized at the beginning of the program the path of the file.

  10. #10
    PhHein's Avatar
    PhHein is offline Senior Member
    Join Date
    Apr 2009
    Location
    Germany
    Posts
    1,430
    Rep Power
    7

    Default Re: store data in mysql after reading from excel files

    Java Code:
    String[] allFields; // <- not initialized
    String filename;// <- not initialized
    String createTableStr = "CREATE TABLE" + filename +"(" + org.apache.commons.lang3.StringUtils.join(allField s,",") + ")";
    Math problems? Call 1-800-[(10x)(13i)^2]-[sin(xy)/2.362x]
    The Ubiquitous Newbie Tips

Similar Threads

  1. Replies: 1
    Last Post: 01-24-2013, 09:30 AM
  2. Replies: 0
    Last Post: 05-11-2011, 11:49 AM
  3. How can i store a data from Excel sheet to an array.
    By deshmukh.niraj04 in forum New To Java
    Replies: 0
    Last Post: 05-03-2011, 06:33 AM
  4. Replies: 5
    Last Post: 08-21-2009, 11:03 AM
  5. How to read data from excel and store it in db
    By saran123 in forum New To Java
    Replies: 5
    Last Post: 10-03-2008, 10:19 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
  •