converting csv to .xls through java in which delimeter sepration is not done properly

Question

I have the below csv file named Vabc.csv

REC_STATUS,TRADE_ID,   SETTLEMENT_DATE,     TRADE_EFFECTIVE_DATE,  PAYMENT_TYPE,                        VERSION,      BREAK_DOWN_BUCKET,         CAUSE,                     NUM_CASHFLOWS_AFFECTED,   PROFILE
Found  ,   178942690,  01-Feb-16,            03-Dec-14,             "Coupon",                              5,         NOISY_BREAK_BUCKET,        REC_TOOL_I | REC_TOOL_H ,      1,                     TRADE_ Offshore
Found  ,   197743320,  Various,              21-Dec-15,            "Brokerage Estimated,Upfront Fee",       1,        ACTUAL DATA BREAK BUCKET,ACTUAL_DATA_BREAK,               2,                       AVS Offshore

As you can see in the above csv file while converting it into .xls file special care need to be taken for the column PAYMENT_TYPE as its value in between consist a comma but that should not be consider as delimiter and it value starts with double quotes and end with double quotes so that should be treated as single value.

now the poistion (index of) column PAYMENT_TYPE is also fixed in the csv file that it will be always be at fifth position as you can see n the above csv file ,now i have to convert the above .csv file into .xls 2003 format through java , i am using jdk 1.5

now the column in the .xls should be like this as shown below but the issue is that payment value should be considered as single starting from double quotes and ending with double quotes and for rest the delimiter is comma separated

REC_STATUS,TRADE_ID,   SETTLEMENT_DATE,     TRADE_EFFECTIVE_DATE,  PAYMENT_TYPE,                        VERSION,      BREAK_DOWN_BUCKET,         CAUSE,                     NUM_CASHFLOWS_AFFECTED,   PROFILE
Found  ,   178942690,  01-Feb-16,            03-Dec-14,             "Coupon",                              5,         NOISY_BREAK_BUCKET,        REC_TOOL_I | REC_TOOL_H ,      1,                     TRADE_ Offshore
Found  ,   197743320,  Various,              21-Dec-15,            "Brokerage Estimated,Upfront Fee",      1,        ACTUAL DATA BREAK BUCKET,ACTUAL_DATA_BREAK,                2,                       AVS Offshore

I have designed the following below program which is not generating the .xls in them mentioned above format please advise how can i correct my above program so that it can generate .xls and the columns should be in order as shown in the above .xls sheet , below is my program please advise how to correct it

public class CSVToExcelConverter {

        public static void main(String args[]) throws IOException
        {
            ArrayList arList=null;
            ArrayList al=null;
            String fName = "C:\\Vabc.csv";
            String thisLine;
            int count=0;
            FileInputStream fis = new FileInputStream(fName);
            DataInputStream myInput = new DataInputStream(fis);
            int i=0;
            arList = new ArrayList();
            while ((thisLine = myInput.readLine()) != null)
            {
                al = new ArrayList();
                String strar[] = thisLine.split(",");
                for(int j=0;j<strar.length;j++)
                {
                    if(j == 4){
                        al.add(strar[j] + "," + strar[j+1]);
                        j++;
                    }
                    al.add(strar[j]);
                }
                arList.add(al);
                System.out.println();
                i++;
            }

            try
            {
                HSSFWorkbook hwb = new HSSFWorkbook();
                HSSFSheet sheet = hwb.createSheet("new sheet");
                for(int k=0;k<arList.size();k++)
                {
                    ArrayList ardata = (ArrayList)arList.get(k);
                    HSSFRow row = sheet.createRow((short) 0+k);
                    for(int p=0;p<ardata.size();p++)
                    {
                        HSSFCell cell = row.createCell((short) p);
                        String data = ardata.get(p).toString();
                        if(data.startsWith("=")){
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            data=data.replaceAll("\"", "");
                            data=data.replaceAll("=", "");
                            cell.setCellValue(data);
                        }else if(data.startsWith("\"")){
                            data=data.replaceAll("\"", "");
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(data);
                        }else{
                            data=data.replaceAll("\"", "");
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(data);
                        }
    //*/
    // cell.setCellValue(ardata.get(p).toString());
                    }
                    System.out.println();
                }
                FileOutputStream fileOut = new FileOutputStream("C:\\test.xls");
                hwb.write(fileOut);
                fileOut.close();
                System.out.println("Your excel file has been generated");
            } catch ( Exception ex ) {
                ex.printStackTrace();
            } //main method ends
        }
    }

Folks please advise any early help would be appreciated , any solution for it

Answer 1

I strongly recommend to use an existing libary to write the Excel Document, for example Apache POI. It has a two compoents

• HSSF to handle Excel '97(-2007) file format
• XSSF to handle Excel 2007 OOXML (.xlsx) file format

Both are pure Java implementation, so you do NOT need to have MS-Excel installation.

BTW: instead of writing your own CSV parser, I would recomend to use an existing one, for example:

• Apache Commons CSV, or
• OpenCSV (SourceForge)

Psydocode for OpenCSV:

 CSVReader reader = new CSVReader(new FileReader("yourfile.csv"), ',', '"');
 List<String[]> allLines = reader.readAll();     
 ...
 HSSFWorkbook hwb = new HSSFWorkbook();
 HSSFSheet sheet = hwb.createSheet("new sheet");
 ...
 for(String line: allLines) {
     HSSFRow row = sheet.createRow((short)sheet.getLastRowNum());
     for(int colIndex = 0; colIndex < line.lenght; colIndex ++) {
         HSSFCell cell = row.createCell((short) colIndex);
         cell.setCellType(Cell.CELL_TYPE_STRING);
         cell.setCellValue(line[colIndex]);
     }
 }