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 the Excel file are only two columns , as shown in the following example:

The database tables are structured as follows:

2. Design Analysis

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

The uuidneeds to be generated, which can be implemented in Oracle using the sys_uuid () function;

Need to generate the current time as the creation time and modification time, using Oracle 's to_char (sysdate, ' yyyy-mm-dd hh:mm:ss ') implementation;

• Resolve Blue row fields with named arguments

The ID of the creator and the person who modified it (both), the organization ID, the IDof the source file, and the named parameters, respectively:

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

Where the file name is to be 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 insert the record into the data table using the Execute SQL footstep step;

3.1. Design Steps

Drag the Excel input step from the input category to the workspace, drag from the Footstep category to execute the SQL footstep to the workspace, and press shift to connect from the Excel input step to the SQL footstep steps. Such as:

3.2. Configuration Steps

• Configure Excel steps

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

• Configure Database connections

Remember to add the Oracle JDBC driver to the Kettle lib directory to test the connection.

• Configuring the Execute SQL footstep Step

Double-click the step to open the configuration, first select the database connection that you configured earlier, and then write the insert statement. It is important to note that the character type is enclosed in quotation marks, even if it is a variable or. All need to be enclosed together.

The content of the question mark needs to be configured with the corresponding stream field in parameters ;

Because multiple rows need to be executed, the execute for each row option needs to be checked;

Because there are variables, the Variable substutution option needs to be checked;

3.3. Configuration Parameters

The previous analysis requires four parameters, which are used in two steps, so we need to configure four named parameters for the transformation: user_id,org_id,file_id, file_name;

Double-click the blank section of the workspace to open the transformation configuration, enter the appropriate parameters, or add a default value for test use in Kettle until the test is not error-free.

At this point, the design work in Kettle has been completed, we can refer to this blog post, on the dynamic query detailed instructions, address:http://blog.csdn.net/neweastsun/article/details/ 39287809;

4. Invoking Transformations in java

Before calling, you need to prepare the contents of the above four parameters, and then pass the parameters to the conversion execution; For more information, refer to this blog post:

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

Import an Excel file into a database using the Kettle component