hello!

i'm working with a dataset in the form of several directories of *.csv (semi-comma separated, actually, for some reason) files, which i would like to process and add to a mysql db. yesterday, mr. data converter came to my attention (praise twitter!) and a few tweaks to the code allowed me to use the semi-comma delimited text, via copy+paste.

this is nice, but i wondered if i could port the code (javascript) to processing so that i could recursively go through the files and add tables. while most of it is working (the syntax at least), a couple of issues are keeping me from actually testing this on the bunch of files i mentioned above.

below is the code i have thus far (apart from SQLibrary and boilerplate code for loading the files), and below that, the questions.

1. String newLine = "\n";
2. String indent = " ";
3. String toMySql(String[] lines, String tableName, String[] columnNames, String[] columnTypes, String indent, String newLine) {
4.   String outputText = "";
5.   int rowCount = lines.length;
6.   int columnCount = columnNames.length;
7.   //begin render loop
8.   outputText += "CREATE TABLE "+tableName+" (" + newLine;
9.   outputText += indent+"id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,"+newLine;
10.   for (int i=0; i < columnCount; i++) {
11.     String dataType = "VARCHAR(255)";
12.     if ((columnTypes[i].equals("int"))||(columnTypes[i].equals("float"))) {
13.       dataType = columnTypes[i].toUpperCase();
14.     };
15.     outputText += indent+""+columnNames[i]+" "+dataType;
16.     if (i < columnCount - 1) {
17.       outputText += ",";
18.     }
19.     outputText += newLine;
20.   }
21.   outputText += ");" + newLine;
22.   outputText += "INSERT INTO "+tableName+" "+newLine+indent+"(";
23.   for (int i=0; i < columnCount; i++) {
24.     outputText += columnNames[i];
25.     if (i < columnCount - 1) {
26.       outputText += ",";
27.     }
28.   }
29.   outputText += ") "+newLine+"VALUES "+newLine;
30.   for (int i=0; i < rowCount; i++) {
31.     outputText += indent+"(";
32.     for (int j=0; j < columnCount; j++) {
33.       if ((columnTypes[j] == "int")||(columnTypes[j] == "float")) {
34.         // outputText += lines[i][j] || "null";
35.       }
36.       else {
37.         // outputText += "'"+( lines[i][j] || "" )+"'";
38.       }
39.       if (j < columnCount - 1) outputText += ",";
40.     }
41.     outputText += ")";
42.     if (i < rowCount - 1) outputText += ","+newLine;
43.   }
44.   outputText += ";";
45.   return outputText;
46. }
47. String[] getColumnNames(String[] lines, String delimiter) {
48.   return split(lines[0], delimiter);
49. }
50. //test columns for number data type
51. String[] getColumnTypes(String[] lines, String delimiter) {
52.   int columnCount = split(lines[0], delimiter).length;
53.   String[] columnTypes = new String[columnCount];
54.   int numRowsToTest = columnCount;
55.   float threshold = 0.5;
56.   for (int i=0; i < columnCount; i++) {
57.     int floatCount = 0;
58.     int intCount = 0;
59.     for (int j=0; j < numRowsToTest; j++) {
60.       /*if (CSVParser.isNumber(lines[j][i])) {
61.        intCount++;
62.        if (String(lines[j][i]).indexOf(".") > 0) {
63.        floatCount++;
64.        }
65.        }*/
66.     }
67.     if ((intCount / numRowsToTest) > threshold) {
68.       if (floatCount > 0) {
69.         columnTypes[i] = "float";
70.       }
71.       else {
72.         columnTypes[i] = "int";
73.       }
74.     }
75.     else {
76.       columnTypes[i] = "string";
77.     }
78.   }
79.   return columnTypes;
80. }
81. boolean isNumber (String token) {
82.   /*if( (token.equals(null)) || isNaN( new Number(string) ) ) {
83.    return false;
84.    } */
85.   return true;
86. }

first up...

issue 1

1. for (int i=0; i < rowCount; i++) {
2.     outputText += indent+"(";
3.     for (int j=0; j < columnCount; j++) {
4.       if ((columnTypes[j] == "int")||(columnTypes[j] == "float")) {
5.         // HERE!
           // outputText += lines[i][j] || "null";
6.       }
7.       else {
8.          // AND HERE!
9.         // outputText += "'"+( lines[i][j] || "" )+"'";
10.       }
11.       if (j < columnCount - 1) outputText += ",";
12.     }
13.     outputText += ")";
14.     if (i < rowCount - 1) outputText += ","+newLine;
15.   }

the commented lines return a " the type of the expression must be an array type but it resolved to String" error. if i'm referring to an item/index of a two-dimensional String array, is that not a String itself?

does this have anything to do with using the "+=" operator, instead of "concat()" method in String, or something of the sort? or with using a single string ("outputText") for the whole mysql output, instead of a String array?

issue 2

1. boolean isNumber (String token) {
2.   /*if( (token.equals(null)) || isNaN( new Number(string) ) ) {
3.    return false;
4.    } */
5.   return true;
6. }

this is just plain wrong, i know. how would one go about writing a method in Processing for detecting if a String is a number?

thanks for reading,
bernardo

(edited, for completeness)