Import database data into Excel using the Pentaho tool

Write in front: This blog describes how to use the Pentaho tool to quickly export database data to an Excel file, and how to import Excel file data into a database.

Add: Using this tool does not require any code and is quick and easy to solve the actual problem, this tool does not only limit this feature, other features later update.

Tool Download: You can choose different version according to the system of your computer to download on Pentaho website: http://www.pentaho.com/download

Requirement one: Export MySQL table data to Excel

First step: Add a Database driver package

Note: due to this example I am going to export MySQL database data so I need to copy the MySQL driver package to the bin directory in the root directory after extracting the tool.

Download the compressed package and unzip it in its root directory to have such a file.

This file is a batch file under Windows that opens the Pentaho tool.

Step Two: Create a new transformation

1, select the Convert right-click New

2. Drag a table entry in the left toolbar with an Excel output to the right of the work area.

Step Three: Configure the database connection

1, double click on the table input to enter the editing interface

Click New to enter database connection configuration

Resolution: Here the main configuration of the native name, and select the database type, configure the host IP, database name, port number, user name, password.

Once configured, you can preview the data.

Fourth Step: Configure the output Excel format

1, we can check the native MySQL data before the output.

2, hold down SHIFT and use the left mouse button to drag the table input to Excel output

3, similarly double-click Excel for related configuration, such as file location, file encoding

Fifth Step: Run

1, click on the toolbar above the work area to select Run

parsing: After running the data basically came out, and then the date does not display properly just set the Excel related properties.

Requirement Two: Import Excel data into MySQL database

First step: Organize your data

1, we modify the above exported data to facilitate the separation

2. Delete old data

Step Two: Create a new transformation

1, same as before, but this time is a new Excel input, table output

2, configure the connection, and Excel input, the table output is the same as before

Step Three: Configure character encoding

Note: when inserting data, you must not forget to configure the appropriate code, or you can look at the office ...

Fourth Step: View Results

Resolution: No problem, the data was successfully re-imported.

Summary:1, use this tool for database operations must first import the corresponding driver package

2, the relevant properties are more configuration, need to be cautious

Import database data into Excel using the Pentaho tool