Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to remove double quotes while reading CSV

Tags:

java

opencsv

public class CSVTeast {
  public static void main(String[] args) {

      CSVTeast obj = new CSVTeast();
        obj.run();

      }

      public void run() {

        String csvFile = "D:\\text.csv";
        BufferedReader br = null;
        String line = "";
        String cvsSplitBy = "~";

        try {

            br = new BufferedReader(new FileReader(csvFile));
            while ((line = br.readLine()) != null) {

                    // use comma as separator

                String[] csvRead = line.split(cvsSplitBy);




                System.out.println("Value [date= " + csvRead[5] 
                                     + " , name=" + csvRead[9]+"]");

            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (br != null) {
                try {
                    br.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        System.out.println("Done");
      }


}

Output is

Value [date= "POLICY_CHANGE_EFFECTIVE_DATE" , name="AGENCY_NAME"]

Value [date= "2014-04-01" , name="USI INSURANCE SERVICES]--this value stated with double qoutes but not end with same . 

Expected output

Value [date= POLICY_CHANGE_EFFECTIVE_DATE , name=AGENCY_NAME]

Value [date= 2014-04-01 , name=USI INSURANCE SERVICES] 
like image 432
Anandv Avatar asked Jun 06 '14 09:06

Anandv


People also ask

How do I remove double quotes from CSV?

If you want to remove all double quotes, simply select the entire worksheet, then go to the Editing group of the Home tab and click the Find and select drop down. Click on Replace (or hit CTRL + h). In the “Find what” field of the Dialog box type in a double quote.

Why does my CSV have double quotes?

Since CSV files use the comma character "," to separate columns, values that contain commas must be handled as a special case. These fields are wrapped within double quotation marks. The first double quote signifies the beginning of the column data, and the last double quote marks the end.

How do you get rid of double quotes in text?

Option 1: Remove any double quotes in a text string with replace('my string','"',''). This will substitute any instance of a double quote anywhere in the string with an empty string.

How do you read csv file with double quotes excel?

When reading in a quoted string in a csv file, Excel will interpret all pairs of double-quotes ("") with single double-quotes("). So replace all double-quotes in your strings with pairs of double quotes. Excel will reverse this process when exporting as CSV.


4 Answers

You can try passing the value through the String.replace() method.

So your code would be:

public class CSVTeast {
 public static void main(String[] args) {

  CSVTeast obj = new CSVTeast();
     obj.run();
  }
  public void run() {
    String csvFile = "D:\\text.csv";
    BufferedReader br = null;
    String line = "";
    String cvsSplitBy = "~";
    try {
        br = new BufferedReader(new FileReader(csvFile));
        while ((line = br.readLine()) != null) {
            String[] csvRead = line.split(cvsSplitBy);
            System.out.println("Value [date= " + csvRead[5].replace("\"","") 
                                 + " , name=" + csvRead[9].replace("\"","")+"]");
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (br != null) {
            try {
                br.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    System.out.println("Done");
  }
}
like image 117
Gabriel Ruiu Avatar answered Oct 09 '22 08:10

Gabriel Ruiu


There's a nice CSV Reader for Java that will handle the mess of this for you, http://opencsv.sourceforge.net/

It has a maven package if your project is maven, else you can download the JARs there.

like image 24
Matthew Trout Avatar answered Oct 09 '22 07:10

Matthew Trout


If the qoutemarks are at the beginning of every CSV line, you can do:

csvRead[5].substring(1, csvRead[5].length()-1)

That will remove the first and last character of that particular string. You then need to store the results somewhere or print it out.

like image 44
Husman Avatar answered Oct 09 '22 08:10

Husman


It is also important to check if the String starts with a double quote, otherwise the code will start deleting the first character of the CSV value. I do this in my code in one of my apps, where my CSV value is coming in rowData[1] which sometimes have double quotes and sometimes it doesn't, depending upon the number of words in the value String.

String item = (String.valueOf(rowData[1].charAt(0)).equals("\"") ? rowData[1].substring(1, rowData[1].length() - 1) : rowData[1]);
like image 37
zeeshan Avatar answered Oct 09 '22 09:10

zeeshan