Convertion of CSV to Table

It converts the comma seperated value file to a table in database.

/*
 * This program used to convert data from CSV file to TABLE

 */

import java.sql.*;
import java.io.*;
public class ConvertCSVtoTABLE {
    
    public void covertCsvtotable(String file)
    {
        Connection conn;
        conn=null;
        ResultSet rs;
        Statement stmt;
        String Query="";
        try
        {
            /*****This program connect with MS-ACCESS DATABASE******/
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn=DriverManager.getConnection("jdbc:odbc:DB");
            stmt = conn.createStatement();
             /******************************************************/
      
            
            int noofcolumn=1,b,bprev,i;
            String text1="'";
            String filename=file;
            FileReader fin=new FileReader(filename);
            BufferedReader bf=new BufferedReader(fin);
            b=bf.read();
            while(b!=-1){
                bprev=b;
                if(b==44)
                {
                    text1=text1+"'";
                    noofcolumn=noofcolumn+1;
                }
                if(b==10)
                {
                     text1=text1+"'";
                     Query="insert into temptable(";
                     for(i=1;i<noofcolumn;i++)
                         Query=Query+"col"+i+",";
                     Query=Query+"col"+i;
                     Query=Query+") values("+text1+")";
                     stmt.executeUpdate(Query);
                     text1="'";
                     noofcolumn=1;
               }
               if(b!=34 && b!=10)
                {
                     text1=text1+(char)b;
                }
               b=bf.read();
               if(bprev==44)
                     text1=text1+"'";
             }
            
            fin.close();
            bf.close();
            conn.close();
            System.out.println("Data Transferred from CSV to TempTable Successfully :)");
         }
  
        catch(Exception E)
        {
             System.out.println(E);
        }        
    }
    
  public static void main(String[] args) 
    {
          ConvertCSVtoTABLE C=new ConvertCSVtoTABLE();
          C.covertCsvtotable("C:\Users\Raj\input.csv");
        
    }
}

[ Read More ]

Parsing a csv file containing data to be saved into database

The code helps in parsing data prsent in a comma separated value format in a file to be uploaded in the database. It checks whether the first token of each line in the csv file is empty or not.

public static void parsingData(String uploadFile, FileValueBinder binder) throws FileFormatException {
  int lineNumber = 1;
 String DELIM_STR = "\r\n";
        StringTokenizer lineTokenizer = new StringTokenizer(uploadFile, DELIM_STR);

  line: 
        while (lineTokenizer.hasMoreTokens()) {

            String tokenVal = lineTokenizer.nextToken();
                   
            // tokenize as CSV         
            String nextToken = null;

            String prevToken = "";

            int counter = 0;

            int parseErrorLocation = 0;

            ArrayList errors = new ArrayList();

            ArrayList values = new ArrayList();

            int indexValue=0;

            // returns the index value of the first comma character in the csv file
            indexValue=tokenVal.indexOf(",");

            // check if the comma char is at 0th position 
            if(indexValue==0){
            
             try {
                   throw new FileFormatException("");
                } catch (Exception ex) {
                    throw new FileFormatException("Please enter a valid value at line number: "+lineNumber+ex.getMessage());
                }
            }

           SampleTokenizer commaTokenizer = new SampleTokenizer(tokenVal, DELIM_CHAR);
           
            while (commaTokenizer.hasMoreTokens()) {
                nextToken = commaTokenizer.nextToken();
                values.add(nextToken);
            }

            //pad the values if required.
            if (counter == (binder.getExpectedNumberOfValues() - 1)) {
                values.add(null);
            }

            Iterator valuesIt = values.iterator();
            counter = 0;

            while (valuesIt.hasNext()) {
                String nextValue = (String) valuesIt.next();
                try {
                    binder.addNextToken(nextValue);
                } catch (ParseException e) {
                    errors.add(e);
                    parseErrorLocation = counter;
                    err = true;
                }

                counter++;
            }
           
            
            

            lineNumber++;
        }

        try {
            binder.eof();
        } catch (ParseException e) {
            try {
               //Exception
            } catch (Exception ex) {
                throw new FileFormatException(ex.getMessage());
            }
        }
   }

  }


The SampleTokenizer refers to the following:

 public SampleTokenizer(String str,String delim)
  {
   
    super(str,delim);
    this.string = str;
    this.delim= delim;
    isCountTokensCalled = false;
    tokenList = new ArrayList();

  }
  


 public SampleTokenizer(String str,String delim, String escape)
  {
   
    super(str,delim);
    this.string = str;
    this.delim= delim;
    isCountTokensCalled = false;
    tokenList = new ArrayList();
    this.ESCAPE_STR = escape;
  }

  public int countTokens() {
    isCountTokensCalled = true;
    String value="";

    while (cursorPos < string.length()) {
      if (String.valueOf(string.charAt(cursorPos)).equals(this.ESCAPE_STR) && !isEscape) {
        isEscape = true; 
        escapeStart = currentPos;
      } else if (String.valueOf(string.charAt(cursorPos)).equals(this.ESCAPE_STR) && isEscape) {
        isEscape = false;
        escapeEnd = currentPos;
      } else if (!isEscape && (String.valueOf(string.charAt(cursorPos)).equals(this.delim))) {
        currentPos = cursorPos;
        value = string.substring(previousPos,currentPos);
        if ("".equals(value))
          value = null;
        tokenList.add(count,value);
        count++;
        previousPos = currentPos+this.delim.length();
      } 
      if (cursorPos == string.length()-1 && !(String.valueOf(string.charAt(cursorPos)).equals(this.delim))) {

        value = string.substring(previousPos);
        if (value.equals(""))
          value = null;
        tokenList.add(count,value);
      }
      cursorPos++;      
    } 

    
    if (string.endsWith(",")){
      
      tokenList.add(count,null);
      count++;
    } else count++;
    
    return count;    
  }
  
  public boolean hasMoreTokens()  {

      if (!isCountTokensCalled)
        this.countTokens();
      if (count > tokenPos)
        return true;
      return false;
  }

  public String nextToken() {
    String token = (String)tokenList.get(tokenPos);
    tokenPos++;
    return token;
    
  }

[ Read More ]

Export to CSV from JSP Page

The code snippet helps to export data from a jsp page which has fetched data from SQL Database into CSV format.

private static  void writeCsv(FileWriter writer , String [] array ) throws Exception{
  try{
   int sizeOfArr = array.length;
   int index = 0; 
   while(index < sizeOfArr){
    writer.append(array[index] == null ? "" : array[index]);
    if( index != (sizeOfArr-1)){
     writer.append(",");
    }else{
     writer.append('\n');
    }
    index++;
   }
  }catch(Exception e){
    e.printStackTrace();
    throw e;
  }
 }

[ Read More ]

Parsing CSV file

It describes, parsing a CSV file without using StringTokenizer in java

import java.io.*;
public class Test {

 public static void main(String args[]) throws IOException {
  String thisLine;
  String[] fullText = new String[100]; //number of possible lines in your file
  int counter = 0;
  String fName = "C:/Users/Desktop/345_APPLE.txt";
  FileInputStream fis = new FileInputStream(fName);
  DataInputStream myInput = new DataInputStream(fis);
  while ((thisLine = myInput.readLine()) != null) {
   System.out.println("---------------------------------\nBegin Parsing");

   int sPos = 0;
   int ePos = 1;
   int tDelim = 0;
   String d = "";
   int colCount = 0;
   int collCount = 0;
   boolean fMe = false;
   boolean finalCol = false;
   while ( ePos < thisLine.length()) {
    sPos = thisLine.indexOf(",", ePos);
    tDelim = thisLine.indexOf(",\"", ePos);

    if (tDelim == ePos) {
     fMe = true;
     collCount = ePos + 1;
     while (collCount <= thisLine.indexOf("\",")) {
      collCount += 1;
     }
     colCount += 1;
     sPos = collCount;
    }

    if (colCount == 47) { //Get Max count of columns
     finalCol = true;
     collCount = ePos + 1;
     while (collCount <= thisLine.lastIndexOf("\"")) {
      collCount += 1;
     }
     colCount += 1;
     sPos = collCount;
    }

    if (sPos == -1) {
     d = thisLine.substring(ePos,thisLine.length()); 
     System.out.println(d);
     break;
    }else if(ePos == 1) {
     d = thisLine.substring(ePos-1,sPos);
     System.out.println(d);
    }else{
     if (fMe) {
      d = thisLine.substring(ePos + 1, sPos);
      fMe = false;
     }else if (finalCol) {
      d = thisLine.substring(ePos, sPos);
      finalCol = false;
     }else{
      d = thisLine.substring(ePos, sPos);
     }
     System.out.println(d);
    }

    colCount += 1;
    ePos = sPos + 1;
   }
   counter++;
   fullText[counter] = thisLine;
   System.out.println("\nEnd Parsing\n---------------------------------\n\n");
  }
 }

}

[ Read More ]

CSV File reader using java

Code used for reading the CSV file format which will be one of the standard format of file type. Usingthis code One can be able to read the content of the csv file for data processing for further validation

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.util.StringTokenizer;
public class TestGrid {
 /**
  * @param args
  */
 public static void main(String[] args) {
  // TODO Auto-generated method stub
  String fileName="C:/APPLE.csv";
  try {
   BufferedReader br = new BufferedReader( new FileReader(fileName));
   String strLine = null;
   StringTokenizer st = null;
   int lineNumber = 0, tokenNumber = 0;
   while( (fileName = br.readLine()) != null)
   {
    lineNumber++;
    //break comma separated line using ","
    st = new StringTokenizer(fileName, ",");
    while(st.hasMoreTokens())
    {
     //display csv values
     tokenNumber++;
     System.out.println("Line # " + lineNumber +
       ", Token # " + tokenNumber
       + ", Token : "+ st.nextToken());
    }
    //reset token number
    tokenNumber = 0;
   }
  }
  catch (FileNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
}

[ Read More ]

Upload Multiple CSV File Parallel | Java Example

This application can be used to upload mulitple csv files parallely using edbloader and insert data in table of PostgreSQL.

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;


public class ImplementPlLoader {

 private String ImplementPlLoader1(String csvFile, String badFile,String ctlFile, String parFile, String logFile,String edbLoaderpath,String tableName, int num) throws Exception 
 {
  String[] edbLoaderpathStr = edbLoaderpath.split(",");
  String[] csvFileStr = csvFile.split(",");
  String[] badFileStr = badFile.split(",");
  String[] ctlFileStr = ctlFile.split(",");  
  String[] parFileStr = parFile.split(",");
  String[] logFileStr = logFile.split(",");
  String parFileData;
  String ctlData;
  boolean success = false;
  String[] cmd = new String[2];
  String exitValue = "";
  
  for(int i=0; i<num; i++)
  {
   System.out.println("edbLoaderpathStr["+i+"]-"+edbLoaderpathStr[i]);
   if(!(new File(edbLoaderpathStr[i])).exists())
    (new File(edbLoaderpathStr[i])).mkdirs();
   
   System.out.println("csvFileStr[i].... "+csvFileStr[i]);
   System.out.println("badFileStr[i].... "+badFileStr[i]);
   System.out.println("ctlFileStr[i] "+ctlFileStr[i]);
   System.out.println("parFileStr[i] "+parFileStr[i]);
   System.out.println("logFileStr[i] "+logFileStr[i]);
   
   if (!(new File(edbLoaderpathStr[i]+csvFileStr[i])).exists())
    return "Fail1";
   
   parFileData = "CONTROL="+ctlFileStr[i]+" BAD="+badFileStr[i]+" LOG="+logFileStr[i];
   System.out.println("parFileData: "+parFileData);

   ctlData = "LOAD DATA INFILE '"+edbLoaderpathStr[i]+csvFileStr[i]+"' INTO TABLE "+tableName+" APPEND FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (empid,empname)";
   System.out.println("ctlData :: " + ctlData);
   
   if ((new File(edbLoaderpathStr[i]+ctlFileStr[i])).exists())
    success=(new File(edbLoaderpathStr[i]+ctlFileStr[i])).delete();
   else
    new File(edbLoaderpathStr[i]+ctlFileStr[i]).createNewFile();

   BufferedWriter out = new BufferedWriter(new FileWriter(edbLoaderpathStr[i]+ctlFileStr[i]));
   out.write(ctlData);
   out.close();

   System.out.println("ctl file success :::" +success);

   /** Creating para file */
   if ((new File(edbLoaderpathStr[i]+parFileStr[i])).exists())
    success=(new File(edbLoaderpathStr[i]+parFileStr[i])).delete();
   else
    new File(edbLoaderpathStr[i]+parFileStr[i]).createNewFile();

   BufferedWriter outParFile = new BufferedWriter(new FileWriter(edbLoaderpathStr[i]+parFileStr[i]));
   outParFile.write(parFileData);
   outParFile.close();

   System.out.println("Para file success :::" +success);
   
   cmd[0]="edbldr";
   cmd[1]=" -d SAM -p 5444 -h localhost USERID=enterprisedb/Admin@123 DIRECT=TRUE PARALLEL=TRUE PARFILE="+parFileStr[i];
   System.out.println("cmd[0] "+cmd[0]);

   String[] env=null;
   Process p = null;

   int iReturn=1;
   long start=System.currentTimeMillis();
   long end=System.currentTimeMillis();

   System.out.println("start:"+start);
   
   while(iReturn!=0 && (end-start)/1000<=5)
   {
    Runtime rt = Runtime.getRuntime();
    p = rt.exec(cmd,env,new File(edbLoaderpathStr[i]));
    iReturn = p.waitFor();
    end=System.currentTimeMillis();
    System.out.println("end:"+end);
    System.out.println("iReturn:"+iReturn);
    System.out.println("exitVal:"+p.exitValue());

    if(p.exitValue()==2)
    {
     exitValue= "2";   // row discarded
     break;
    }
   }
   if((new File(badFile)).exists())
    return "-1";

   if(exitValue.equals("2"))  // row discarded
    return "-2";
  }
  
  return "1";
 }
 
 public static void main(String[] args) throws Exception {
  ImplementPlLoader implPlLoader =  new ImplementPlLoader();
  
  String csvFile = "empFileCsv1.csv,empFileCsv2.csv"; // Name of Csv Files
  String badFile = "empFileBad1.bad,empFileBad2.bad"; 
  String ctlFile = "empFileCtl1.ctl,empFileCtl2.ctl";
  String parFile = "empFilePar1.par,empFilePar2.par";
  String logFile = "empFileLog1.log,empFileLog2.log";
  
  String edbLoaderpath = "D://Sam//edbldr1//,D://Sam//edbldr2//";
  String tableName = "EMP_PL";
  String str = implPlLoader.ImplementPlLoader1(csvFile,badFile,ctlFile,parFile,logFile,edbLoaderpath,tableName,2);
  System.out.println("return str "+str);
  
  if(str.equals("Fail1"))
   System.out.println("Data file doesnot exist");
 }

 

}

[ Read More ]