method One, using Sql*loader
is a more important method, provided that the destination table in Oracle data already exists. The general steps are as follows:
1, save the Excel file as a new file, such as file name Text.txt, file type selection text file (Tab delimited), where the selection type is CSV (comma-delimited) is also OK, but when writing the following control.ctl, change the field terminator to ', ' (Fields terminated by ', '), assuming save to C-packing directory.
2, if no table structure exists, it is created, assuming the table is test, and there are two columns dm,ms.
3, with Notepad to create Sql*loader control files, said on the Internet file name suffix for ctl, in fact, I found on the use of TXT suffix also line. into table test-Append records to table test
Fields terminated by X ' 09 '--field terminated by X ' 09 ', is a tab (tab)
(DM,MS)--Define column corresponding order
notes: The way data is imported the append in the example is as follows: Insert, for default, to require a table to be empty at the start of a data load, append to append a new record to a table, replace, delete old records, Replace with the newly loaded record; truncate.
4, use the Sql*loader command at the command prompt to implement data entry.
Sqlldr userid=system/manager control= ' c:control.ctl '
I'll give an example
1. Export the Test.csv file for the regional table
"04", " Gao Bu "
" 05 "," Li Zhu "
" 06 "," Qixian "
" 08 "," Robe Jiang "
" 09 "," Ping Shui "
" 12 "," Dong Pu "
2. Edit Sqlldr Export Control file Test.ctl contents are as follows:
infile ' e:test.csv '
Replace into table test
Fields terminated by ', ' optionally enclosed by ' '
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 22.214.171.124.0-production on Wednesday July 21:28:06
(c) Copyright to Oracle Corporation. All rights reserved.
Reach submission point, logical record count by
Method II, using the Plsql Developer tool with Plsql Developer
, is the most common tool used by the well-known Oracle DBA.
in cases where a single file is small (less than 100000 lines) and the destination table structure already exists-certainly not for Excel, because the maximum behavior of an Excel file can be 65536--by selecting data Copy, and then using the Plsql Developer tools.
1 Enter the SELECT * from test for update in the SQL window of Plsql developer.
2 Press F8 to execute
3 to open the lock, and then click the plus sign. The column header to the first column, so that all the columns are selected, and then paste, the last commit can be committed.
There are many third-party tools, Toad,pl/sql developer,sql Navigator, and so on.
Method III, using other data transfer tools as a staging point.
I am using SQL Server 2000. The
Imports Excel tables into the SQL Server database before importing them into Oracle. These two operations are simple, if not just look for help.
Here are two of your own experiences:
First, notice that the version and format of the Excel file match the data source that was imported to SQL Server. If your data source is imported to SQL Server when you select Microsoft Excel 97-2000, and the version of the Excel file is micaosoft Excel 5.0/95 workbook, you may have problems. For information on how to look at the excle file format, you open the Excel file and choose Save As to see other save types such as Micaosoft Excel 5.0/95 workbooks or Microsoft Office excle workbooks, and so on. When
two is imported into Oracle by SQL Server, remember to capitalize all the table names in Oracle. because Oracle's table names are uppercase and SQL tables may be of mixed size, the table is not found in Oracle after import, for example, the Products table in Oracle query should be written as SELECT * from Scott. Products ";