how can I parse CSV(excel,not separated by comma) file in Java ?

Question

I have a CSV files (excel) which has data in it and i need to parse the data using java. the data in those files doesn't separated using comma,the CSV files has number of columns and number of rows(each cell has data) where all the data is written. i need to go through on all the files until i get to the EOF(end of file)of each file and parse the data. the files contains also empty rows in it so empty row is not a criteria to stop parsing,i think only EOF will indicate that i've reached to the end of the specific file.

many thanks.

It's gonna be hard to help if you do not tell us what yoou already have tried? If files areb't comma separated, then by which other character?";" "tab" etc? — Romczyk, Dec 03 '12 at 07:21
Please edit your post and add some sample input (indent 4 spaces to make them fixed-width) — Jim Garrison, Dec 03 '12 at 07:23
i have tried to convert it to excel file (xls) and then parse is.every cell has a number in it.it doesn't separated by anything-it is not one string.tried to load an image but the site doesn't allow me. thanks — user1864229, Dec 03 '12 at 07:54
@user1864229 Try opening the CSV file in notepad. If you go from xls to csv it should seperate the values with ';' — Lyrion, Dec 03 '12 at 08:34

score 2 · Answer 1 · answered Dec 03 '12 at 07:44

2

You can use opencsv to parse the excel CSV. I've used this myself, all you need to do is split on the ';'. Empty cells will be parsed aswell.

You can find info here : http://opencsv.sourceforge.net/

And to parse the excelCSV you can do:

 CSVReader reader = new CSVReader(new FileReader("yourfile.csv"), ';');

answered Dec 03 '12 at 07:44

Lyrion

426
6
21

Rafael,how do i load the source(library) I've downloaded from sourceforge ? thanks – user1864229 Dec 03 '12 at 08:20
@user1864229 Just like you would any library? Put it in your library folder for that project. – Lyrion Dec 03 '12 at 08:33
is there a specific file/folder i need to use? – user1864229 Dec 03 '12 at 08:53
@user1864229 You need a 'lib' folder in your project – Lyrion Dec 03 '12 at 08:59

score 1 · Answer 2 · answered Dec 05 '12 at 06:14

Aside from other suggestions, I would offer Jackson CSV module. Jackson has very powerful data-binding functionality, and CSV module allows reading/writing as CSV as an alternative to JSON (or XML, YAML, and other supported formats). So you can also do conversions between other data formats, in addition to powerful CSV-to/from-POJO binding.

score 0 · Answer 3 · answered Dec 03 '12 at 08:13

Please have a Stream Object to read the CSV file.

FileInputStream fis = new FileInputStream("FileName.CSV");

BufferedInputStream bis = new BufferedInputStream(fis); InputStreamReader isr = new InputStreamReader(bis);

Read an inputstream Object and store the file in String object.

Then using StringTokenizer with ,[comma] as delimeter -->you will get the tokens Please manipulate the token to get the value.

String str = "This is String , split by StringTokenizer, created by mkyong";

StringTokenizer st = new StringTokenizer(str);

    System.out.println("---- Split by space ------");
    while (st.hasMoreElements()) {
        System.out.println(st.nextElement());
    }

    System.out.println("---- Split by comma ',' ------");
    StringTokenizer st2 = new StringTokenizer(str, ",");

    while (st2.hasMoreElements()) {
        System.out.println(st2.nextElement());
    }

Thanks,

Pavan

From [StringTokenizer](http://docs.oracle.com/javase/7/docs/api/java/util/StringTokenizer.html): `StringTokenizer is a legacy class that is retained for compatibility reasons although its use is discouraged in new code. It is recommended that anyone seeking this functionality use the split method of String or the java.util.regex package instead.` — Alexis Leclerc, Jan 30 '14 at 21:22

score 0 · Answer 4 · answered Oct 28 '17 at 07:38

Suppose you have a csv fileContent in form of string:

String fileContent;

Generally, the CSV fileContent are parsed into List>.

final List<String> rows = new ArrayList<String>(Lists.newArraysList(fileContent.split("[\\r\\n]+")));

Split the file into List of rows. Then use CSVParser of OpenCSV and parse the comma separated line into List

final CSVParser parser = new CSVParser();
final List<List<String>> csvDetails = new ArrayList<List<String>>();
    rows.forEach(t -> {
        try {
            csvDetails.add(Lists.newArrayList(parser.parseLine(t)));
            } catch (Exception e) {
                throw new RunTimeException("Exception occurred while parsing the data");
            }
        });

how can I parse CSV(excel,not separated by comma) file in Java ?

4 Answers4