Category Archives: java

Replace Null Values in CSV with Java

I’ve been working with the weka java machining learning algorithms, and with the large amounts of data extracted from databases via SQL, I’ve been running into the issue of having null values.  In order for the data to be read by weka, they nulls need to be replaced with a value, which in my case, should be 0.

Here is a sample of one row of my data:  2725079062,2,77,,,,,,,,,,,,,,,4,2,2,,t
There are many ways to do this, here is a common approach:

 mylines=mylines.replaceAll(",,", ",0,");

however what I was running into is that if there are multiple “,,” it doesn’t parse correctly.  Below is how I solved it:

public class ConvertData {
public static void main(String[] args) throws IOException {
BufferedReader br = new BufferedReader(new FileReader(
"data/address_yes_raw.csv"));
BufferedWriter bw = new BufferedWriter(new FileWriter(
"data/address_yes_raw_zero.csv"));
String line = "";
while ((line = br.readLine()) != null) {
String[] values = line.split(",", -1); 
// make an array out of line
String writableString = ""; 
//initial string which will be the final output for the row
ArrayList al = new ArrayList(); 
// use array list because can edit array and modify size easily
for (String element : values) {
if (element==null || element.length()==0) {
al.add("0");
} else {
al.add(element);
}
}
for (String s : al){ // add commas between each element of arraylist
writableString += s + ",";
}
writableString = writableString.substring(0, // remove last comma
writableString.length() - 1);
bw.write(writableString + "\n"); 

//writes the line and carriage return
}
br.close();
bw.close();
}
}

Final output would be like this:
2725079062,2,77,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,2,2,0,t

*side note, remember to resfresh the data folder when exporting