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 this
  • student.csv

    1 sid sname age 2 1 suresh 24 3 2 surya 25 4 3 ashok 27

I have to display a browse button and whenever user selects student.csv and clicks on submit button, i have to store the records inside the database except headings (Here sid,sname,age are headings in student.csv file).Please help me in resolving this problem.

Thanks & Regards

Suresh

View Answers

June 28, 2011 at 5:20 PM

import java.io.*;
import java.sql.*;
import java.util.*;
import jxl.Cell; 
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings; 
import jxl.read.biff.BiffException;

public class ReadCSV{
ArrayList<String> list1=new ArrayList<String>();
ArrayList<String> list2=new ArrayList<String>();
ArrayList<String> list3=new ArrayList<String>();

public void getHeadingFromXlsFile(Sheet sheet){
    int columnCount = sheet.getColumns();
    for(int i = 0; i < columnCount; i++){
        System.out.println("Coloumn Count : "+sheet.getCell(i, 0).getContents());
    }
}
public void contentReading()throws Exception{
    WorkbookSettings ws = null;
    Workbook workbook = null;
    Sheet s = null;
    Cell rowData[] = null;
    int rowCount = 0;
    int columnCount = 0;
    int totalSheet = 0;


        ws = new WorkbookSettings();
        ws.setLocale(new Locale("en", "EN"));
        workbook = Workbook.getWorkbook(new File("C:/data.csv"), ws);

        s = workbook.getSheet(0);
        rowCount = s.getRows();
        columnCount = s.getColumns();
        for(int i = 1; i < rowCount; i++){
            rowData = s.getRow(i);
            if(rowData[0].getContents().length() != 0){ 
                for(int j = 0; j < columnCount ;j++){
                 switch(j){
                    case 0:
                        System.out.println("ID: "+rowData[j].getContents());
                        list1.add(rowData[j].getContents());
                        break;
                    case 1:
                        System.out.println("Name: "+rowData[j].getContents());
                        list2.add(rowData[j].getContents());
                        break;
                    case 2:
                        System.out.println("Age: "+rowData[j].getContents());
                        list3.add(rowData[j].getContents());
                        break;
                       }
                    }
                  }
        }
    workbook.close(); 
    try{
   Class.forName("com.mysql.jdbc.Driver");
         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/roseindia", "root", "root");
         Statement st=conn.createStatement();
         for(int i=0;i<list1.size();i++){
             String id=list1.get(i).toString();
             String name=list2.get(i).toString();
             String age=list3.get(i).toString();

         st.executeUpdate("insert into student(id,name,age) values('"+id+"','"+name+"','"+age+"')");
         }
        System.out.println("Inserted successfully!");
    }
    catch(Exception e){
    System.out.println(e);
      }

}
    public static void main(String[] args)throws Exception {
    try {
        ReadCSV xlReader = new ReadCSV();
        xlReader.contentReading();
    }
    catch (Exception e) {
        e.printStackTrace();
    }
  }
}









Related Tutorials/Questions & Answers:
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
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
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
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... the field table. Actually they are separated by comma. I want to take the values
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
storing csv into oracle database
storing csv into oracle database  i want jsp code for storing csv file into oracle database
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... and output XML file with all records into a single XML with containing as Row's
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
Uploading a Software and storing in the database
Uploading a Software and storing in the database  I want to upload a software(may be of maximum 20mb) through JSP, and store it in the database. The coding present in the site for uploading and storing in the database
php csv file uploding into mysql database table.
php csv file uploding into mysql database table.  hai friends, i... fields. i have one mysql database table with two files fields, when ever i am uploding csv file, i want to fetch the datas in corresponding fields in table. can
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
storing xml into database - XML
storing xml into database   hi i have an xml file it contains... .......with it i know how to persist a simple xml file into data base but i m finding some difficulty to start with i m sending u the xml file
Remote file storing in mobile
Remote file storing in mobile  Hai.. i want to do my semester project..i have selected a topic "Remote file storing in mobile"..it is in the form like any Java enabled GPRS based mobile phone users can store their images,video
Remote file storing in mobile
Remote file storing in mobile  Hai.. i want to do my semester project..i have selected a topic "Remote file storing in mobile"..it is in the form like any Java enabled GPRS based mobile phone users can store their images,video
Backup selected records into txt file
to copies the selected records or rows from a table into text file. Further... table. This text file is used to recover the records in the database  ... records into txt file, we create a table 'Stu_Table'. The create table is used
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
PHP WHERE clause example to fetch records from Database Table
the data within the MYSQL database table. You can also fetch the results using... on the MYSQL Server and will fetch the results from the database table employee...) 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
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
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
searching inside a file for details
searching inside a file for details  i created a file with name,country,state etc...when i'm searching for the name, i have to get other details like country,state etc...please do help
Export Database table to CSV File
Export Database table to CSV File In this tutorial, you will learn how to retrieve data from database and save it to CSV File. The Comma-separated values... as writing into a CSV file. Here is a database table is to be extracted:ADS
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
getting and storing dropdown list in database in jsp
getting and storing dropdown list in database in jsp  i have a drop... database. but unable to store the selected value in database table. please help... lyk this. but it storing null value in the database. :\ what to do
Delete a Records in Database
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
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
Tag Inside Table in HTML
Tag Inside Table in HTML       The Tutorial illustrates an example from Tag Inside Table in HTML.In this code we define a HTML page and show you to create a tag inside a Table
Update Records in Database
Update Records in Database       The Update Records in database is used to modify..., the code run a select query to retrieve the records from a table country
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
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... Backup selected records into txt file   
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
create table in mysql of split file created in java
create table in mysql of split file created in java  i have created splite file in java. now i want to create table from splited data how to do
complex xml parsing and storing in database - XML
complex xml parsing and storing in database  Hi Experts ,i want to parse my xml document and store it in mysql database. This is my code. How to parse this complex data. EDI_DC40 800 0000000000557748
Storing content from file path to an array
Storing content from file path to an array  Hi, I have a path... would like to access the "Animation.txt" file from this path and store the contents in this text file to an array. I am using jsp. I can access my path but how
Java to create table in jsp file that include other jsp file
Java to create table in jsp file that include other jsp file  String jspContent = "table" += "tr" += "td" += "jsp:include page='fileSource'" //this line is not working properly... += "/td" += "/tr>" += "/table" Please refer
getting problem in storing & retrieving value in database
getting problem in storing & retrieving value in database  Hello everyone i am doing a project where my back end is ms access & i want to store value like 5353250214 so if i store it as long int in access it wont take it &
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
How to display data fom MySQL DataBase-table in to JSP file by submitting a value in combobox.
How to display data fom MySQL DataBase-table in to JSP file by submitting... DataBase/DB Name:lokesh; Table Name:TR_list; columns:nodename,packageno,TR... to create a viewTR.jsp file in netbeans IDE, where i have to : 1.connect to DB
storing details in database on clicking submit button - JSP-Servlet
storing details in database on clicking submit button  I am using JSP... database on clicking submit button. I am unable to do this.Can u tell me how to code......Tell me where an what code should I write to store the values in data base table
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
retrieving newly added records from mssql database and display in a jsp
retrieving newly added records from mssql database and display in a jsp  Here is my requirement, I have to retrieve recently added 10 records from mssql database table and display those records in a jsp.And i have to delete
Shifting txt file to database - Java Beginners
and put into database using Java/JSP. Database table is as below ID numeric...Shifting txt file to database   Question Details: I want to shift data from txt file to Database. The data is written in the following text format
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... Database Table: movies title year_made alam ara
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
Creating a MySQL Database Table to store Java Types
and java file. After the connection has been established creates a database table... Creating a MySQL Database Table to store Java Types... to store a java types in our database table. Now one question may arise in your
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
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
Inserting Records using the Prepared Statement
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... and deleting the records from the database table. Here is the code of program
Java save file to database
Java save file to database In this section, you will learn how to insert file... between MySql database and java file with the help of various types of APIs... code, you can insert any file into the database

Ads