Results 1 to 5 of 5

Thread: mySQL -> excel

  1. #1
    Join Date
    Apr 2005
    Posts
    46

    Post Unanswered: mySQL -> excel

    I have a mySQL database, and I want to export a table to open in excel. I want to be doing it through PHP, so I could click a link and have the file download, but I am looking specifically for the mySQL to save the file.

    mysqldump --opt --user=username --password database > filetosaveto.sql
    ...or...
    (mysqldump [options] db_name [tables] > filetosaveto.sql)

    It is my understanding that the above statement will create "filetosaveto.sql" as a file in the directory that it is run.

    What can you recomend?

  2. #2
    Join Date
    Apr 2005
    Posts
    46
    I tried to use:

    SELECT DISTINCT * INTO OUTFILE "/result.txt" FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n" FROM MailingList

    to save my "mailingList" table into "result.txt" file...
    I am using this through PHP, and am getting an error: "Error: 1045: Access denied for user: 'username@%' (Using password: YES)"

    I'm not sure why it won't grant access, there are other things on the page that are reading and writing to the table and connecting fine with hte username/pw.

    I know this isn't the PHP forum, but was curious if the code above will get my desired outcome. Please let me know, thanks.

  3. #3
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    For your SELECT INTO OUTFILE problem, the user you are connecting to the database with does not have privileges to create a file on the database server. See the following from the mysql manual -
    The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.

    The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host (meaning the client that is connecting to the database, which would be the web server/PHP) other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host.

  4. #4
    Join Date
    Apr 2005
    Posts
    46

    Exclamation

    Is this something I can get around with permissions?
    I need to have this .csv file saved to my web directory (or
    directly to my desktop) for me to open in excel.

    It is hosted on a hosting provider, so I don't have
    direct access to the server.

    What's the best way to save and open this .csv file?

  5. #5
    Join Date
    Apr 2005
    Posts
    46
    Solution can be found in my post on the PHP section...as the solution is through php.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •