I am making a program where I read data from .txt files and store them in tables. In my program the user would give the directory of the files, the program would find all the .txt files and for each one of these would create a table which would have as name the name of the file and each table would have two fields (text and price).
These two columns are separeted by space. In my code below is shown all the program. But I have problem when I am trying to import the data programmatically. The Error exception that I get is that I have error in SQL syntax. Could anyone help me because I am trying to solve it for a couple af days with no result?
public class notepad {
public static void main(String args[]) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection) DriverManager.getConnection(
"jdbc:mysql://localhost:3330/mydb", "root", "root");
String dirpath = "";
Scanner scanner1 = new Scanner(System.in);
while (true) {
System.out.println("Please give the directory:");
dirpath = scanner1.nextLine();
File fl = new File(dirpath);
if (fl.canRead())
break;
System.out.println("Error:Directory does not exists");
}
try {
String files;
File folder = new File(dirpath);
File[] listOfFiles = folder.listFiles();
for (int i = 0; i < listOfFiles.length; i++) {
if (listOfFiles[i].isFile()) {
files = listOfFiles[i].getName();
if (files.endsWith(".txt") || files.endsWith(".TXT")) {
List<File> txtFiles = new ArrayList<File>();
txtFiles.add(listOfFiles[i]);
String[] parts = files.split("\\.");
String tablename = parts[0];
for (File txtFile : txtFiles) {
List sheetData = new ArrayList();
try {
FileReader in = new FileReader(txtFile);
BufferedReader br = new BufferedReader(in);
String line = br.readLine();
while (line != null) {
System.out.println(line);
line = br.readLine();
String filename = dirpath.substring(dirpath
.indexOf('\\') - 2, files
.indexOf(parts[0]));
}
in.close();
} catch (Exception e) {
System.err.println("Error: " + e.getMessage());
}
getCreateTable1(con, tablename);
importData(con, txtFile, tablename);
}
}
}
}
} catch (Exception e) {
System.out.println();
}
}
private static String getCreateTable1(Connection con, String tablename) {
try {
Class.forName("com.mysql.jdbc.Driver");
Statement stmt = con.createStatement();
String createtable = "CREATE TABLE " + tablename
+ " ( text VARCHAR(255), price int )";
System.out.println("Create a new table in the database");
stmt.executeUpdate(createtable);
} catch (Exception e) {
System.out.println(((SQLException) e).getSQLState());
System.out.println(e.getMessage());
e.printStackTrace();
}
return null;
}
private static String importData(Connection con, File txtFile,
String tablename) {
try {
Statement stmt;
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String importingdata = "LOAD DATA INFILE '"
+ txtFile.getAbsolutePath() + "' INTO TABLE '" + tablename
+ " (text,price)";
stmt.executeUpdate(importingdata);
} catch (Exception e) {
System.out.println(((SQLException) e).getSQLState());
System.out.println(e.getMessage());
e.printStackTrace();
}
return null;
}
}