Several ways to import data from oracle to Excel

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. Compare the excelfile with a new file. For example, the file name is text.txt. Select a text file for the file type (tab-separated). Select csv as the file type (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 no table structure exists, 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 append method used in the preceding example is as follows:Insert, which is the default method. When data loading starts, the table is required to be blank; append: Add a new record to the table; replace: delete old record, replace with new 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: \ bad.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

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 do not need to check any help, you can.

Here we have two experiences:
1. 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.

2. When importing data from SQL Server to oracle, remember to write all the table names in oracle in uppercase.Because the oracle table names are in uppercase, and the SQL table may be mixed-sized, this table cannot be found in oracle after import, for example, if this Products table is queried in oracle, it should be written as select * from scott. "Products ";