how to read csv data and insert into database

Question

I trying to read and insert CSV data in jsp but i unable to read, for xls,.xlsx format i can read and insert data i google it and i found below code which is modified

Reader in1=new FileReader(file1);

Iterable<CSVRecord> records=CSVFormat.RFC4180.withFirstRecordAsHeader().parse(in1);
for(CSVRecord record:records)
{
   String policy_no=record.get("POLICY_NO");
   String ENDORSEMENT_NO=record.get("ENDORSEMENT_NO");
   String GP_EBAO=record.get("GP_EBAO");
   String TOL_PRM=record.get("TOL_PRM");
   String PRODUCT_NAME=record.get("PRODUCT_NAME");
   String POLICY_NUMBER=record.get("POLICY_NUMBER");
   String GP_OF=record.get("GP_OF");
   String DIFF=record.get("DIFF");

}

but still it is giving me org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13] exception

here is my code for reading excel can anyone help me how to read and insert csv data please

    <%@page import="org.jfree.data.io.CSV"%>
<%@page import="org.apache.commons.fileupload.FileItemStream"%>
   <%@page import="java.util.List"%>
   <%@page import="org.apache.commons.fileupload.FileItem"%>
   <%@page import="org.omg.PortableServer.Servant"%>
   <%@page import="org.apache.commons.fileupload.FileItemFactory"%>
   <%@page import="java.nio.file.Paths"%>
   <%@page language="java" contentType="text/html; charset=UTF-8"
           pageEncoding="UTF-8"%>
        <%@page import="java.sql.*" %>
       <%@page import ="java.util.Date" %>  
       <%@page import ="java.io.*" %>  
       <%@page import ="java.io.FileNotFoundException" %>  
       <%@page import ="java.io.IOException" %>  
       <%@page import ="java.util.Iterator" %>  
       <%@page import ="java.util.ArrayList" %> 
       <%@page import="org.apache.poi.hssf.usermodel.*" %>
       <%@page import ="org.apache.poi.hssf.usermodel.HSSFCell" %>  
       <%@page import ="org.apache.poi.hssf.usermodel.HSSFRow" %>  
       <%@page import ="org.apache.poi.hssf.usermodel.HSSFSheet" %>  
       <%@page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>  
       <%@page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %>
       <%@page import="org.apache.poi.ss.usermodel.Cell" %>
       <%@page import ="org.apache.poi.ss.usermodel.Row"%>
       <%@page import="org.apache.poi.ss.usermodel.Sheet" %>
       <%@page import="org.apache.poi.ss.usermodel.Workbook" %>
       <%@page import="com.oreilly.servlet.MultipartRequest" %>
       <%@page import="org.apache.poi.xssf.usermodel.*"%>
       <%@page import="org.apache.poi.xssf.usermodel.XSSFWorkbook"%>
       <%@page import="org.apache.commons.fileupload.disk.DiskFileItemFactory"%>
   <%@page import="org.apache.commons.fileupload.servlet.ServletFileUpload"%>
   <%@page import="org.apache.commons.csv.CSVFormat" %>
   <%@page import="org.apache.commons.csv.CSVRecord" %>




     <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
        <style>
            .blueText {
   color: blue;
}
        </style>

    </head>
    <body>

        <%!
       public static boolean isRowEmpty(Row row){
         int firstCol = row.getFirstCellNum();
         for(int cnt = 0; cnt<4 ; cnt++){
             Cell cell = row.getCell(firstCol+cnt);
             if(cell!=null && cell.getCellType()!=Cell.CELL_TYPE_BLANK){
                 return false;
             }
         }
         return true;
    }



        %>   
  <%


 ArrayList CellArrayListHolder=new ArrayList();
 Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con=DriverManager.getConnection("jdbc:oracle:thin:@172.18.114.213:1821:ed","xe","se");
      String contentType = request.getContentType();

if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0))
{
DataInputStream in = new DataInputStream(request.getInputStream());
int formDataLength = request.getContentLength();
byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;
while (totalBytesRead < formDataLength) {
byteRead = in.read(dataBytes, totalBytesRead,formDataLength);
totalBytesRead += byteRead;
}
String file = new String(dataBytes);
String saveFile = file.substring(file.indexOf("filename=\"") + 10);
saveFile = saveFile.substring(0, saveFile.indexOf("\n"));
saveFile = saveFile.substring(saveFile.lastIndexOf("\\") + 1,saveFile.indexOf("\""));
//out.println(saveFile);
int lastIndex = contentType.lastIndexOf("=");
String boundary = contentType.substring(lastIndex + 1,contentType.length());
int pos;
pos = file.indexOf("filename=\"");
pos = file.indexOf("\n", pos) + 1;
pos = file.indexOf("\n", pos) + 1;
pos = file.indexOf("\n", pos) + 1;
int boundaryLocation = file.indexOf(boundary, pos) - 4;
int startPos = ((file.substring(0, pos)).getBytes()).length;
int endPos = ((file.substring(0, boundaryLocation)).getBytes()).length;



FileOutputStream fileOut = new FileOutputStream(saveFile);



//out.println("<script type=\"text/javascript\">");
// out.println("alert('You are successfully Login at:" + saveFile + "')");
// out.println("location='EmployeeLogin.html';");
//   out.println("</script>");

fileOut.write(dataBytes, startPos, (endPos - startPos));
fileOut.flush();
fileOut.close();




%>

<%




File file1 = new File(saveFile);
FileInputStream file_inut=new FileInputStream(file1);



if(saveFile.indexOf(".")>0)
{
 saveFile=saveFile.substring(0,saveFile.lastIndexOf("."));
}
    String query="Select File_name,to_char(FILE_UPLOAD_DATE,'DD-MON-YYYY') from DYNAMIC_INSERT where File_name='"+saveFile+"'";
    Statement st1=con.createStatement();
    ResultSet rs=st1.executeQuery(query);
    if(rs.next())
    {
          String file_name=rs.getString(1);
          String FILE_UPLOAD_DATE=rs.getString(2);


                       out.println("<script type=\"text/javascript\">");
 out.println("alert('File name " + file_name + " already exists File upload Date is "+FILE_UPLOAD_DATE+"')");
 out.println("location='xlsUpload.html';");
   out.println("</script>");


    }
  else
    { 


XSSFWorkbook workbook=new XSSFWorkbook(file_inut);
XSSFSheet  firstsheet=workbook.getSheetAt(0);
Iterator<Row> iterator=firstsheet.rowIterator();
int count=0;
int search_id=0; 
XSSFRow nextrow;
Row row;



StringBuffer sb=new StringBuffer();

while(iterator.hasNext())
{
 row =(XSSFRow)iterator.next();

for(int col=0;col<row.getLastCellNum();col++)
 {
     Cell cell=row.getCell(col, Row.CREATE_NULL_AS_BLANK);
   //sb.append(cell.getStringCellValue()+"Null");

 }


    ArrayList rowarraylist=new ArrayList();
    Iterator<Cell> cellIterator=row.cellIterator();

    while(cellIterator.hasNext())
    {

       XSSFCell cell=(XSSFCell)cellIterator.next();

        rowarraylist.add(cell);





    }

    CellArrayListHolder.add(rowarraylist);

}
       //out.println(CellArrayListHolder);
       ArrayList rowarraylist=null;
       //PreparedStatement st=con.prepareStatement("insert into DYNAMIC_INSERT values(?,?,?,?,?,?,?,?,?,?,?)");
       PreparedStatement ps=con.prepareStatement("insert into DYNAMIC_INSERT values(?,?,?,?,?,?,?,?,?,?,?)");

      ps.setString(1, saveFile);
      ps.setInt(2, search_id);
        ps.setDate(3,new java.sql.Date(System.currentTimeMillis()));

for(int i=1;i<CellArrayListHolder.size();i++)
{
    rowarraylist=(ArrayList)CellArrayListHolder.get(i);


     //st.setString(1, file_name);
    ps.setString(4, (rowarraylist.get(0)).toString());
   ps.setString(5, (rowarraylist.get(1)).toString());
   ps.setString(6, (rowarraylist.get(2)).toString());
    ps.setString(7, (rowarraylist.get(3)).toString());
    ps.setString(8, (rowarraylist.get(4)).toString());
    ps.setString(9, (rowarraylist.get(5)).toString());
    ps.setString(10, (rowarraylist.get(6)).toString());
    ps.setString(11, (rowarraylist.get(7)).toString());
    //ps.setString(10, (rowarraylist.get(6)).toString());
    /*st.setString(6, rowarraylist.get(2).toString());
   st.setString(7, rowarraylist.get(3).toString());
   st.setString(8, rowarraylist.get(4).toString());
    st.setString(9, rowarraylist.get(5).toString());
   st.setString(10, rowarraylist.get(6).toString());
  st.setString(11, rowarraylist.get(7).toString());*/
    //st.executeUpdate();
    count=ps.executeUpdate();
}



if(count>0)
{
       out.println("<script type=\"text/javascript\">");
   out.println("alert('File uploaded successfully');");
      out.println("location='xlsUpload.html';");
   out.println("</script>");    
}
    }
}

  %>
    </body>
</html>

Possible duplicate of "Package should contain a content type part [M1.13]" — Ori Marko
– Ori Marko, Commented Jun 11, 2018 at 11:06
@user7294900: i updated my question is not duplicate question sir please fully — Atul
– Atul, Commented Jun 11, 2018 at 11:14
If you have the access to DB directly, i suggest to use external table to "read" the csv data and insert it. — Ychdziu
– Ychdziu, Commented Jun 11, 2018 at 12:20
I agree with @ychdziu. This seems like an awful lot of code to write (and maintain!) when Oracle database already provides utilities to do this: external tables from inside the database or SQL*Loader from the client side. — APC
– APC, Commented Jun 11, 2018 at 14:23

Atul · Accepted Answer · 2018-06-12 08:54:39Z

I got solution for this i'm using opencsv-2.2 jar Here is my working code where detail properly insert in table

<%@page import="org.jfree.data.io.CSV"%>
<%@page import="org.apache.commons.fileupload.FileItemStream"%>
   <%@page import="java.util.List"%>
   <%@page import="org.apache.commons.fileupload.FileItem"%>
   <%@page import="org.omg.PortableServer.Servant"%>
   <%@page import="org.apache.commons.fileupload.FileItemFactory"%>
   <%@page import="java.nio.file.Paths"%>
   <%@page language="java" contentType="text/html; charset=UTF-8"
           pageEncoding="UTF-8"%>
        <%@page import="java.sql.*" %>
       <%@page import ="java.util.Date" %>  
       <%@page import ="java.io.*" %>  
       <%@page import ="java.io.FileNotFoundException" %>  
       <%@page import ="java.io.IOException" %>  
       <%@page import ="java.util.Iterator" %>  
       <%@page import ="java.util.ArrayList" %> 
       <%@page import="org.apache.poi.hssf.usermodel.*" %>
       <%@page import ="org.apache.poi.hssf.usermodel.HSSFCell" %>  
       <%@page import ="org.apache.poi.hssf.usermodel.HSSFRow" %>  
       <%@page import ="org.apache.poi.hssf.usermodel.HSSFSheet" %>  
       <%@page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>  
       <%@page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %>
       <%@page import="org.apache.poi.ss.usermodel.Cell" %>
       <%@page import ="org.apache.poi.ss.usermodel.Row"%>
       <%@page import="org.apache.poi.ss.usermodel.Sheet" %>
       <%@page import="org.apache.poi.ss.usermodel.Workbook" %>
       <%@page import="com.oreilly.servlet.MultipartRequest" %>
       <%@page import="org.apache.poi.xssf.usermodel.*"%>
       <%@page import="org.apache.poi.xssf.usermodel.XSSFWorkbook"%>
       <%@page import="org.apache.commons.fileupload.disk.DiskFileItemFactory"%>
   <%@page import="org.apache.commons.fileupload.servlet.ServletFileUpload"%>
   <%@page import="au.com.bytecode.opencsv.CSVReader"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
         <%


//  try
//  {
   //try
    //{
 ArrayList CellArrayListHolder=new ArrayList();
 Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con=DriverManager.getConnection("jdbc:oracle:thin:@172.18.119.213:1821:godb","xe","se");
      String contentType = request.getContentType();

if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0))
{
DataInputStream in = new DataInputStream(request.getInputStream());
int formDataLength = request.getContentLength();
byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;
while (totalBytesRead < formDataLength) {
byteRead = in.read(dataBytes, totalBytesRead,formDataLength);
totalBytesRead += byteRead;
}
String file = new String(dataBytes);
String saveFile = file.substring(file.indexOf("filename=\"") + 10);
saveFile = saveFile.substring(0, saveFile.indexOf("\n"));
saveFile = saveFile.substring(saveFile.lastIndexOf("\\") + 1,saveFile.indexOf("\""));

//out.println(saveFile);
int lastIndex = contentType.lastIndexOf("=");
String boundary = contentType.substring(lastIndex + 1,contentType.length());
int pos;
pos = file.indexOf("filename=\"");
pos = file.indexOf("\n", pos) + 1;
pos = file.indexOf("\n", pos) + 1;
pos = file.indexOf("\n", pos) + 1;
int boundaryLocation = file.indexOf(boundary, pos) - 4;
int startPos = ((file.substring(0, pos)).getBytes()).length;
int endPos = ((file.substring(0, boundaryLocation)).getBytes()).length;



FileOutputStream fileOut = new FileOutputStream(saveFile);




fileOut.write(dataBytes, startPos, (endPos - startPos));
fileOut.flush();
fileOut.close();




%>
<%
    File file1 = new File(saveFile);
//FileInputStream file_inut=new FileInputStream(file1);

CSVReader reader = new CSVReader(new FileReader(file1), ',');







if(saveFile.indexOf(".")>0)
{
 saveFile=saveFile.substring(0,saveFile.lastIndexOf("."));
}
    String query="Select File_name,to_char(FILE_UPLOAD_DATE,'DD-MON-YYYY') from DYNAMIC_INSERT where File_name='"+saveFile+"'";
    Statement st1=con.createStatement();
    ResultSet rs=st1.executeQuery(query);
    if(rs.next())
    {
          String file_name=rs.getString(1);
          String FILE_UPLOAD_DATE=rs.getString(2);


                       out.println("<script type=\"text/javascript\">");
 out.println("alert('File name " + file_name + " already exists File upload Date is "+FILE_UPLOAD_DATE+"')");
 out.println("location='xlsUpload.html';");
   out.println("</script>");


    }
    else
    {
        int search_id=0; 
       String[] nextline;
       int i=0;
        PreparedStatement ps=con.prepareStatement("insert into DYNAMIC_INSERT values(?,?,?,?,?,?,?,?,?,?,?)");

         ps.setString(1, saveFile);
      ps.setInt(2, search_id);
        ps.setDate(3,new java.sql.Date(System.currentTimeMillis()));
        while((nextline=reader.readNext())!=null)
        {

                    ps.setString(4, nextline[0]);
                    ps.setString(5, nextline[1]);
                    ps.setString(6, nextline[2]);
                    ps.setString(7, nextline[3]);
                    ps.setString(8, nextline[4]);
                    ps.setString(9, nextline[5]);
                    ps.setString(10, nextline[6]);
                    ps.setString(11, nextline[7]);

                    i=ps.executeUpdate();


        }
        if(i>0)
        {
             out.println("<script type=\"text/javascript\">");
   out.println("alert('File uploaded successfully');");
   out.println("</script>");    
        }

    }
}

    %>
    </body>
</html>

Collectives™ on Stack Overflow

how to read csv data and insert into database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related