Import an Excel file into a database using the Kettle component

Import an Excel file into a database using the Kettle component

1. achieve Goals

Import the contents of the Excel file into the target table, then use Java to invoke the Kettle transformation. The contents of an Excel file are only two columns , as shown in the following scales:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbmv3zwfzdhn1bg==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

The structure of the database table is as follows:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbmv3zwfzdhn1bg==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

2. Design Analysis

• Use Oracle functions to resolve fields for yellow rows: two uuid and two system dates;

A uuidis required, which can be implemented in Oracle using the sys_uuid () function;

You need to generate the current time as the creation time and the change time, using Oracle 's to_char (sysdate, ' yyyy-mm-dd hh:mm:ss ') implementation.

• Use named parameters to resolve Blue line fields

Creator and moving ID(both identical), organization ID, IDof source file, using named parameters, respectively:

${user_id },${org_id},${file_id},${file_name};

The file name is used to read the Excel step;

• Use Excel row content as the value of a green row

That is, the enterprise name and organization code of two fields;

• Use default values as values for white rows

3. Design the kettle conversion file

Use the Excel input step to read the Excel content, and use the run SQL footstep step to insert the record into the data table.

3.1. Design Steps

Drag the Excel input step from the input category to the workspace, and drag the run SQL footstep from the footstep category to the workspace. Press the shift key. Connect to the SQL footstep Step from the Excel input step. For example, with:

3.2. Configuration Steps

• Configure Excel steps

Double-click the Excel step to open the Configuration dialog box:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbmv3zwfzdhn1bg==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

• Configure Database connections

Remember to add the JDBC driver for Oracle to the Kettle lib folder. Able to test the connection.

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbmv3zwfzdhn1bg==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

• Configure run SQL footstep steps

Double-click the step to open the configuration. First, select the database connection that you configured earlier. Then write the insert statement.

It is important to note that the character type is enclosed by an argument, even if it is a variable or. All need to be enclosed.

The contents of the question mark need to be configured with the corresponding stream field in parameters .

You need to select the Execute for each row option because you need to run multiple lines;

Due to the variable, the Variable substutution option needs to be selected;

3.3. Configuration Parameters

The previous analysis required four parameters, which were used in two steps. So we need to configure four named parameters: user_id,org_id,file_id,file_name;

Double-click the blank section of the workspace to open the transformation configuration. Enter the corresponding parameter, also can add the default value, used in Kettle to test the use, until the test is not error.

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbmv3zwfzdhn1bg==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

So far, the design work in Kettle has been done. You can participate in this blog post, about dynamic query specific instructions. Address:http://blog.csdn.net/neweastsun/article/details/39287809;

4. Invoking Transformations in java

Before calling, you need to prepare the above four parameters and then pass the parameters to the conversion run. Specific content for this blog post:

http://blog.csdn.net/neweastsun/article/details/40886841

Import an Excel file into a database using the Kettle component