Use the kettle component to import an excel file to the database, kettleexcel

Use the kettle component to import an excel file to the database, kettleexcel

Use the kettle component to import an excel file to the database

1. Goals

Import the excel file content to the target table. Then, use java to call kettle conversion. The excel file contains only two columns, for example:

The structure of the database table is as follows:

 

2. Design Analysis

• Use the oracle function to solve the field of the Yellow Line: Two uuid and two system dates;

Uuid needs to be generated, which can be implemented using SYS_UUID () function in oracle;

You 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;

• Use the named parameter to solve the Blue Line Field

The ID of the creator and the modifier (consistent with the two), organization ID, and source object ID respectively use the naming parameters:

$ {USER_ID}, $ {ORG_ID}, $ {FILE_ID}, $ {FILE_NAME };

The file name is used to read the excel file;

• Use the excel row content as the value of the Green Row

That is, the enterprise name and organization code fields;

• Use the default value as the value of the white line

3. Design kettle conversion files

Use the excel input step to read the excel content, and use the SQL Execution step to insert records into the data table;

3.1. Design Steps

Drag the excel input step from the input class to the workspace, and drag the execution SQL step from the step class to the workspace. Press shift to connect to the SQL step from the excel input step. For example:

3.2. configuration steps

• Procedure for configuring excel

Double-click excel to open the configuration dialog box:

• Configure database connection

Remember to add the jdbc driver of oracle to the lib directory of kettle and test the connection.

• Steps for configuring SQL Execution

Double-click this step to open the configuration. First select the database connection configured earlier, and then write the insert statement. Note that character types should be enclosed in quotation marks, even variables or? Must be included.

You must configure the corresponding stream fields in parameters for the question mark content;

To Execute multiple rows, select the Execute for each row option;

Because there are variables, You need to select the Variable substutution option;

3.3. Configure Parameters

The preceding analysis requires four parameters, which are used in two steps respectively. Therefore, you need to configure four parameters for conversion: USER_ID, ORG_ID, FILE_ID, and FILE_NAME;

Double-click the blank area of the workspace to open the conversion configuration, enter the corresponding parameters, or add the default value for testing in kettle until the test is successful.

So far, kettle's design work has been completed. You can refer to this blog article for a detailed description of dynamic queries at: http://blog.csdn.net/neweastsun/article/details/39287809;

4. Call and convert in java

Before calling the API, you need to prepare the content of the above four parameters, and then pass the parameters to the conversion for execution. For details, refer to this blog article:

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