Several Methods of Introducing Excel into oracle

Method 1. Use SQL*Loader
This is a more widely used method, provided that the destination table already exists in oracle data. The general steps are as follows:
1. Save the excel file as a new file, for example, the file name is text. txt, and the file type is text file (tab-delimited). Here, it is OK to select csv (comma-delimited), but when writing the following control. ctl, the field terminator should be changed to ',' (fields terminated by ','), assuming that it is saved to the root directory of c disk.

2. If no table structure exists, create it, assuming that the table is test and has two columns, dm and ms.

3. Create SQL*Loader control file with Notepad. The suffix of the file name on the Internet is ctl. In fact, I found that it is OK to use txt suffix. For example, it is named control. ctl, which reads as follows: (--The following is a comment, which is not actually needed)
load data--Control file identification
infile 'c:\ text. txt'--The file name of the data to be entered is test. txt
append into table test--Adding records to table test
fields terminated by X '09'-Field terminates at X '09' and is a tab (TAB)
(dm, ms)--Defines the column correspondence order

Remarks: The data import method used in the example of append, there are the following: insert, for the default way, data loading at the beginning of the table is required to be empty; append, append a new record to the table; replace, deleting old records and replacing them with newly loaded records; truncate, same as replace.

4. Enter data using the command SQL*Loader at the command prompt.
sqlldr userid=system/manager control='c:\control.ctl'

Let me give you an example
1. Export the test. csv file for the locale table
"01", "urban area"
"02", "Ke Qiao"
"03", "Qian Qing"
"04" and "Gaobu"
"05" and "Lizhu"
"06", "Qi Xian"
"08", "Paojiang"
"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 by '"'
trailing nullcols
(id,name)
3. Execute in cmd:
E:\ > sqlldr userid=xxx/yyy@dbxx control=e:\test.ctl log=e:\log.txt bad=e:\bad.txt
SQL*Loader: Release 8.1. 7.0. 0-Production on Week 3 July 2021: 28:06 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Commit point reached, logical record count 48

Method 2. Using PLSQL Developer
Use the PLSQL Developer tool, which is the most commonly used tool of the famous Oracle DBA.
In cases where a single file is small (less than 100,000 rows) and where the destination table structure already exists--certainly not more than that for excel, since the maximum behavior of the excel file is 65536--you can select all the data COPY and then use the PLSQL Developer tool.
1 Enter select * from test for update in sql window of PLSQL Developer;
2 Press F8
3 Open the lock, and then press 1 plus sign. Click the mouse to the first column header, so that all columns into the selected state, and then paste, and finally COMMIT can be submitted.
There are also many third-party tools, such as Toad, PL/SQL Developer, SQL Navigator and so on.

Method 3. Use other data transfer tools as transfer stations.
I'm using Sql Server 2000.
First import the excel table into the sqlserver database, and then import it from Sql server into oracle. These two operations are simple, if you don't look for help casually, you can.

Here are two of my own experiences:
1. Note that the version and format of the excel file should match the data source when importing into sql server. If the data source selected for importing to sql server is Microsoft Excel 97-2000, and the version of the excel file is Micaosoft Excel 5.0/95 workbook, there may be a problem. As to how to look at the Excle file format, you open the excel file and select Save As to see other save types such as Micaosoft Excel 5.0/95 workbook or Microsoft Office Excle workbook.

2. Remember to capitalize all table names in oracle when you import them from Sqlserver to oracle. Because the table names of oracle are all uppercase, and the table of sql may be mixed in size, the table cannot be found in oracle after import. For example, this Products table should be written as select * from scott. "Products" when queried in oracle;