problem while importing the data from text to mysql

Question

Hi, I have used an application to import some data from a text file to mysql. I have used the following code.

try
{
   stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                               ResultSet.CONCUR_UPDATABLE);
   stmt1= (Statement) conn.createStatement();
   int deleteRows = stmt1.executeUpdate("delete  from powerdata where dateformat < dateformat_sub(now(), interval 8 month)");
   query = "LOAD DATA LOCAL INFILE \"E:\\powerdata.txt\" INTO TABLE powerdata  set dateformat=str_to_date(date, '%m/%d/%Y' '%H:%i:%s');";
   int Update= stmt.executeUpdate(query);
}

But the query to load the data from text to mysql is null. Can anybody tell me where I have made a mistake?

Answer 1

You probably haven't given the user you are using FILE privileges. If you look over the documentation they talk about the privileges a remote user would need to load data using LOAD DATA LOCAL INFILE. There is a good reason this is turned off so be careful if you enable it.

Answer 2

Could it be that the problem is the \"E:\\powerdata.txt\" bit? You're escaping the quotes, but you're not escaping the backslashes. That means your query actually looks like this: LOAD DATA LOCAL INFILE "E:\powerdata.txt" INTO TABLE powerdata set dateformat=str_to_date(date, '%m/%d/%Y' '%H:%i:%s');

To fix the filename, use \"E:\\\\powerdata.txt\".