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
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.
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;
That is, the enterprise name and organization code of two fields;
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
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 ">
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 ">
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