title: How to convert a huge .csv file to excel using POI
body: <p>I have Java code that converts CSV to xlsx.  It works fine with a small file size.  Now I  have a CSV file with 2 lakh records (200,000) and on conversion I am getting an out of memory error.</p>
  
  <p>I tried changing the workbook to SXSSFWorkbook and increasing the heap size and Java memory size to -Xms8G -Xmx10G.  Even this did not work.  I tried it on a UNIX box.</p>
  
  <p>On searching, I got some code about using BigGridDemo.  Can anyone help me in customizing that to reading a .csv file and then using its logic to write to xlsx, or any other  solution?</p>
  
  <pre><code>     try  {   FileReader input = new FileReader(strFileToConvert);
       BufferedReader bufIn = new BufferedReader(input);  
       if(strExcel_Typ.equals("-xls"))
       {
         wbAll = new HSSFWorkbook();
       }
       else
       {
          wbAll = new SXSSFWorkbook(100);
          wbAll.setCompressTempFiles(true);
       }
       sheetAll     = wbAll.createSheet();
       rowAll       = null;
       cellAll      = null;
       shoRowNumAll = 0;
  
       // do buffered reading from a file
       while ((line = bufIn.readLine()) != null)
       {
          intCntr++;
          //if there is any data in the line
          if (line.length() &gt; 0)
          {
              System.out.println(shoRowNumAll);
             //create a new row on the spreadsheet
             rowAll = sheetAll.createRow((int)shoRowNumAll);
             shoRowNumAll++;
  
             if (line.indexOf("\"", 0) &gt; 0)
             {
                if (intCntr == 1)
                {
                   //only issue the message the first time quotes are found
                   System.out.println("Double quotes found.  Stripping double quotes from file");
                }
  
                line = line.replaceAll("\"", "");
             }
  
             //if its the first row and no delimiters found, there is a problem
             if (line.indexOf(strDelim, 0) == -1  &amp;&amp; intCntr == 1)
             {
                  System.exit(1);
                     }
  
             processLine(line);
             ((SXSSFSheet) sheetAll).flushRows(100);
          }
       }
      bufIn.close();
      /write the excel file
       try
       {            String file = strOutPutFile;
                  ExcelOutAll  = new FileOutputStream(file);
          wbAll.write(ExcelOutAll);
       }
       catch (IOException e)
       {
         System.err.println(e.toString());
       }
  
       ExcelOutAll.close();
  </code></pre>
  
  <p>Processline Method:</p>
  
  <pre><code>processLine(String line)
  {
  
    //find the first next delimiter starting in position 0
    intNxtComma = line.indexOf(strDelim, 0);
     while (intCurPosInLine &lt; line.trim().replaceAll(",","").length())
    {
       strCellContent = line.substring((intCurPosInLine), intNxtComma);
  
       //create a new cell on the new row
       cellAll = rowAll.createCell(intCellNum);
  
  
       //set the font defaults
       Font font_couriernew_10 = wbAll.createFont();
       font_couriernew_10.setFontHeightInPoints((short)10);
       font_couriernew_10.setFontName("Courier New");
  
      CellStyle cellStyle = wbAll.createCellStyle();
  
       //if its the first row, center the text
       if (shoRowNumAll == 1)
       {
          cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
          font_couriernew_10.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
       }
  
       cellStyle.setFont(font_couriernew_10);
  
  
       // if the col. needs to be numeric, set the cell format to number
       if ((strNumericCols.indexOf(Integer.toString(intCellNum), 0) &gt; -1) &amp;&amp; (intCntr &gt; 1))
       {
          DataFormat datafrmt = wbAll.createDataFormat();
          cellStyle.setDataFormat(datafrmt.getFormat("$#,##0.00"));
  
          }
  
       cellAll.setCellStyle(cellStyle);
  
  
  
       //populate the cell
       if ((strNumericCols.indexOf(Integer.toString(intCellNum), 0) &gt; -1) &amp;&amp; (intCntr &gt; 1))
       {
          //if the col. needs to be numeric populate with a number
           if(strCellContent != null &amp;&amp;  !"".equals(strCellContent.trim())){
          douCellContent = Double.parseDouble(strCellContent.replaceAll(",",""));
          cellAll.setCellValue(douCellContent);
           }
       }
       else
       {
          cellAll.setCellValue(strCellContent.trim());
       }
  
       intCellNum++;
  
  
       intCurPosInLine = intNxtComma + 1;
  
       //if we dont find anymore delimiters, set the variable to the line length
       if (line.indexOf(strDelim, intCurPosInLine) == -1)
       {
          intNxtComma = line.trim().length();
       }
       else
       {
          intNxtComma = line.indexOf(strDelim, intNxtComma + 1);
       }
    }
  }
  </code></pre>