reading the records from a .xlsx file and storing those records in database table

reading the records from a .xlsx file and storing those records in database table

Here is my requirement, I want to read the records from a .xlsx file and store that records in database table. I tried like this

public class StoreFile {
    public int storeFileRecords(String saveFile,ServletContext application){
        Connection con=null;
        Statement st=null;
        Properties props=null;
        String bytesToString=null;
        try {
            props=new Properties();
            props.load(application.getResourceAsStream("databasedetails.properties"));
            String url=props.getProperty("url");
            String userName=props.getProperty("userName");
            String password=props.getProperty("password");
            String driverClassName=props.getProperty("driverClassName");

                Class.forName(driverClassName);
                con = DriverManager.getConnection(url,userName,password);
                st=con.createStatement();
                FileInputStream fstream = new FileInputStream(saveFile);
                DataInputStream din = new DataInputStream(fstream);
                BufferedReader br = new BufferedReader(new InputStreamReader(din));
                String strLine;
                ArrayList<String> list=new ArrayList<String>();
                while ((strLine = br.readLine()) != null){
                    if(saveFile.endsWith(".xlsx")){
                        byte[] b=strLine.getBytes();
                        System.out.println("b: "+b);
                        bytesToString=new String(b);
                        System.out.println("bytesToString: "+bytesToString);
                        list.add(strLine);
                    }else{
                        strLine=strLine.toString();
                        System.out.println("strLine: "+strLine);
                        list.add(strLine);
                    }
                }
                Iterator<String> itr=null;
                System.out.println("list: "+list);
                int k=0;
                for (itr=list.iterator(); itr.hasNext(); ){
                    String str=itr.next().toString();
                    String [] splitSt=null;
                    if(saveFile.endsWith(".csv"))
                        splitSt =str.split(",");
                    if(saveFile.endsWith(".txt"))
                        splitSt =str.split(" ");
                    if(saveFile.endsWith(".xlsx"))
                        splitSt =str.split(",");
                    String id=splitSt[0];
                    String name=splitSt[1];
                    String age=splitSt[2];
                    System.out.println("id: "+id);
                    System.out.println("name: "+name);
                    System.out.println("age: "+age);
                    String query="insert into student values("+id.trim()+",'"+name.trim()+"',"+age.trim()+")";
                    System.out.println("query: "+query);
                    if(id.equalsIgnoreCase("sid")) continue;                
                    k+=st.executeUpdate(query);
                }               
                return k;
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return 0;
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return 0;
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return 0;
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return 0;
            }
            finally{
                try {
                    st.close();
                    con.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }               
            }
    }   
}

Here the problem is after reading the records line by line the data is not converted to String. And i want to know what is the default seperator of xlsx file. Which seperator we have to use in case of xlsx file. I have used , as a seperator.

View Answers

June 30, 2011 at 3:29 PM

Try the following code:

import  java.io.*;  
 import java.sql.*;
 import java.text.*;
 import  org.apache.poi.hssf.usermodel.HSSFSheet  ;
 import  org.apache.poi.hssf.usermodel.HSSFWorkbook ; 
 import  org.apache.poi.hssf.usermodel.HSSFRow  ;
 import  org.apache.poi.hssf.usermodel.HSSFCell ; 
 import org.apache.poi.ss.usermodel.*; 
 public class ReadExcelFile{
 public static void main(String[] args)throws Exception{
  short a=0;
  short b=1;
  short c=2;
  short d=3;
  int i=0;
  Class.forName("com.mysql.jdbc.Driver");
         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
         Statement st=conn.createStatement();

    String value1=" ", value2=" ",value3 =" ", value4 =" ";
    String filename  ="C:/hello.xlsx"; 
    if(filename != null && !filename.equals("")){
    try{
    FileInputStream fs =new FileInputStream(filename);
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    for(int k = 0; k < wb.getNumberOfSheets(); k++){
    int j=i+1;
    HSSFSheet sheet = wb.getSheetAt(k);
    int rows  = sheet.getPhysicalNumberOfRows();
    for(int r = 1; r < rows; r++){
    HSSFRow row   = sheet.getRow(r);
    int cells = row.getPhysicalNumberOfCells(); 
    HSSFCell cell1  = row.getCell(a);
      value1 = cell1.getStringCellValue();
    HSSFCell cell2  = row.getCell(b);
      value2 = cell2.getStringCellValue();
    HSSFCell cell3  = row.getCell(c);
      value3 = cell3.getStringCellValue();
    HSSFCell cell4  = row.getCell(d);
      value4 = cell4.getStringCellValue();

    System.out.println(value1+"\t"+value2+"\t"+value3+"\t"+value4);
    st.executeUpdate("insert into student(firstname,lastname,email,address) values('"+value1+"','"+value2+"','"+value3+"','"+value4+"')");
    }
        i++;
     }
    }
    catch(Exception e){
    System.out.println(e);
    }
   }
  }
 }









Related Tutorials/Questions & Answers:
reading the records from a .xlsx file and storing those records in database table
reading the records from a .xlsx file and storing those records in database table  Here is my requirement, I want to read the records from a .xlsx file and store that records in database table. I tried like this public class
storing records which of a file into a table of mssql database
storing records which of a file into a table of mssql database  I have a requirement like this, A file contains some records with headers same as column names of a table. I have to store those records into a table
Advertisements
Storing records of a file inside database table in java
Storing records of a file inside database table in java  Here is my requirement, I have a file which contains some number of records like... the records inside the database except headings (Here sid,sname,age are headings
Fetch user records from a table in database
Fetch user records from a table in database  Hi. I have a field in database named stages. its datatype is varchar(60). It contains values chennai,trichy,kanchipuram for a single record. I have to retrieve these data from
Fetch Records from SQL database and convert into XML file
Fetch Records from SQL database and convert into XML file  Hi Experts... lets say 10 records from table / view then you need to Update the selected... provide me the code to FULFIL my requirement.... 1) Fetch all the records from
how to display records from database
how to display records from database  I want to display records from database in tables, the database is having 2000 records and i want to display 20 records at a time and to use next and previous link buttons to show
retrieving newly added records from mssql database and display in a jsp
from mssql database table and display those records in a jsp.And i have to delete these 10 records from the jsp and retrieve the next recently added 10 records...retrieving newly added records from mssql database and display in a jsp 
deleting all records from a table in mysql
deleting all records from a table in mysql  Hi, I am finding code for deleting all records from a table in mysql. How to delete all rows in mysql...; This will delete all the records from the table. But if you are using auto
deleting all records from a table in mysql
deleting all records from a table in mysql  Hi, I am finding code for deleting all records from a table in mysql. How to delete all rows in mysql...; This will delete all the records from the table. But if you are using auto
PHP WHERE clause example to fetch records from Database Table
on the MYSQL Server and will fetch the results from the database table employee... the data within the MYSQL database table. You can also fetch the results using...) To test the code we are using existing database table. You can create your own
Reading Text file and storing in map
Reading Text file and storing in map  Hi I have multiple text files. I want to read thoses files and store those records in map. Map will be of "LinkedHashMap<String, Map<String, String>>" type. Please let me know
Backup selected records into txt file
to copies the selected records or rows from a table into text file. Further, the text file is used as backup file, whenever there is loss of records or rows from table. This text file is used to recover the records in the database  
Insert Records in Table
Insert Records in Table       The Insert data in Table is used to insert  records or rows into the table in database. The Syntax used to insert records into a table
problem in reading 10000 records in java
problem in reading 10000 records in java  Hi I have a huge records 10000 records ,while reading it show some error only i can able to read 2000 recds and need to display all the recods in jsper reports Thanks Gopi
Write records into text file from database
Write records into text file from database You have already learnt how to insert records from text file to database. But here we are going to retrieve records from database and store the data into the text file. For this purpose, we have
retrieve the records from one table to another table by using cursors
retrieve the records from one table to another table by using cursors   Hi I'm fresher to backend(mysql),my requirement is retrieve the records from one table to another table by using cursors.The following is my procedure
Delete a Records in Database
from the table. The generalized Syntax  used for delete query in database... to delete the records or rows from a table followed by where clause... Delete a Records in Database   
Upload csv or .xlsx file from JSP form to Database Table using servlet
Upload csv or .xlsx file from JSP form to Database Table using servlet  dear sir, i need the Servlet code that reads the .xlsx or CSV excel file and stores it into the oracle database table. Sir Its very Urgent I have
displaying List of records from database in a jsp using ajax
displaying List of records from database in a jsp using ajax  Sir, I need to retrieve the records from the database for every 7 seconds and display those records in a jsp.Following is my code. x.jsp: <%@page import
Deleting Records using the Prepared Statement
the records from the database table by using the PreparedStatement interface... for deleting the records from the database table. Brief description below... after sometime so we need to delete those records. We can do it very
How to download web page table data, export the table records in an excel file and save
How to download web page table data, export the table records in an excel file and save  i have a web page(.jsp) which contains the table of 4 to 5 columns. i m displaying the table using in my jsp page. below this table i want
how to move records from one table to other based on its creation time
how to move records from one table to other based on its creation time  Hi all,, As part of my requirement ,the records in one table have to move to second table based on the record creation time means the records should
Update Records in Database
, the code run a select query to retrieve the records from a table country... Update Records in Database       The Update Records in database is used to modify
Backup selected records into txt file
the selected records or rows from a table into text file. Further, the text file is used as backup file, whenever there is loss of records or rows from table...; The Backup Query create a backup copy of records from table 'stu_table' where
JDBC : Duplicate records in a table
JDBC : Duplicate records in a table In this tutorial, you will learn how to find the duplicate records in a table. Duplicate records in a table : You can check for duplicate records of table. You can group record by any of fields
how to write a query for adding records in database
how to write a query for adding records in database  How write fire query in JSP for adding records in database
data insertion from xml file to database table
data insertion from xml file to database table  Hi all, I have data in the XML file. I need to insert it into table in the database using servlet. so please reply me . ThankYou
Combining records from two or more orders files into a single ordered file is called
Combining records from two or more orders files into a single ordered file is called  Combining records from two or more orders files into a single ordered file is called? 1. Menu 2. Merging 3. Taking 4. All of the above 5
displaying List of records from database in a jsp using ajax, onclick it should display the results ?? its urgent can u help me
displaying List of records from database in a jsp using ajax, onclick it should display the results ?? its urgent can u help me   displaying List of records from database in a jsp using ajax, onclick it should display the results
update the selected records from view immediatly
update the selected records from view immediatly  Hi I am... Table as soon as i get the data form the view to excel. Is there any way I can update the records immedialty in view as soon as see the data form view. Thank
Writing Log Records to a Log File
Writing Log Records to a Log File   ... and severe that have log records. Log records are written into a log file... is exist then log records will be written into a log file and it displays
Need to Remove Duplicate Records from Excel Sheet
Need to Remove Duplicate Records from Excel Sheet  Need to Remove Duplicate Records from Excel Sheet. I have one excel sheet having two fields... empnum rating (without using sql query have to remove records from excel using java
Mysql From Table
Mysql From Table       Mysql From Table is used to specify the table from which the records... 'from' is used to specify the table 'Stu' from which records is retrieved
How to delete records from jtabel - Swing AWT
How to delete records from jtabel  hello I am using jtabel to diaplay recorda of file using abstruct data model. i used vector in model... number). also tell me ho to genrate serial number for file records.  Hi
Mysql Find Duplicate Records
an example from 'Mysql Find Duplicate Records'. To grasp and understand example, the select query is used to return the records from table 'employee12'.  select * from employee12; Table structure
printing records from jsp - JSP-Servlet
printing records from jsp  Hi Plz tell me how to printing out pages from jsps with page numbers Thank you  Hi friend, For paging in Jsp visit to : http://www.roseindia.net/jsp/paging.shtml http
JDBC: Select Records Example
records from the table using JDBC API. Select Records : Select statement retrieves data from the table and display to the console. Data are retrieved... it into the console or also use it for another operations. For selecting table records
Write Text File to Table
text file that have to be inserted the records into a database table. This program... the records of a simple text file and write (insert) into a simple table in MySQL database. All records are written in the simple text file and again, if you need
How to display mysql database records as per clock timing?
How to display mysql database records as per clock timing?  I want to display database records as per clock timing
Data displaying with limited records in jsp
Data displaying with limited records in jsp  How to display table with limited 10 records , after clicking next button another 10 records from database upto last record please help me
to fetch data from ms word and storing into database
to fetch data from ms word and storing into database  i want to know how to fetch datafields from ms word and storing into database??? please answer soon .its urgent
Count Records using the Prepared Statement
to count all records of the database table by using the PreparedStatement... will know that how many records in a database table then you get easily with the help... the records of database table by using the PreparedStatement. For this, firstly
Inserting Records using the Prepared Statement
and deleting the records from the database table. Here is the code of program... to learn how we will insert the records in the database table by using... This program will perform the work for inserting the records in 'movies' database table
Writing to and reading from a binary file in java.
Writing to and reading from a binary file in java.  I have written... work, but the records obtained after reading the binary file are much less than... the binary file from another program as follows: m_dis = new DataInputStream
reading a csv file from a particular row
reading a csv file from a particular row  how to read a csv file from a particular row and storing data to sql server by using jsp servlet
JDBC Select All Records Example
JDBC Select All Records Example In this tutorial we will learn how select all records from the table use mysql JDBC driver. This tutorial example  for select  all records from table if exist and defined how the records fetch
Arrange a Column of Database Table
that arrange the records of database table in descending order. The descending...; to arrange the records of database table in descending order. The SELECT... an example with code that arrange the records of database table in descending order
Reading a file from Jar JAVA
Reading a file from Jar JAVA  I have added one excel sheet into a jar file. can anybody tell me how i can read that file. actually when i am running code from eclipse i able to read it but when i am adding that jar file
Reading a file from Jar JAVA
Reading a file from Jar JAVA  I have added one excel sheet into a jar file. can anybody tell me how i can read that file. actually when i am running code from eclipse i able to read it but when i am adding that jar file
Reading string from file timed
Reading string from file timed  So I want to make a file reader/ buffered reader that reads a new line of the textfile, lets say every 30 second. Like it reads the first line, waiting 30 seconds, read the next line and so one

Ads