How to export mysql data as xlsx?-CodePudding

How to export the data from mysql as xlsx, mysql workbranch only support export as CSV as i see in workbranch. I wan to have some programme which can directly get an mysql stored procedures result as .XLSX format file. Any Idea?

CodePudding user response：

If you're using python, you can use pandas to read the MySQL database into a dataframe and then export it to an excel file using the to_excel function.

CodePudding user response：

This worked for me -- You need to create the csv file first

import mysql.connector

db= mysql.connector.connect(host='localhost',user='username',
      password='password',
      database="dbname")

cur=db.cursor()

QUERY='SELECT * FROM tablename;'

cur.execute(QUERY)
result=cur.fetchall()
path = "..."
result.to_csv(f"{path}.csv")

CodePudding user response：

SELECT actor.actor_id ,actor.first_name ,actor.last_name ,actor.last_update FROM sakila.actor INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/actor.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'