Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • How to Insert an Excel Table into Oracle Database

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 466
    Answer it

            Am trying to insert am excel table into oracle database and when i try i get issuesmy table to the database doesnt get formatted correct

                public class excelDatabaseFile {        
                               // String for sql method
                    static String s = "INSERT INTO myTableValues VALUES(?,?,?,?,?,?,?,?)";
                // excel
                    private static Workbook workbook;
                    //sheet inside excel
                    private static sheet;
                    // formating hte data(String,integer so on
                    private static DataFormatter dataFormatter;
                    //For performance reasons, this class keeps a cache of all previously calculated 
                    // intermediate cell values.
                    private static FormulaEvaluator evaluator;
                    public static void main(String[] args) throws Exception{
                // loading/registering drivers
                //getting connection
                        Connection con = DriverManager.getConnection(
                        try {
                            // file location
                            File file = new File(
                        // reading file data    
                            FileInputStream fis = new FileInputStream(file);
                            // creating a statement that provides a methods to execute queries with the // 
                            PreparedStatement preparedStatement = con.prepareStatement(s);
                            //creating a workbook for decting excel
                            workbook = WorkbookFactory.create(fis);
                // getting the sheet of excel (page one)
                            sheet = workbook.getSheetAt(0);
                            dataFormatter = new DataFormatter();
                            // It is intended for compilation of algebraic expressions involving functions.
                            evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                // array of keys for hashmap-my collection to store the data
                ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
                            //looping: first block is less than sheet last row get next row
                            for (int i = 1; i <= sheet.getLastRowNum(); i++)  
                                // row is first sheet get first row
                                Row row = sheet.getRow(i);
                //if row is not = to unknown/nothing 
                                if (row != null) {
                // create a hashmap to store data
                       HashMap<String, String> map = new HashMap<String, String>();
                // x = first cell in excel sheet; if first cell is less and equil to last row get next row
                                    for (int x = row.getFirstCellNum(); x <= row.getLastCellNum(); x++) 
                // Cell is called v and = to row get the cells
                                        Cell v = row.getCell(x);
                // myValue = converting what inside the cells to a string
                                        String myValue = dataFormatter.formatCellValue(v);
                                // first row = to first sheet get the rows
                                        Row rowfirst = sheet.getRow(0);
                                //Cell called headcell = first row of sheet get whats in side the cell
                                        Cell headcell = rowfirst.getCell(x);
                // formatting the map to put the string cell in a storage
                                       map.put(dataFormatter.formatCellValue(headcell), myValue);
            // extends the Statement This object can then be used to efficiently execute this statement //multiple //times.
                                        preparedStatement.setString(1, myValue);
                                        preparedStatement.setString(2, myValue);
                                        preparedStatement.setString(3, myValue);
                                        preparedStatement.setString(4, myValue);
                                        preparedStatement.setString(5, myValue);
                                        preparedStatement.setString(6, myValue);
                                        preparedStatement.setString(7, myValue);
                                        preparedStatement.setString(8, myValue);
                //adding map to list
                // closing file input stream
                // closing connection
                        catch(Exception e) 
                //exits current program by terminating running Java virtual machine.
           //   [1]:

 0 Answer(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: