aspose file tools*
The moose likes Java in General and the fly likes read from csv: how to handle null? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "read from csv: how to handle null?" Watch "read from csv: how to handle null?" New topic
Author

read from csv: how to handle null?

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Hi all,

I have a csv file with data in it. example of data (imagine evert word in a diff cell)

Country ID Capital Comment
USA 44 WAS na
JAPAN TOKYO na

the problem here is the null value on the ID of JAPAN (it's empty). When I read the next value after JAPAN (expecring ID) I get the capital Tokyo. mmm....I'm expecting null?!

my code looks like this:


can anyone guide of how to get a null?
thanks
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Look at the alternative constructor with the boolean returnDelims. When that is false it returns tokens
[code[
JAPAN
TOKYO
NA
[/code]
Set to true it returns tokens and delimiters:

Now you can detect two commas in a row.

BUT! You will find that CSV is trickier than it looks. For example, a string token might have a comma inside it. Then the string is probably wrapped in quotes:

RICH GUYS, 1, "GATES, BILL"

And the token might have a quote in it, so the quote gets an escape charcter. And the token might have escape characters in it so they are escaped, too.

StringTokenizer not going to do the whole job. There are enough other quirks that it would be a good idea to Google for a proven CSV parser.
[ July 24, 2006: Message edited by: Stan James ]

A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
ok...this is good.

so I did this:
StringTokenizer st = new StringTokenizer(line,",", true);

and I am getting the commas (,) as you described. Now, what you are saying is this: if you find 2 consecutive commas - the cell is null.
EXCELLENT! THANKS

you also mentioned, and you were right, a problem with "GATES, BILL",

what if I recieve the file as is (I cant change it) and the cell contains the value: GATES, BILL (not "GATES, BILL")

how would you recomend reading the value as GATES BILL (no comma)
thanks Stan
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I don't know if CSV is a real standard or just common convention but I'd think a cell containing a comma that isn't escaped or quoted or something is just plain wrong. For my own work with CSV (not in Java) I used Excel save-as-CSV to generate test cases and to serve as the standard. Again, finding an established library would be the best bet here, if maybe not quite as educational and fun to write.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: read from csv: how to handle null?