Csv file to MySQL database table

Question

Fix the error and i need to import(store) csv files to mysql database table

Class.forName("com.mysql.jdbc.Driver");
    java.sql.Connection c = null;
    c = DriverManager.getConnection("jdbc:mysql://localhost:3306/Sample", "root", "root");
    Statement s = null;
    s = (Statement) c.createStatement();
    int i = s.executeUpdate("load data local infile 'E:\\Book1.csv' into table sample.copy_table fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n'");

Returned errors :

Error:Invalid character constant in this line ("load data local infile'E:\\Book1.csv'.......)
java.sql.SQLException: Unable to open file 'E:Book1.csv'for 'LOAD DATA LOCAL INFILE' command.Due to underlying IOException: 

** BEGIN NESTED EXCEPTION ** 

java.io.FileNotFoundException
MESSAGE: E:Book1.csv (The system cannot find the file specified)

... and what exactly is the problem? Some error message? Data not correctly imported? — Sylvain Leroux
– Sylvain Leroux, Commented Aug 8, 2013 at 10:12
Since you are in Java, I think you should be doing terminated by '\\r\\n' and maybe also 'E:\\\\Book.csv'. That's because the literal you write will be escaped by Java, which will pass it to the SQL server, which will escape it again. If you write \\B, the server is receiving \B. — Giulio Franco
– Giulio Franco, Commented Aug 8, 2013 at 11:25

Stephan · Accepted Answer · 2013-08-08 11:49:54Z

1

Try this, you forgot to escape ":

int i = s.executeUpdate("load data local infile 'E:\\Book1.csv' into table sample.copy_table fields terminated by ',' optionally enclosed by '\"' lines terminated by '\r\n'");

answered Aug 8, 2013 at 11:49

Stephan

8,1103 gold badges40 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Csv file to MySQL database table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related