export mysql data to csv file

Question

I am trying to do 2 output data from mysql to a csv file. My code is as follows:

public void exportData(Connection conn,String filename) {
        Statement stmt;
        String query;
        try {
            stmt = conn.createStatement();

            //For comma separated file
            query = "SELECT * into OUTFILE  '/tmp/input.csv' FIELDS TERMINATED BY ',' FROM router ";
            stmt.executeQuery(query);

        } catch(Exception e) {
            e.printStackTrace();
            stmt = null;
        }
    }
};

I get the following error at the line where stmt.executequery is called.

java.sql.SQLException: Access denied for user 'nxy'@'%' (using password: YES)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1476)
    at DBase.exportData(automateExport.java:50)
    at automateExport.main(automateExport.java:18)

I am able to connect to the database properly and even execute basic queries but unable to export data to a .csv file. Please help.

Thank you

Vineet Reynolds · Accepted Answer · 2010-07-30 22:13:51Z

3

You need to have the FILE privilege for your user account to allow for the file to be created at the server host, via a SELECT .. INTO OUTFILE command.

answered Jul 30, 2010 at 22:13

Vineet Reynolds

76.9k17 gold badges154 silver badges177 bronze badges

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

10 Comments

JJunior Over a year ago

Thanks for the quick response. Is there anyway I can enable it ?

Vineet Reynolds Over a year ago

You need to run GRANT FILE on "." to <user>@<host> (remove quotes around star-dot-star, as Stackoverflow is eating them up) to grant it to a user (replace the user and host tags with appropriate values for your case). Since GRANT FILE is an administrative privilege, you need to connect as root to execute it.

JJunior Over a year ago

Thanks. But I do not understand why I should create file at server host??? I just want to create a file on my machine and export the data here. Could you please explain??

JJunior Over a year ago

Is there no way to do it without using Grant File ?? As I do not have any kind of administrative access and unable to connect as root. :(

Vineet Reynolds Over a year ago

Well, in that case you'll need to write code (perhaps in Java itself) that will obtain all the rows of the table, which you can save as a CSV file (if you format the results correctly). This link might help you in assessing other options: support.modwest.com/content/6/135/en/…

|

Collectives™ on Stack Overflow

export mysql data to csv file

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related