A brief introduction to the data-exchange method between Excel and Oracle

As we all know, Microsoft Excel is intuitive and convenient for data entry, statistics, production charts, but its limited data management capabilities, a large number of data query capacity, if the advantages of its data calculation and large database Oracle data query Advantages, A powerful data-processing system can be designed.

Suppose that the employee situation table that corresponds to the Excel data file has been created in the Orac le system, where the fields are in order the employee Code (EMP-NO), Employee name (ename), salary (Salary), Employment Time (hiredate).

This article will use this example to introduce the concrete steps for data interchange between Excel and Oracl E.

1. Loading data in Excel into an Oracle database

The Advanced utility of Oracle database systems Sql*loader can load the data stored in the original Excel directly into the tables in the Oracle database.

First, after completing the form data entry in MicrosoftExcel, select the Save File command and select Format text file (space delimited) in the file Type column of the dialog box to form a standard formatted text file (*. PRN) or select "CSV (Comma delimited)" to form a comma-bound file (*. CSV). The difference between the two is: the standard format of the text file in each record, such as the length of the original library file fields in a compact, character-type field data left-aligned, data-type field data right alignment, insufficient part of the space to complement. The comma-delimited file feature is that each record can be unequal, the fields are sorted by their defined order, the fields are separated by commas, and the left and right spaces of the character field and data type fields are eliminated.

Since the two format files have different data loading methods, if we save the employee EMP form as emp.csv, the data becomes the following form:

120,joe,1192.64,93/04/11

121,kert,309.60,87/01/14

23,peter,1500.25,84/03/12

124,smith,678.00,91/01/07

15,young,2390.12,80/05/03

Then, the following control file is created by Notepad, which contains the name and format of the data file, how the File field is bounded, how the data type is, and so on, save the load control file as Emp.ctl:

LoadData INFILE ' C:\EMP. CSV ' Badfile ' EMP. Bad ' Discardfile ' EMP. DSC ' APPEND intotableemp fileds terminated by ', ' (Emp-no,ename,salary,hiredatedata (8) "Y Y/mm/dd")

After that, double-click the Sql*loader icon, enter the Ora 睠 LE7 Data Loading tool, in the pop-up Sqlloader main Screen dialog box, enter the user login name, password, database name and control filename, confirm the data load. In this way, while converting, loading data, the error messages and statistics in the execution process are deposited into the login file (LogFile), and the raw data that the Oracle rejects is sql*loader into the Reject file (rejectfile). After loading, the user can open the login file to see how the data is loaded, and understand why the data was rejected because the data itself does not conform to the database table definition or the integrity principle. This shows that data loading is not only fast but also safe.