How can I remove punctuaton and letters from CSV?

Question

I'm making a toto-appilcation where I have to read data from the CSV file. Unfortunately, my program does not work, the following error occurs:

Exception in thread "main" java.lang.NumberFormatException: For input string: ""2015"
at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:67)
at java.base/java.lang.Integer.parseInt(Integer.java:654)
at java.base/java.lang.Integer.parseInt(Integer.java:786)
at com.epam.training.toto.data.FileBasedDataStore.init(FileBasedDataStore.java:31)
at com.epam.training.toto.App.main(App.java:20)

The CSV file consist of semicolon separated data, a leading punctuation mark, and a trailing one; just like this:

"2015;1;2;24;240 EUR" (I have more data,I just wanted to show the example)

Basically I'm trying to get rid of the punctuation marks and the type of valute, so it won't make compiler errors when I'm trying to parse the money filed into an int.

I tried several methods I found on the internet, however it either throws error due to the " or it does not remove the EUR or it replaces all with " ", which also throws an error.

The part of my code which should handle the parsing looks like this currently:

try (BufferedReader reader = new BufferedReader(new FileReader(init))) {
        String line;


        while ((line = reader.readLine()) != null) {
            String[] fields = line.replaceAll("\\p{Punct}" + "EUR", " ").split(";");

            int year = Integer.parseInt(fields[0]);
            int week = Integer.parseInt(fields[1]);
               
                      etc...

Dont throw stones at me please, I'm fairly a beginner.

Do not attempt to parse CSV yourself, use an existing library (there are several). CSV has some subtleties that are hard to get right unless you have some experience, so use library code that has already been thoroughly tested. — Jim Garrison, Feb 28 '22 at 20:43
This is not a CSV (comma-separated variables) file. It is a semicolon-separated file. IN your code, apart from anything else, removing the punctuation removes the semicolons, so you can't subsequently split on them. — user207421, Mar 01 '22 at 02:28

score 1 · Answer 1 · answered Feb 28 '22 at 21:01

There might be other ways but this is what i think could be easier for you.

String[] fields = line.split(";");
// removing 1st char from 1st word
fields[0] = fields[0].substring(1)

// removing currency from last field
// split by space and get the first word
fields[fields.length-1] = fields[fields.length-1].split(" ")[0];
//

more ways to split by space: How to split a String by space

score 0 · Answer 2 · answered Feb 28 '22 at 21:48

0

The problem are the double quotes around all. Do first:

line = line.replaceAll("^\\s*\"([^\"]*)\"\\s*$", "$1")

answered Feb 28 '22 at 21:48

Joop Eggen

107,315
7
83
138

score 0 · Answer 3 · answered Mar 01 '22 at 02:07

To remove the straight quote marks, use escape syntax like this:

"\""

To represent week within a year, add ThreeTen-Extra to your app for its YearWeek class.

Define a class to hold your parsed data. If the main purpose of the class is to communicate data transparently and immutably, define the class as a record.

Here is some untested example code.

record Quote ( YearWeek yearWeek , int third , int fourth , int euros ) {}
List< Quote > quotes = 
    Files
    .lines( … )
    .map( string -> string.replace( "\"" , "" ).replace( " EUR" , "" ) )
    .map( string -> string -> string.split( ";" ) 
    .map( partsArray -> new Quote( 
        YearWeek.of( Integer.parseInt( partsArray[0] ) , Integer.parseInt( partsArray[1] ) ) ,
        Integer.parseInt( partsArray[2] ) ,
        Integer.parseInt( partsArray[3] ) ,
        Integer.parseInt( partsArray[4] ) ,
    )
    .toList()
;

How can I remove punctuaton and letters from CSV?

3 Answers3