Please stop writing faulty CSV parsers!
I've seen hundreds of CSV parsers and so called tutorials for them online.
Nearly every one of them gets it wrong!
This wouldn't be such a bad thing as it doesn't affect me but people who try to write CSV readers and get it wrong tend to write CSV writers, too. And get them wrong as well. And these ones I have to write parsers for.
Please keep in mind that CSV (in order of increasing not so obviousness):
- can have quoting characters around values
- can have other quoting characters than "
- can even have other quoting characters than " and '
- can have no quoting characters at all
- can even have quoting characters on some values and none on others
- can have other separators than , and ;
- can have whitespace between seperators and (quoted) values
- can have other charsets than ascii
- should have the same number of values in each row, but doesn't always
- can contain empty fields, either quoted:
"foo","","bar"or not:"foo",,"bar" - can contain newlines in values
- can not contain newlines in values if they are not delimited
- can not contain newlines between values
- can have the delimiting character within the value if properly escaped
- does not use backslash to escape delimiters but...
- uses the quoting character itself to escape it, e.g.
Frodo's Ringwill be'Frodo''s Ring' - can have the quoting character at beginning or end of value, or even as only character (
"foo""", """bar", """") - can even have the quoted character within the not quoted value; this one is not escaped
If you think this is obvious not a problem, then think again. I've seen every single one of these items implemented wrongly. Even in major software packages. (e.g. Office-Suites, CRM Systems)
There are good and correctly working out-of-the-box CSV readers and writers out there:
- opencsv
- Ostermiller Java Utilities
- Apache Commons CSV
If you insist on writing your own at least read the (very short) RFC for CSV.
Answer from Scheintod on Stack OverflowPlease stop writing faulty CSV parsers!
I've seen hundreds of CSV parsers and so called tutorials for them online.
Nearly every one of them gets it wrong!
This wouldn't be such a bad thing as it doesn't affect me but people who try to write CSV readers and get it wrong tend to write CSV writers, too. And get them wrong as well. And these ones I have to write parsers for.
Please keep in mind that CSV (in order of increasing not so obviousness):
- can have quoting characters around values
- can have other quoting characters than "
- can even have other quoting characters than " and '
- can have no quoting characters at all
- can even have quoting characters on some values and none on others
- can have other separators than , and ;
- can have whitespace between seperators and (quoted) values
- can have other charsets than ascii
- should have the same number of values in each row, but doesn't always
- can contain empty fields, either quoted:
"foo","","bar"or not:"foo",,"bar" - can contain newlines in values
- can not contain newlines in values if they are not delimited
- can not contain newlines between values
- can have the delimiting character within the value if properly escaped
- does not use backslash to escape delimiters but...
- uses the quoting character itself to escape it, e.g.
Frodo's Ringwill be'Frodo''s Ring' - can have the quoting character at beginning or end of value, or even as only character (
"foo""", """bar", """") - can even have the quoted character within the not quoted value; this one is not escaped
If you think this is obvious not a problem, then think again. I've seen every single one of these items implemented wrongly. Even in major software packages. (e.g. Office-Suites, CRM Systems)
There are good and correctly working out-of-the-box CSV readers and writers out there:
- opencsv
- Ostermiller Java Utilities
- Apache Commons CSV
If you insist on writing your own at least read the (very short) RFC for CSV.
scanner.useDelimiter(",");
This should work.
import java.io.File;
import java.io.FileNotFoundException;
import java.util.Scanner;
public class TestScanner {
public static void main(String[] args) throws FileNotFoundException {
Scanner scanner = new Scanner(new File("/Users/pankaj/abc.csv"));
scanner.useDelimiter(",");
while(scanner.hasNext()){
System.out.print(scanner.next()+"|");
}
scanner.close();
}
}
For CSV File:
a,b,c d,e
1,2,3 4,5
X,Y,Z A,B
Output is:
a|b|c d|e
1|2|3 4|5
X|Y|Z A|B|
Reading data from a CSV file
java - Reading a .CSV file using Scanner? - Stack Overflow
How do you read data from a .csv file in Java?
How to read and print only specific rows of a CSV file?
Videos
I'm attempting to read data from a CSV file, then put each row into an object, then place the object inside an arraylists of objects. I seem to be getting an error on this line int age = fileReader.nextInt();
Can't seem to figure out what the issue is. Can anyone point me in the right direction?
Here is my full code:
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package testfileio;
import java.io.FileNotFoundException;
import java.io.File;
import java.util.Scanner;
/**
*
* @author gregf
*/
public class TestFIleIO {
/**
* @param args the command line arguments
* @throws java.io.FileNotFoundException
*/
public static void main(String[] args)
throws FileNotFoundException {
Company jam = new Company();
File csv = new File("C:\\Users\\gregf\\Documents\\NetBeansProjects\\TestFIleIO\\employees.csv");
Scanner fileReader = new Scanner(csv);
fileReader.useDelimiter(",|\n");
//skips first row (column headers)
fileReader.nextLine();
while(fileReader.hasNext()) {
String name = fileReader.next();
int age = fileReader.nextInt();
fileReader.nextLine();
try {
jam.setEmployees(new Employee(name, age));
}
catch(Exception ex) {
System.out.println(ex);
}
}
System.out.println(jam);
}
}A few notes:
Employee is an object with the name: String and age: int data fields
Company is an object that contains an ArrayList of Employee type and has a toString method to print all the emplyoee names and ages.
Edit: Here is my error:
Exception in thread "main" java.util.InputMismatchException at java.base/java.util.Scanner.throwFor(Scanner.java:939) at java.base/java.util.Scanner.next(Scanner.java:1594) at java.base/java.util.Scanner.nextInt(Scanner.java:2258) at java.base/java.util.Scanner.nextInt(Scanner.java:2212) at testfileio.TestFIleIO.main(TestFIleIO.java:50) C:\Users\gregf\Documents\NetBeansProjects\TestFIleIO\nbproject\build-impl.xml:1328: The following error occurred while executing this line: C:\Users\gregf\Documents\NetBeansProjects\TestFIleIO\nbproject\build-impl.xml:948: Java returned: 1 BUILD FAILED (total time: 0 seconds)
If I have an Excel file with columns of numerical values in and want to use these in a Java program how would I go about doing this?
In this case I have a few hundred rows of four-vectors (px, py, pz, E) and want to use the different values later on in the program (I can do this, it's just attempting to get the program to read them properly that's getting me stuck).