Import and process TXT files in Oracle

Welcome to the Oracle community forum and interact with 2 million technical staff. When you enter the Oracle database, you will often encounter the problem of importing TXT files to Oracle for processing, in this way, the TXT data can be analyzed and processed. The following describes the complete import and processing methods as a reference. Other departments store their data in the form of. xls.

Welcome to the Oracle community forum to interact with 2 million technical staff> when you enter the Oracle database, you will often encounter the problem of importing TXT files to Oracle for processing, in this way, the TXT data can be analyzed and processed. The following describes the complete import and processing methods as a reference. Other departments store their data in the form of. xls.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Oracle databases often encounter the problem of importing TXT files to Oracle for processing, so that you can analyze and process the data in TXT files. The following describes the complete import and processing methods as a reference.

Other departments have their own data in the form of. xls. First, we need to process the column data and store it in TXT.

You can directly copy this column of data to a text editor such as Notepad ++ to prevent the occurrence of DOS line breaks. Otherwise, the data will not be successfully imported into the Oracle database.

The last row of the copied data in this column is always empty and needs to be deleted.

A txt file is generated, and 1.txt is generated.

Now, upload 1.txt to the Linux Script directory through FTP to facilitate the import.

During import, we need to use two programs: one is the import script daoru. sh and the other is the Import Control File daoru. ctl. To import all the programs in the script file, we only have one imported column, so we only need to create a column of TABLE PHONE_NO to meet our needs. If there are more columns, you can add them on your own.

Is the control file daoru. ctl. What we need to do is execute daoru. sh script to generate the temporary table hh_tmp1 and insert a column of PHONE_NO char (11) fields. The mobile phone number is 11 bits. We can use char (11.

After the execution, our 1.txt file has been imported into the temporary table hh_tmp1 of the database, because it is in the daoru. in the sh script, we have eliminated repeated operations on the imported data, so we only need to extract the data we want according to our needs.

We use PLSQL Developer. The database users logged on to by PLSQL Developer must be consistent with the database users you use in the script, and are connected through the original database table, to obtain the data we need.

Two columns are extracted: PHONE_NO and BELONG_CODE. The BELONG_CODE is sorted in ascending order to distinguish the region from the region.

Another example is to convert 1.txt to excel format, and then open it in windows. But the statements executed in linux.

Cat exp_imp_report.list | sed's/|/\ t/G'> 1.xls