How to modify default delimiter while loading values to csv file

Question

My requirement is to write the values from DB into .csv file and email the same. with the below code fetched the values from DB and by createFile(testFile) method created the file and stored in server location then emailed the same.

//Sample input data:
SRT678,Textile STS Secure Limited, OMD,First,Last

Issue is SRT678 goes to column1 , Textile STS Secure Limited - column2 , OMD - column3 but i need "Textile STS Secure Limited, OMD" to column2. I didn't set any delimiter as "," but i believe by default "," is taken as delimiter.

Excel use a system regional setting - List separator to determine which default delimiter to use.is there any way to do changes through code? How can i skip "," here Textile STS Secure Limited, OMD and make it into same column in excel.

      public Object extractData(ResultSet rs) {
      List list1 = new ArrayList();
      TestFileInfo testFile = new TestFileInfo();
      String base_name="UsersInfo";
      String file_name=base_name + ".csv";
      testFile.setFileName(file_name);
        String header_row="empid,empName,firstName,lastName\r\n";
      list1.add(header_row);
        boolean valid = true;
        int total = 0;
        while (rs.next()) {
           if (valid) {
               total ++;
               String row = rs.getString("empid") 
                + "," + rs.getString("empName") 
                + "," + rs.getString("firstName")
                + "," + rs.getString("lastName")  
                + "\r\n" ;
            list1.add(row);
           }
           else {
                break;
               }
            }
          if (valid)
        {
            testFile.setTotalRecords(total);
            testFile.setRowList(list1);
        }
        else  
        {
            testFile = null;
        }
      return testFile;
     }

// createFile(testFile);

      static public void createFile( TestFileInfo testFile){
      List rowList = file.getRowList();
      File testFile = new File(location,file.getFileName()); 
     if (!testFile.exists()) {
          testFile.createNewFile();
    }
      FileWriter fstream = new FileWriter(testFile);
        BufferedWriter out = new BufferedWriter (fstream);
        for (Iterator row = rowList.iterator(); row.hasNext();) {
               out.write((String) row.next());
            }
            out.close();
            file.setCsvFile(testFile);
        return ;
       }

Any of your help is appreciated! Thanks!

Answer 1

Your file is a comma delimited. So whenever it sees a comma, it considers it as a new column entry.

To skip doing this, we add the entry in a double-quotes ("a, b, c").

Here a, b, c are now in a single column.

To achieve this, we use a .contains() method and check if it has a comma in it.

String x = rs.getString("emid");
if(x.contains("\""))
  x = x.replace("\"", "\"\""); 
if(x.contains(",")) 
   x = "\"" + x + "\"";