Here's How to Export Data from a Database to Excel using CloverDX

Written by
Michal Tomcanyi
Michal Tomcanyi

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 CloverDX lets you generate XLS/XLS(X) effortlessly and with some advantages:

  • CloverDX 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 data 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:

Export Data from a Database to Excel

Our goals in this post are:

  1. Connect to the database and download the customer data.
  2. Write the customer data to Excel sheet, including the header with column names.
  3. And finally, to make it more interesting - 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.

Export Data from a Database to Excel: Connection to MySQL database to export data

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.

Export Data from a Database to Excel: Graph to export data from MySQL to Excel

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.

Export Data from a Database to Excel: Metadata extracted from database - export data

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.

Export Data from a Database to Excel: DBInputTable Configuration - export data

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.

Export Data from a Database to Excel: XLSDataWriter configuration - export data

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!

Export Data from a Database to Excel: Export data in Excel format

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.

Export Data from a Database to Excel: Export data to multiple sheets

The resulting Excel file now contains multiple worksheets (one for each US state) and all records in the same worksheet have the same state.

Export Data from a Database to Excel: Export data to multiple sheets

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!

13 stages of a data migration project and how to manage them successfully:
Download 'The Guide to Data Migration Projects'

Posted on March 15, 2010.
DISCOVER  CloverDX Data Integration Platform  Design, automate and operate data jobs at scale. Learn more <>

Subscribe to our blog

Related Content

Where to go next