Exception in thread "main" java.sql.SQLException: Unable to open file

Question

how to Solve this error and made load data query to open my file??

I get java.sql.SQLException: Unable to open file when trying to create statement from file. Code:

public class LoadData {

  public static void main(String[] args) throws Exception {

  File file=new File("C://Users//suriyan.s//Documents//suriyan//employees.csv");

  String filename = file.getAbsolutePath();

  System.out.println(filename);

  String tablename = "employee";

  Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/college", "root", "");


  // Load the data

  String qry = "LOAD DATA LOCAL INFILE '" + filename + "' INTO TABLE " + tablename + " FIELDS TERMINATED BY ','"
+ " LINES TERMINATED BY '\n';";

  Statement stmt = connection.createStatement();

  stmt.execute(qry);


  }
}

Can you print out the full query and show that to us? My guess is there is a small typo somewhere. It looks like you are surrounding the file path in single quotes. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 6, 2017 at 11:27

user85421 · Accepted Answer · 2017-07-06 12:49:23Z

1

Documentation of DATA LOAD INFILE:

On Windows, specify backslashes in path names as forward slashes or doubled backslashes.

getAbsolutePath is returning single backslashes...

The Exception should be including the file name as it is interpreted by MySQL.

Try (remembering that "\\" is a single backslash in Java):

String filename = file.getAbsolutePath().replace("\\", "/");
System.out.println("File: \"" + filename + "\"");  // just for debugging

Probably not the problem since accessing as root, but check:

Non-LOCAL load operations read text files located on the server. For security reasons, such operations require that you have the FILE privilege.

edited Jul 6, 2017 at 12:49

answered Jul 6, 2017 at 12:00

user85421

29.8k11 gold badges66 silver badges96 bronze badges

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

Comments

badgerboy86 · Accepted Answer · 2017-07-06 13:12:24Z

0

The problem could be the string query 'qry'. Try removing the semi-colon ; from the end of the string.

answered Jul 6, 2017 at 13:12

badgerboy86

887 bronze badges

1 Comment

user85421 Over a year ago

yes, that can be a problem depending on the driver/DB. I think MySQL is lenient , but Oracle did not accept it (long time ago).

Collectives™ on Stack Overflow

Exception in thread "main" java.sql.SQLException: Unable to open file

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related