There are several methods to import data to Oracle!

These methods can also be used to import data in other formats to Oracle. Here, we only use Excel as an example.

Method 1: use SQL * Loader

This is a lot of methods, as long as the target table already exists in Oracle Data.
The general steps are as follows:
1. Set the excelfile to a new file. For example, set the file name to text.txt. For the file type, select a text file (tab-separated). Here, set the file type to CSV (separated by commas), but write the following control. when CTL is used, change the field terminator to ',' (fields terminated by ','), and save it to the C root directory.
2. If there is no existing table structure, create it. Assume that the table is test and there are two columns: DM and Ms.
3. Use notepad to create an SQL * loader control file. The file name mentioned on the internet is suffixed with CTL. In fact, I can use the txt extension myself. For example, name it "control. CTL". The content is as follows)

Load data -- Control File ID
Infile 'C:/text.txt '--the name of the data file to be input is test.txt
Append into table test -- append records to Table Test
Fields terminated by '09' -- the field is terminated on '09' and is a tab)
(DM, MS) -- Define column correspondence order
Note: The data import method is the append used in the above example. There are several types: insert, which is the default mode. When data loading starts, the table must be blank; append: new records should be appended to the table; replace, delete the old record and replace it with the newly loaded record
; Truncate, same as replace.
4. Run the SQL * loader command at the command line prompt to input data.

Sqlldr userid = system/Manager Control = 'C:/control. CTL'

Let me give you an example.

1. Export the test.csv file of the example table
"01", "city"
"02", "Ke Qiao"
"03", "Qian Qing"
"04", "zhubu"
"05", ""
"06", "Qi Xian"
"08", "Jiang"
"09", "pingshui"
"12", "Dongpu"
....

2. Edit the sqlldr export control file test. CTL as follows:
Load data
Infile 'e:/test.csv'
Replace into Table Test
Fields terminated by ', 'optionally enclosed '"'
Trailing nullcols
(ID, name)

3. Run the following command in cmd:
E:/> sqlldr userid = xxx/YYY @ dbxx control = E:/test. CTL log = E:/log.txt bad = E:/B
Ad.txt

SQL * Loader: Release 8.1.7.0.0-production on Wednesday July 20 21:28:06 2005

(C) Copyright 2000 Oracle Corporation. All rights reserved.

Reaching the submission point, the logical record count is 48

E:/>
For more details, see http://www-rohan.sdsu.edu/doc/oracle/server803/A54652_01/ch04.htm#793

Method 2 Use PLSQL developer

The PLSQL developer tool is the most commonly used tool by famous oracle DBAs.
When a single file is small (less than 100000 rows) and the target table structure already exists-for excel, it will certainly not exceed, because the maximum behavior of an Excel file is 65536-you can select all data copies and then use the PLSQL developer tool.
1. In the SQL window of PLSQL developer, enter select * from test for update;
2. Press F8.
3. open the lock, click the plus sign, click the column header in the first column, select the entire column, paste it, and submit the commit statement.
There are also many third-party tools, such as toad, PL/SQL developer, and SQL navigator.

Method 3 use other data transfer tools as transfer stations.

I am using SQL Server 2000.
Import an Excel table to the SQL Server database, and then import it from SQL Server to Oracle. These two operations are simple. If you don't want to check the help, you can do it. Otherwise, you can ask me too :)
Here we have two experiences: first, note that the version and format of the Excel file must match the data source imported to SQL Server. If Microsoft Excel 5.0 is selected as the data source when importing data to SQL Server, and the Excel file version is micaosoft Excel/95, a problem may occur. For more information about how to view the excle file format, open the Excel file and save it as. You can see other storage types such as micaosoft Excel 5.0/95 workbooks or Microsoft Office excle workbooks.
Second, when importing data from SQL Server to Oracle, remember to write all the table names in Oracle in uppercase. It took some time to get this experience. At the beginning of the import, the SQL Server table imported by excle is named in lower case, and the default data is also in lower case in Oracle. Some strange situations have occurred, on the Enterprise Manager Console, you can view the imported tables in the list of all tables, and use the table data editor to view the imported data, however, it is strange that a table cannot be even seen in a specific tablespace. I am always wondering where the table is going. Second, no matter whether it is in a specific tablespace, table data is always visible, but in SQL * Plus or using PLSQL developer's SQL window, write select statements to view data, always prompt error: Ora-00942: Table or view does not exist. This problem began to plague me for a long time. Later, I had the chance to give it a try and change the table name to all uppercase when SQL Server imports oracle ), after the import, the records can be retrieved correctly regardless of whether the SQL * Plus or PLSQL developer writes the SELECT statement in the SQL window (the table name in the window can be in upper or lower case. This is a strange question. As an experience, if any hero has a high opinion, please do not give me any further advice.