converting csv to xls in java taking care of a column

Question

I am working as java developer and i want to convert an .csv file to .xls 2003 format so that my csv will be converted into .xls file the structure of my .csv file would be like

REC_STATUS,TRADE_ID,SETTLEMENT_DATE,TRADE_EFFECTIVE_DATE,PAYMENT_TYPE,VERSION,BREAK_DOWN_BUCKET,CAUSE,NUM_CASHFLOWS_AFFECTED,PROFILE
Found only in File :B,178942690,01-Feb-16,03-Dec-14,"Coupon",5,NOISY_BREAK_BUCKET,REC_TOOL_ISSUE_PAYMENT_DIRECTION_MISMATCH | REC_TOOL_ISSUE_NOTIONAL_MISMATCH | TRADE_VERSION,1,AVS Offshore
Found only in File :B,197728700,Various,21-Dec-15,"Coupon,(x20)",2,ACTUAL DATA BREAK BUCKET,ACTUAL_DATA_BREAK,20,AVS Offshore

Now as you can see that .csv file is comma separated but for value under payment type the value is like "Coupon,(x20)" now for this the value should be treated as single so the logic should be like that for the fifth column since indexing would be stating from 0 onwards so the position of column is fixed inside the .csv file so for the value of the fifth column payment type if the value is "Coupon,(x20)" then it should be treated as single do not split this fifth column value so for the fifth column value the logic should something starts from double quotes and end with double quotes , so please advise how will i convert the .csv to .xls taking care of the fifth column value also

what I have tried is , shown below please advise how can i correct this code and overcome from the problem

public class CSVToExcelConverter {

public static void main(String args[]) throws IOException
{
ArrayList arList=null;
ArrayList al=null;
String fName = "test.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++)
{
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("test.xls");
hwb.write(fileOut);
fileOut.close();
System.out.println("Your excel file has been generated");
} catch ( Exception ex ) {
ex.printStackTrace();
} //main method ends
}
}

Use a counter for the column. When you encounter the 5th column, simply read two columns and jump to the 7th column — Nikitha, Feb 09 '16 at 16:48
@NikithaReddy Thanks a lot can you also please show a bit it would be great if you can please post the code i would debug it in order to grasp thanks in advance — sss, Feb 09 '16 at 16:49
@SheetalMohanSharma I have edited and added the code please check — sss, Feb 09 '16 at 16:52
You might want to implement or use a proper [CSV parser](https://www.google.com/search?q=java+csv+parser) that doesn't search for the separator character inside quoted strings. — Kenney, Feb 09 '16 at 16:53
@Kenney I still not able to grasp please can you please advise how can i overcome from this while reading csv in my above example — sss, Feb 09 '16 at 16:57
@Kenney Thanks can you please advise what logic should i correct in my above piece of code to overcome from this problem — sss, Feb 09 '16 at 17:03
Did you click my last link and read it? It explains it there. — Kenney, Feb 09 '16 at 17:06
@Kenney Thanks but please advise how can i connect that with my above piece of code please that will be really helpful Thanks inadvance — sss, Feb 09 '16 at 17:08

score 0 · Answer 1 · edited Feb 09 '16 at 19:03

0

    public class CSVToExcelConverter {

    public static void main(String args[]) throws IOException
    {
         ArrayList arList=null;
         ArrayList al=null;
         String fName = "test.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 == 5){
                       a1.add(strar[j] + "," + strar[j+1]);
                       j++;
                  }
                  al.add(strar[j]);
              }
              arList.add(al);
              i++;
         }
    }
}

edited Feb 09 '16 at 19:03

v.ladynev

19,275
8
46
67

answered Feb 09 '16 at 16:55

Nikitha

373
2
18

Thanks but please advise me for my above solution as i need to implement the logic in my above solution please – sss Feb 09 '16 at 16:58
can you please explain what is happening in above piece of code and also please advise where is the output file location is mention – sss Feb 09 '16 at 17:18
When the 5th column is encountered both 5th and 6th columns are considered and I added a "," in between. Btw I just copied part of your code. – Nikitha Feb 09 '16 at 17:20
Thanks a lot let me try it please – sss Feb 09 '16 at 17:22
You don't close streams, don't specify encoding (like me :) ), and use deprecated [DataInputStream.readLine()](https://docs.oracle.com/javase/7/docs/api/java/io/DataInputStream.html#readLine()) — it is starnge to use it for reading lines. – v.ladynev Feb 09 '16 at 17:31
@NikithaReddy Ok :) – v.ladynev Feb 09 '16 at 17:34
@NikithaReddy Thanks but request you to please post the fully incorporated code that will be really grateful Thanks in advance – sss Feb 09 '16 at 17:41
what other code do you need? Did this solve your problem? – Nikitha Feb 09 '16 at 17:48
@NikithaReddy no please it did not solve my problem still – sss Feb 10 '16 at 16:31

v.ladynev · Answer 2 · 2016-02-09T17:11:58.233

0

You can use opencsv

 CSVReader reader = new CSVReader(
                new FileReader("c:/xxx.csv"), ',', '"', 1);
 // Read all rows at once
 List<String[]> allRows = reader.readAll();

This code ignores a separator - , in the quotes - " and skip the first line (REC_STATUS,TRADE_ID,SETTLEMENT_DATE ...).

If you don't need to skip the first line just use defaults (a separator - , quotes - ")

CSVReader reader = new CSVReader(
                    new FileReader("c:/xxx.csv"));
// Read all rows at once
List<String[]> allRows = reader.readAll();

edited Feb 09 '16 at 17:11

answered Feb 09 '16 at 17:05

v.ladynev

19,275
8
46
67

Please advise what is meant it will skip the first line with context to .csv file i have given – sss Feb 09 '16 at 17:07
Probably a header line would be skipped – Nikitha Feb 09 '16 at 17:09
@v.ladynev Thanks a lot but can you please advise also then how i will generate the .xls workbook as I am using poi 3.10 version and now I have all the data in the allRows array i want to simply put this in .xls excel file in similar fashion – sss Feb 09 '16 at 17:11
@v.ladynev so i also please advise i want to read all rows and also the double quotes value as single so the final statement would be CSVReader reader = new CSVReader( new FileReader("c:/xxx.csv"), ',', '"'); – sss Feb 09 '16 at 17:15
@sss Yes `CSVReader reader = new CSVReader( new FileReader("c:/xxx.csv"), ',', '"');`, but it is the same as `CSVReader reader = new CSVReader( new FileReader("c:/xxx.csv"));` because of this is default behavior of opencsv. – v.ladynev Feb 09 '16 at 17:18
@SSS so it will include my , inside the " it would treat as a single entity – sss Feb 09 '16 at 17:20
@sss Yes. Sorry, I don't have a code snippet for xls. Just ask other question about creating an xls from `List`, but you will need provide code of your try. – v.ladynev Feb 09 '16 at 17:26

converting csv to xls in java taking care of a column

2 Answers2