I was motivated to write this post by inquiries such as this one on MySQL forum about how to export data from database to Excel. MySQL is not the only one though, similar requests can be found on Oracle or Postgres forums too. Of course a CSV format or doing some coding is always an option, but Clover lets you generate XLS/XLS(X) effortlessly and with some advantages:
- Clover does NOT require Excel to do be installed on machine where we are doing the export. (Yes, this means you can export even on your Linux MySQL installation.)
- We can export from any database that has a JDBC driver.(MySQL, Oracle, Informix, DB2, Postgres)
- Export can run on any platform – Windows, OS X, Linux.
- It is possible to do additional validation and manipulation before writing to Excel.
To keep things simple we will work just with a single database table called customers stored in MySQL:
Our goals in this post are:
- Connect to the database and download the customer data.
- Write the customer data to Excel sheet, including the header with column names.
And finally, to make it more interesting
3. Split customers into multiple sheets based on the state of their origin.
Let’s build the graphs now:
Step 1: Connecting to MySQL database
Create a new project and a new graph (see the videos if you don’t know what I am talking about), go to Outline window and new database connection.
Connection to my MySQL database
Step 2: Building the graph
Build the graph from DBInputTable and XLSDataWriter components. DBInputTable reads data from database table, or result of SQL query, while ExcelDataWriter writes it to Excel spreadsheet.
Graph exporting from MySQL to Excel
You will need a metadata definition to describe the structure of your database records. The easiest way to do this is to use a wizard from Outline window. Right click on Metadata entry and select Metadata > Extract from database. Once done, assign your metadata on the link (edge) between the DBInputTable and XLSDataWriter. Basic structure of our export graph is laid out.
Metadata extracted from database
We’ll configure the components now.
Configure the DBInputTable to use the MySQL connection we created in the previous step. Supply the SQL query to retrieve the data. Mine is just simple “select *”, but it can be any statement – joins, aggregation, while-filtering etc.
DBInputTable Configuration
Next, configure the XLSDataWriter to write into desired output file. Notice the property settings. To include header on the very first line, we set the Metadata row property is to 1. Data must be appended after the header row. That is why the Start row property is set to 2.
XLSDataWriter Configuration
Step 4. Run the graph
Start the graph now and check the exported data in your output file. If you just wanted to export the data you are done!
Exported data in Excel format
Optionally: Split data to sheets based on field value
Clover has a nifty feature of being able to write data into multiple sheets, splitting them based on a field value. Let’s split our customers into sheets based on the state of their origin. To do this we only need to change a setting in XLSDataWriter configuration. Set the Sheet name property to value $state. This tells the writer to examine the value of state field of each record to be written our and place it to a matching sheet.
The resulting Excel file now contains multiple worksheets (one for each US state) and all records in the same worksheet have the same state.
After the reading is set up, you can further extend the graph with some more components – filters, sorters, duplicate removals, or reformat to compute new values or change structure of output. Of course, designing a reverse process – import from Excel to a database is similarly straightforward. Happy exporting!
