How to take care of comma after reading excel data

Question

In java, I am reading list of values from excel sheet. After reading the file, the output is something like below.

12345678,abcdefg,123,"Summer class, embedded",2012

I need to remove commas from the above output.

I used StringUtils.commaDelimitedListToStringArray() and assigned to a String[]. While using this commaDelimitedListToStringArray() method, "Summer class, embedded" is getting divided into two results.

Is there any way to avoid this?

I want to read as whole string.

Parsing CSV files not as simple as you're hoping. Use a CSV library. http://stackoverflow.com/questions/200609/can-you-recommend-a-java-library-for-reading-and-possibly-writing-csv-files — Ben M., Sep 22 '15 at 18:50

score 0 · Answer 1 · answered Sep 22 '15 at 18:58

You need to use a parsing method that is more sophisticated than a simple split on a character.

At a minimum, it should have two modes, Splitting and Skipping. Then the logic would look like this

Start in Splitting mode.
Read a character.
If in skipping mode, and the character is a quote, then shift to splitting mode.
If in splitting mode, and the character is a comma, then split.
If in splitting mode, and the character is a quote, then shift to skipping mode.
Continue at 2 until all characters are read.

Learning how to parse is a very useful tool, even though there are plenty of pre-built parsers out there. There are always problems that require "just" enough parsing to require you to roll a new tool.

With that in mind, I'd first reach for a CSV file parsing tool. Then, in some cases, regex parsing might be a good choice. Finally, rolling your own parser might be advisable, but if you do, please read up on discrete finite automata.

If you learn DFA, those that don't understand the math behind it will marvel that your parsers work, and a well built DFA is often very fast.

For a csv parsing library http://opencsv.sourceforge.net/ is a long standing good option. — Edwin Buck, Sep 22 '15 at 18:59

score 0 · Answer 2 · answered Sep 22 '15 at 19:02

Here is an example using the commons csv library:

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;

List<String> results = new ArrayList<String>();
try (Reader rdr = new FileReader(pathToFile); CSVParser parser = CSVFormat.DEFAULT.parse(rdr);)
{
    Iterator<CSVRecord> records = parser.iterator();
    while (records.hasNext())
    {
        CSVRecord row = records.next();
        Iterator<String> values = row.iterator();
        while(values.hasNext())
        {
            results.add(values.next());
        }
    }
}
catch(IOException e)
{
    // log the error here
}

score 0 · Answer 3 · edited Dec 10 '15 at 05:20

0

univocity-parsers allows you to handle this without any trouble.

CsvParserSettings settings = new CsvParserSettings();
CsvParser parser = new CsvParser(settings);
List<String[]> allRows = parser.parseAll(new FileReader(new File("/path/to/your.csv")));

Disclosure: I am the author of this library. It's open-source and free (Apache V2.0 license).

edited Dec 10 '15 at 05:20

josliber

43,891
12
98
133

answered Sep 25 '15 at 05:01

Jeronimo Backes

6,141
2
25
29

How to take care of comma after reading excel data

3 Answers3