Oracle Excel Data Import method summaryAbstract: In the process of programming and data aggregation and exchange, it is often possible to import the file data content compiled by other personnel in the office environment into Oracle. Currently, program developers often use the following methods: 1. Use the import tool SQL * loader provided by Oracle; 2. Use the PLSQL developer tool; 3. Use other data transfer tools as the transfer station. The following describes the advantages and disadvantages of Several import methods and their operation steps using the instance method. Assume that the 'e:/test.xls 'file contains three columns of data representing the customer ID, customer name, and contact number. The content is as follows:
| 10001 |
Zjaxi01 |
13562485246 |
| 10002 |
Zjaxi02 |
13562485247 |
| 10003 |
Zjaxi03 |
13562485248 |
| 10004 |
Zjaxi04 |
13562485249 |
| 10005 |
Zjaxi05 |
13562485250 |
| 10006 |
Zjaxi06 |
13562485251 |
| 10007 |
Zjaxi07 |
13562485252 |
| 10008 |
Zjaxi08 |
13562485253 |
| 10009 |
Zjaxi09 |
13562485254 |
| 10010 |
Zjaxi10 |
13562485255 |
| 10011 |
Zjaxi11 |
13562485256 |
| 10012 |
Zjaxi12 |
13562485257 |
| 10013 |
Zjaxi13 |
13562485258 |
| 10014 |
Zjaxi14 |
13562485259 |
Use SQL * LoaderSQL * loader is an Oracle Data Loading tool. In NT, SQL * loader commands are sqlldr and sqlldr/sqlload commands are generally used in UNIX. It is usually used to migrate operating system files to Oracle databases. It is the loading method used by large data warehouses because it provides the fastest way (direct, parallel ). However, this method requires that the Oracle table that stores data already exists. When using this method to import Excel content to Oracle, you must first save the Excel file as the text format, select the text type or CSV type, and save E:/test.xls as E:/test.csv. If Oracle does not have a table structure that stores data, you need to create the table test (ID, name, telphone ). Use NotePad to create the SQL * loader control file test. CTL (the CTL suffix is an attempt to prove that this is a control file, but in fact this file suffix is not important, users can choose freely, but the file format must be text format), the content is as follows: load datainfile 'e:/test.csv '-- data source file name append | insert | Replace -- append after the table, insert into an empty table, replace replaces the original content into table test -- Name of the database table to be imported [When id = id_memo] -- filter condition fields terminated by X '09' -- field separator (ID, name, telphone) -- enter the sqlldr command line in the Command window to execute the import operation sqlldr userid = system/Manager Control = 'e:/test. keyword description of the CTL 'sqlldr function: userid -- Oracle username userid = username/passwordcontrol -- control file name control = 'e:/insert. CTL 'Log -- Log File Name Log = 'e:/insert. log 'bad -- corrupted file name data -- data file namediscard -- discard file namediscardmax -- number of discards to allow (all by default) Skip -- number of records skipped during import (0 by default) load -- number of records imported during import (all by default) errors -- number of records allowed for error (50 by default)
Use PLSQL developerPL/SQL developer is an integrated development environment dedicated to developing, testing, debugging, and optimizing Oracle PL/SQL storage program units, such as triggers, stored procedures, functions, and packages. If there is not much data in a single file (less than 10 million rows) and the target table structure already exists, you can use PLSQL developer to import the Excel content directly to the Oracle database through simple copy and paste operations. The procedure is as follows: l in the SQL window of PLSQL developer, enter select * from test for update, where test is the name of the Oracle data table to be imported; l execute the query (by clicking the button or the shortcut key F8); l click the lock button above the query result to edit the queried content. L copy (CTRL + C) from the Excel data table to import data in Oracle. If it is a single column data import, You can first press "add (+)" in the query result in PLSQL developer) "button to make the data table in the" add data "status, click the column name of PLSQL developer, and press Ctrl + V to import the data, click the submit button in PLSQL developer to submit the changes. L if you want to import all columns at the same time, first select copy all data, then add (+) and click the asterisk at the beginning of the line to make the entire row of data in the selected state, finally, you can import data through paste. L if data is imported separately, you must first select copy data to be imported, then add (+) and click Edit name of that column, make the entire column of data in the selected state, and finally the data can be imported into the paste. When PLSQL developer is used to import and export data, the main problem is that Chinese data may fail when the data volume is large, it is better to define the type of the received data table as nvarchar2. In addition, this method requires more manual participation and cannot achieve automatic import.
Use SQL Server for transitWhen performing this operation, you must first import and export the Excel content to the SQL Server database using the SQL Server Import and Export tool, and then use the same tool to import the SQL Server intermediate content to the Oracle database, two data replication and I/O operations are required. The efficiency is relatively low. In addition, according to csdn user jkflyfox, In this method, all Oracle file names must be capitalized; otherwise, errors may occur.
