ETL Tool-kettle data import and Export-excel table to database

Case Purpose: When there are tens of thousands of records or more data in Excel, use Kettle to import into the database. Speed can feel much faster than copy-paste.

• Here I will demonstrate that there are more than 150,000 records of the data in the "data.xlsx" table, using kettle to import to the testuser user of the Oracle database.

preparatory work: 1.data.xlsx table;

2.testuser users will have a table with the same DATA.XLSX table structure (number of columns and field type).

Begin!!!

1. Create a new "transform", drag "Excel Input", "table output" into the new conversion workspace

File > New > Convert to open, and expand input and output in the left panel, and drag Excel input and table output to the transform 1 workspace. \ \ \ 1 is shown below:

Figure 1: Workspace dragged "Excel Input" and "table output"

2. Set "Excel input" related parameters

1), double-click the "Excel Input" icon in the "Convert 1" workspace to open the Settings window.

Figure 2: Fill in the contents of "Table Type" and "file or directory" two rows

Figure 3: When you click Add, the table of contents will appear in the "Selected files"

Figure 4: My data is in Sheet1, so Sheet1 is selected into the list

Figure 5: Open the Fields tab, click "Get fields from header data", and note the correctness of the Time field format

3. Set "table output" related parameters

1), double-click the "a" workspace (I'll "convert 1" to save the "table output" icon in "a") to open the Settings window.

Figure 6: Setting up the contents of the database connection

Figure 7: Setting the target mode

Figure 8: Setting the target table

Figure 9: Set the mapping of local tables to database tables (that is, if the fields correspond correctly)

Click "OK" to complete the setup after Setup is complete.

4. Run to import the table into the database.

Figure 10: Run and start

Figure 11: Detecting Yuncheng Results

Note: If the red shading appears in step measures, the import fails. View the prompts in the log. Personal experience, the most common error is that in the "Excel input" setting, the field type of the date Type field, is set to the character type.

End!!!

ETL Tool-kettle data import and Export-excel table to database