Read Comma separated file containing commas, single/ double quotes, line feed characters.


Read Comma separated file containing commas, single/ double quotes, line feed characters.

While working in iSupplier implementation, we need to upload comma separated file containing ASN data provided by the client into database. While using comma separated file, user had restrictions not to put comma or any special characters in the file. But what if user needs to upload his address information into database. Address may contain commas and/or special characters. Below is the code that reads CSV file containing commas, special characters/quotes and inserts filtered data into database.

 /*************************************************************************************************************
  *
  *     Reading Data from comma separated file, this code will filter the data from comma separated
  *     file and then insert that data into table.
  *    
  *
  * **********************************************************************************************************/

  public String readCSVFile(String filePath, String TableName, String ColumnList,String skipperValue,String UserId)
  {
        String LS = System.getProperty("line.separator");
        String fileContent = null;
        String ErrorCount = null;
        int skipper = Integer.parseInt(skipperValue);
        String Contentwoquotes = "";
        int beginIndex = 0,endIndex = 0,i = 0;
        String splitString = "";
        String BufferData = "";     
        int charFound;
        int countNull = 1;
        String quoteString = "";
        int trackQuotes = 0;
        String q2string = "";
       
        try
        {
               FileReader fr = new FileReader(filePath);
               BufferedReader reader = new BufferedReader(fr);
               String line;
               int rowNumber = 0;
              
               
               while( skipper != 0 )
               {
                    reader.readLine();
                    skipper--;
               }
              
               while ((line = reader.readLine()) != null)
               {
                   fileContent = line;
                   BufferData = "";
                   q2string = "";
                   quoteString = "";
                   Contentwoquotes = "";
                   countNull = 0;
                   splitString = "";
                   int quoteTracker = 0;
                  
                   while(line.indexOf(",") > -1 )
                   {
                      endIndex = line.indexOf(",");
                      
                      if (line.charAt(0) == '"')
                      {
                           i = 0;
                           quoteTracker = 0;
                           splitString = "";
                      
                           while ( i < line.length() )
                           {  
                                  if (line.charAt(i) == '"' )
                                  {
                                       if (trackQuotes == 1 )
                                       {
                                           q2string = q2string + line.charAt(i); 
//                                           System.out.println("Quote String  At  ::"+q2string);
//                                           System.out.println("Counter :"+trackQuotes);
                                           i++;
                                           trackQuotes = 0;
                                       }
                                       else
                                       {                                             
                                           trackQuotes++;
                                           quoteTracker = 0;
//                                           System.out.println("Track Quotes ::"+trackQuotes);
                                           i++;
                                       }
                                           
                                  }
                                  else if ("'".equals(line.substring(i,i+1)))
                                  {
                                      q2string = q2string + line.charAt(i) + "'" ;
//                                      System.out.println("Quote String  At  ::"+q2string);
//                                      System.out.println("Counter :"+trackQuotes);
                                      i++;
                                      trackQuotes = 0;
                                     
                                  }
                                  else
                                  {
                                          if (line.charAt(i) == ',' && trackQuotes == 1)
                                          {
                                              charFound = checkASCII(q2string);
                                              quoteTracker = 1;
                                              if (charFound == 1)
                                              {
                                                  BufferData = BufferData + "'" +q2string + "',";       
                                                  Contentwoquotes = Contentwoquotes + q2string + "        ";
                                              }
                                              else
                                              {
                                                  BufferData = BufferData + q2string; 
                                                  Contentwoquotes = Contentwoquotes + q2string + "        ";
                                              }
//                                              q2string = "";
//                                              System.out.println("Line Data ::"+line);   
//                                              System.out.println("Value of I ::"+i);
//                                              System.out.println("Counter :"+trackQuotes);
//                                              System.out.println("Line Length ::"+line.length());
//                                              line = line.substring(i+1);
//                                              System.out.println("Remaining line ::"+line);   
                                             
//                                                if ("".equalsIgnoreCase(line))
//                                                    BufferData = BufferData + "'',";

                                              i = 0 ;
                                              break;
                                          }
                                          else
                                          {
                                              if (line.charAt(i) == ',')
                                              {
                                                  endIndex = line.indexOf(",");
//                                                  System.out.println("End index ::"+endIndex);
                                                  if (endIndex == 0 && quoteTracker != 0)
                                                  {
                                                      q2string =  q2string + "'',";
                                                  }
                                                  else
                                                      q2string =  q2string + line.charAt(i);
                                                      
                                              }
                                              else
                                              {
                                                  q2string = q2string + line.charAt(i);
//                                                  System.out.println("Quote String  At  ::"+q2string);
//                                                  System.out.println("Counter :"+trackQuotes);
//                                                  System.out.println("Value of I ::"+i);
//                                                  System.out.println("Line length ::"+line.length());
//                                                    trackQuotes++;
                                              }   
                                              i++;
                                          }   
                                          trackQuotes = 0;
                                         
                                          /*
                                          if (line.charAt(i) == ',' && trackQuotes == 1)
                                          {
                                              splitString = q2string;
                                              q2string = "";
                                          }  
                                          else
                                          {
                                             q2string = q2string + line.charAt(i);
                                             System.out.println("Quote String  At  ::"+q2string);
                                             System.out.println("Counter :"+trackQuotes);
                                          }*/
                                     
                                  }

                                 
                                  if(i == line.length() && trackQuotes == 0 && i != 0  && quoteTracker == 0)
                                  {
                                     i = 0;
                                     line = reader.readLine();
//                                     System.out.println("Line data......"+line);
                                     if (line == null )
                                        break;
                                  }   
                                 
                           }
                          
                           splitString = splitString + q2string;
//                           System.out.println("splitString ::"+splitString);
//                             Contentwoquotes = Contentwoquotes + splitString + "        ";
                           endIndex = line.indexOf(",");
//                           System.out.println("Buffer Data ::"+BufferData);
//                           System.out.println("End Index   ::"+endIndex);
                          
                           if (endIndex != -1 && endIndex != 0 )
                           {
                               if (line != null)
                               {
                                  line = line.substring(i+2);
                                  endIndex = line.indexOf(",");
                               }  
                           }
                           else
                           {
                               line = line.substring(i);    
                           }
//                             System.out.println("Remaining Line Data "+line);
                      }
                      else
                      {
                           endIndex = line.indexOf(",");
                           splitString = line.substring(beginIndex,endIndex);                             
                           i = 0;
                           quoteString  = "";
                           trackQuotes = 0;
//                             System.out.println("Split String catched :: "+splitString);
                          
                           if ((splitString.indexOf("'")!= -1 ))
                           {
                                  while ( i < splitString.length() )
                                  {  
                                         if ("'".equals(splitString.substring(i,i+1)))
                                         {
                                             quoteString = quoteString + splitString.charAt(i) + "'" ;
//                                               System.out.println("Quote String  At  ::"+quoteString);
                                             i++;
                                             //trackQuotes = 0;
                                            
                                         }
                                         else
                                         {
                                             quoteString = quoteString + splitString.charAt(i);
//                                               System.out.println("Quote String  At  ::"+quoteString);
                                             i++;
                                             //trackQuotes = 0;
                                         }
                                  }
                                  splitString = quoteString;

                                  /*BufferData = BufferData + "'" +quoteString + "',"; */
                           }
                           else
                           {
                               if (line != null)
                               {
                                   if (endIndex != 0 )
                                   {
                                        charFound = checkASCII(splitString);
                                        if (charFound == 1)
                                        {
                                            BufferData = BufferData + "'" + splitString + "',";       
                                        }
                                        else
                                        {
                                            BufferData = BufferData + splitString + ","; 
                                        }
                                   }
                                   else
                                   {
                                     // If string has null value
                                       if(endIndex != -1)
                                       { 
                                           countNull++;
        //                                 System.out.println("Null Counter ::"+countNull);
                                           BufferData = BufferData + "'',";
                                       }  
                                   }
                               }
                               else
                               {
                                   charFound = checkASCII(splitString);
                                   if (charFound == 1)
                                   {
                                       BufferData = BufferData + "'" + splitString + "'";       
                                   }
                                   else
                                   {
                                       BufferData = BufferData + splitString; 
                                   }
                               }   
                           }
                          
                           Contentwoquotes = Contentwoquotes + splitString + "        ";
                          
                           if(line.length() > endIndex)
                              line = line.substring(endIndex+1);
                       
                      }

                   }
               
//                   System.out.println("Content w/o quotes ::"+Contentwoquotes);
//                   System.out.println("Line with quotes  ::"+BufferData);
//                   System.out.println("Line Data ::"+line);
//                   System.out.println("Begin Index ::"+beginIndex);
//                   System.out.println("End Index   ::"+endIndex);
                
                    if (line == null )
                       break;
               
                    /** Locating last field in the line */
                   
                     if (line != null )
                     {
                             if ("".equalsIgnoreCase(line))
                                  countNull++;
                             int isQuoteFinished = 0;
                             int charFlag = 0;
                             int flag = 0;
                             /** Checking at end of the Line */
//                             System.out.println("Line length ::"+fileContent.length());
//                             System.out.println("Null Counter ::"+countNull);
                             int contentlength = fileContent.length() + 1;
//                             System.out.println("Now outside the loop........");
                             if (countNull != contentlength)
                             {
                                     if ("".equalsIgnoreCase(line))
                                         BufferData = BufferData + "''";  
                                        
                                     else if (line.charAt(0) == '"')
                                     {
                                         q2string = "";
                                         i = 0;
                //                         System.out.println("Double quotes at 0th index.");
                                         while ( i < line.length() )
                                         {  
                                                if (line.charAt(i) == '"' )
                                                {
                                                     if (trackQuotes == 1 )
                                                     {
                                                         q2string = q2string + line.charAt(i); 
//                                                         System.out.println("Quote String  At  ::"+q2string);
//                                                         System.out.println("Counter :"+trackQuotes);
                                                         i++;
                                                         trackQuotes = 0;
                                                     }
                                                     else
                                                     {
                                                         trackQuotes++;
                                                         isQuoteFinished = 0;
                                                         i++;
                                                     }
                                                     charFlag = 0;    
                                                }
                                                else if ("'".equals(line.substring(i,i+1)))
                                                {
                                                    q2string = q2string + line.charAt(i) + "'" ;
//                                                    System.out.println("Quote String  At  ::"+q2string);
//                                                    System.out.println("Counter :"+trackQuotes);
                                                    i++;
                                                    charFlag = 0;
                                                    trackQuotes = 0;
                                                }
                                                else
                                                {
                                                    if (line.charAt(i) == ',' && trackQuotes == 1)
                                                    {
                                                        charFound = checkASCII(q2string);
                                                        isQuoteFinished = 1;
                                                        if (charFound == 1)
                                                        {
                                                            BufferData = BufferData + "'" +q2string + "',";       
                                                            Contentwoquotes = Contentwoquotes + q2string + "        ";
                                                        }
                                                        else
                                                        {
                                                            BufferData = BufferData + q2string + ","; 
                                                            Contentwoquotes = Contentwoquotes + q2string + "        ";
                                                        }
                                                       
                                                        q2string = "";
//                                                        System.out.println("Line Data ::"+line);   
//                                                        System.out.println("Value of I ::"+i);
//                                                        System.out.println("Counter :"+trackQuotes);
//                                                        System.out.println("Line Length ::"+line.length());
                                                        line = line.substring(i+1);
//                                                        System.out.println("Remaining line ::"+line);   
                                                        if ("".equalsIgnoreCase(line))
                                                            BufferData = BufferData + "''"; 
                                                        i = 0;
                                                    }
                                                    else
                                                    {
                                                        if (line.charAt(i) == ',')
                                                        {
                                                            endIndex = line.indexOf(",");
//                                                            System.out.println("End index ::"+endIndex);
//                                                            System.out.println("Char Flag ::"+charFlag);
                                                            if (endIndex == 0 && isQuoteFinished != 0 && charFlag != 1 )
                                                            {
                                                                flag = 1;
                                                                q2string =  q2string + "'',";
                                                            }
                                                            else {
                                                                q2string =  q2string + line.charAt(i);
//                                                                System.out.println("Char Flag ::"+charFlag);
                                                                charFlag = 0;
                                                            }
                                                               
                                                        }
                                                        else
                                                        {   
                                                            q2string = q2string + line.charAt(i);
                                                            charFlag = 1;
//                                                            System.out.println("Quote String  At  ::"+q2string);
//                                                            System.out.println("Counter :"+trackQuotes);
                                                            trackQuotes++;
                                                        }   
                                                        i++;
                                                           
                                                    }   
                                                    trackQuotes = 0;
                                                }
                                               
//                                               System.out.println("Counter Value revs :"+trackQuotes);
                                               if(i == line.length() && trackQuotes == 0 && i != 0 && isQuoteFinished == 0 )
                                               {
                                                   i = 0;
                                                   line = reader.readLine();
                                                   /* New line Comes */
                                                   if (line == null )
                                                      break;
                                               } 
                                             
//                                               System.out.println("Buffer Data ::"+BufferData);
                                         }  // end of while ( i < line.length() )
                                         
                                        
                                       
                                         if (flag == 1)
                                             line = line.substring(i-1);
                                         
//                                         System.out.println("Remaining Line Data ::"+line);
//                                         System.out.println("String ::"+q2string);
                                        
                                         
                                        
                                         if (",".equalsIgnoreCase(line))
                                         {
                                             BufferData = BufferData + q2string + "''"; 
                                         }   
                                         else
                                         {
                                             charFound = checkASCII(q2string);
                                             if (charFound == 1 )
                                             {
                                                 BufferData = BufferData + "'" + q2string + "'";       
                                             }
                                             else
                                             {
                                                 BufferData = BufferData + q2string; 
                                             }
                                             
                                         }   
                                        Contentwoquotes = Contentwoquotes + q2string + "        ";
                //                  
                                     } // end of if (line.charAt(0) == '"')
                                     else
                                     {
                                         i = 0;
                                         quoteString = "";
                                         if ((line.indexOf("'")!= -1 ))
                                         {
//                                                System.out.println("String having quotes.......");
                                                while ( i < line.length() )
                                                {  
                                                       if ("'".equals(line.substring(i,i+1)))
                                                       {
                                                           quoteString = quoteString + line.charAt(i) + "'" ;
//                                                           System.out.println("Quote String  At  ::"+quoteString);
                                                           i++;
                                                       }
                                                       else
                                                       {
                                                           quoteString = quoteString + line.charAt(i);
//                                                           System.out.println("Quote String  At  ::"+quoteString);
                                                           i++;
                                                       }
                                                }
                                                Contentwoquotes = Contentwoquotes + quoteString + "         ";
                                                BufferData = BufferData + "'" +quoteString + "'";
                                         } // end of if ((line.indexOf("'")!= -1 ))
                                         else
                                         {
                                             charFound = checkASCII(line);
                                             if (charFound == 1){
                                                     BufferData = BufferData + "'"+ line + "'";
                                             }
                                             else{
                                                     BufferData = BufferData + line;
                                             }
                                             Contentwoquotes = Contentwoquotes + line  + "         ";
                                         } // end of nested else
                                     } // end of main else
                                    

                                       
//                                     System.out.println("Line length ::"+line.length());
                //                   System.out.println("line.charAt(beginIndex) ::"+line.charAt(beginIndex));
                //                   System.out.println("line.lastIndexOf('\"')::"+line.lastIndexOf('"'));
//                                     System.out.println("Content w/o quotes ::"+Contentwoquotes);
//                                     System.out.println("Line with quotes  ::"+BufferData);
//                                     System.out.println("Line Data ::"+line);
                //                   System.out.println("Begin Index ::"+beginIndex);
                //                   System.out.println("End Index   ::"+endIndex);
               
                                     insertIntoTable(BufferData,TableName,ColumnList,Contentwoquotes,UserId,rowNumber);  
                                    
                                     if (line == null )
                                        break;
                                   
                             }
                           
                     }
                     else {
                                insertIntoTable(BufferData,TableName,ColumnList,Contentwoquotes,UserId,rowNumber);  
                     }
                    
               } // end of while ((line = reader.readLine()) != null)       
                   

              rowNumber++;
                
        }
        catch (IOException e) {
            e.printStackTrace();
        }

/**********************************************************************
               Below method inserts line into table.
**********************************************************************/
  public void insertIntoTable(String RowData,String TableName, String ColumnList, String Contentwoquotes,String UserId, int rowNumber)
  {
      OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getDBTransaction();
      int beginIndex = 0,endIndex = RowData.indexOf(","),i = 0;
      String splitString = "";
      String Line = RowData;
      String BufferData = "";      
      int charFound;
      String InsertStmt;
      ArrayList Value = new ArrayList();
      CallableStatement oraclecallablestatement;

      try
      {
          oraclecallablestatement = oadbtransactionimpl.createCallableStatement(insertstmt, -1);               
          oraclecallablestatement.execute();
          System.out.println("Insert Command Executed Successfully.");
          oraclecallablestatement.close();
      }
      catch (SQLException e)
      {
          
          String insertintoErrorTabHead = "INSERT INTO XXKOP.STGGING_ERROR_DTLS(ERROR_MSG,COLUMNNAME) " +       " Values('"+e.getMessage().substring(e.getMessage().indexOf(" ")+1)+"','"+Contentwoquotes+"')";

          try
          {
                oraclecallablestatement = oadbtransactionimpl.createCallableStatement(insertintoErrorTabHead, -1);
                oraclecallablestatement.execute();
                oraclecallablestatement.close();
          }
          catch (SQLException exp)
          {
                throw new OAException(e.getMessage() + "    " + insertstmt,OAException.ERROR);
          } 
     
      }
  }
  

Comments

  1. oraclefusion14 March 2017 at 02:41

    Thanks for sharing the useful information and good points were mentioned in this article and for the further information visit our site
    Oracle Fusion Financials Training

    ReplyDelete
  2. Oracle Fusion22 March 2017 at 22:27

    Thank you very much. The post was lengthy, but I loved to read it till the last word. That is a good tip especially to those new to the blog here. Brief but very accurate information…
    Oracle Fusion HCM Technical Coaching

    ReplyDelete
  3. oracleSCM27 March 2017 at 22:45

    Hi,
    This is a very interesting blog.Thanks for sharing such a
    good blog.
    oracle fusion SCM online training

    ReplyDelete

Post a Comment

Popular posts from this blog

Search on Master-detail table in OAF

Search on Master-detail table in OAF Hello friends, Oracle application framework provides me another opportunity to work on and to get more acquainted with. So here I am eager to share another short article on OAF.  Just finished a project on OAF and came across various fact and findings about the framework. You guys may have come across master – detail relationship in OAF and ‘how-to’ behind that as well.  I have recently read articles on how to insert a row into child table. Well this article is quite a different one. While developing an application, there might be a requirement wherein we need to work on master detail relationship.  In my case, the requirement was to develop the search page having result table including master and detail table records.  The user may enter any search criteria that may fall under master table and/or detail table. Depending upon the search performed by the user, the master and detail table records must be populated. For...
Read more

Implement 3-level master detail hierarchy using table region in OA framework.

Image
Implement 3-level master detail hierarchy using table region in OA framework. After depth searching on various oracle forums, I came to know that multi-level hierarchy in advance table is not achievable by easy means. There could be at the most one detail region inside an advance table.  If the business requirement is to display records in master->detail->detail format then advance table won’t be effective. For e.g. If the requirement is to show item transaction details year wise and when user clicks on show-hide region against any record will navigate to the month wise details and when user clicks on show-hide region against any record will navigate to the day wise details such hierarchy is possible, The OA framework provides the table region to achieve the same functionality. Procedure: Create view object for corresponding master – detail – detail regions. Define view link for 1 st level of master-detail hierarchy. Define view link for 2 nd leve...
Read more

Shuttle Control in OA Framework

Image
Shuttle Control in OA Framework While working on various business scenarios, we may come across multiple situations wherein we need to select specific values from the list and require moving to another list which will be considered as final list.  OA framework provides shuttle control to fulfill the same. For e.g.  In Jdeveloper, while attaching view object to the application module we need to select correct view object from view object definition list in left side panel and move it to right side panel/list so as to include view object definition into the  application module. Well, to achieve such operations Oracle application framework provides such option by means of shuttle control.  Shuttle Control allows user to move values from one list region to other. Design:  Select page region and right click -> new region set region style property of this region to shuttle.  After setting region style you will get shuttle region with default leading l...
Read more