CloverDX is a new name for CloverETL Learn more
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:
To keep things simple we will work just with a single database table called customers stored in MySQL:
Our goals in this post are:
Let's build the graphs now:
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.
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.
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.
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.
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.
Start the graph now and check the exported data in your output file. If you just wanted to export the data you are done!
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!
13 stages of a data migration project and how to manage them successfully:
Download 'The Guide to Data Migration Projects'