0

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;
    }
}
2
  • show us the exception along with the stack-trace... also where the ex is thrown at create table or load data? Commented Jun 4, 2013 at 7:55
  • The error is when i load the data. "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''temp (text,price)' at line 1 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''temp (text,price)' at line 1" Commented Jun 4, 2013 at 8:04

1 Answer 1

1

Try change

+ txtFile.getAbsolutePath() + "' INTO TABLE '" + tablename
                                            ^

to

+ txtFile.getAbsolutePath() + "' INTO TABLE " + tablename

This orphan quote makes your statement invalid.

Sign up to request clarification or add additional context in comments.

6 Comments

Now I get this: java.sql.SQLException: File 'C:\ProgramData\MySQL\MySQL Server 5.6\data\UsersDocumentstest emp.txt' not found (Errcode: 22 - Invalid argument)
the path should be this : C:\ProgramData\MySQL\MySQL Server 5.6\data\UsersDocuments\test emp.txt
@dedmar That tells us that your statement is finally working which answers your question. But the problem is that your mysql can't read the file you specified. Probably because path and(or) filename are incorrect (as Stephan commented).
yes I understand what you said! Could you help me how I would take the path correctly?
@dedmar Did you try to execute your statement directly in mysql? Does a file with the path that you posted exist and is accessible?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.