Excel Imports Oracle data, Oracle database Export Excel method

Export
If you export, select the query results in the query results in Pl/sql SQL window and right click on the selection of COPY to Excel

Import

Method One
The following article is mainly about how to use Sql*loader to export Excel-related data to the Oracle database, the main purpose is to achieve the Oracle database to insert Excel related files in the actual application data, the following is the specific content of the article introduction.

Implementation steps:

1. Open Microsoft Excel

2, file (F) → new (N) → workbook →

3, input Sql*loader will Excel data, save for Test.xls,

4, document (F) → Save As (A) →

Save type is: Tab delimited, named Text.txt, save to C: (can also be saved as a CSV file, separated by commas)

5, must first create the table structure:

Connect to Sql*plus to System/manager user login,

The following is a code fragment:sql> conn System/manager

Create a table structure

The following is a code fragment:

Sql> CREATE TABLE Test (ID number,--ordinal

USERNAMEVARCHAR2 (10),--User name

PASSWORDVARCHAR2 (10),--Password

SJ VARCHAR2 (20)--date of establishment;

6, to create Sql*loader input data Oracle database required files, are saved to C:, edited with Notepad:

Control file: Input.ctl, the contents are as follows:

Load data--1, control file identification

Infile´test.txt´--2, the data file to enter is named Test.txtappend

Into table test--3, append records to table test

Fields terminated by x´09´--4, field terminated by X´09´, is a tab character (tab), if it is a CSV file, change to: Fields terminated By´,´

(ID,USERNAME,PASSWORD,SJ)--Define column correspondence order

A, insert, by default, requires the table to be empty when Sql*loader starts the Excel data load

b, append, append new records to the table

c, replace, delete old records, replace the new Loaded records

D, Truncate, ditto

7, in the DOS window using the Sql*loader command to achieve data input www.111cn.net

Here is the code fragment: C:>sqlldr System/manager Control=input.ctl

The default log file name is: Input.log

Default bad record file is: Input.bad

If you are importing an Excel database to an Oracle database operation remotely to Sql*loader, the input string should read:

The following is a code fragment:

C:>sqlldr userid=system/manager@servicename_192.168.1.248 Control=input.ctl

8, connected to Sql*plus, to see if the successful input, you can compare Input.log with the original Test.xls file, to see whether the Oracle database import all, whether the import success.

Note: The contents of the cell in Excel do not have additional styles such as line wrapping, annotation, etc., which may cause the exported CSV or TXT file to contain special symbols so that it cannot be imported

Method Two

You can also import SQL Server to import Oracle with its export tools, but not for some image fields, if you don't have this kind of field.

The Data export Import feature provided by Ms_sqlserver can facilitate the easy import of Excel into Oracle database

Import the Excel table into the SQL Server database, and then import it into Oracle. These two operations are simple, if not casually check a help on it, or ask me to do:
Here are two of your own experiences: note that the version and format of the Excel file are matched to the data source when you import 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.
The second is when SQL Server is imported into Oracle, remember to capitalize all the table names in Oracle. It took a lot of time for this experience to change. When I first started, because the table named SQL Server imported by excle is lowercase and then down to Oracle, the default is lowercase, and there are some very strange cases in which the Enterprise Manager console You can see the poured table in the list of all tables, and you can also see the imported data with the table Data Editor. But in the concrete table space does not even see the table, it is strange, I always think that the table exactly where to go; second, no matter in the specific table space to see, the table data can always be seen, But in Sql*plus or by using the SQL Window of Plsql Developer, writing a SELECT statement to view the data, always prompting an error: Ora-00942: The table or view does not exist. This problem began to haunt me for a long time, and then brainwave, holding a try to try the psychology of the table name is changed to all uppercase (not partially capitalized) when imported by SQL Server, regardless of sql*plus or plsql developer SQL window to write a SELECT statement (inside the table name in uppercase or lowercase), you can correctly remove the record. Very strange question, as experience, if any heroes have ideas, please do not hesitate to enlighten. Www.111cn.net

Method Three

With the Plsql developer tool, when a single file is small (less than 100000 lines) and the destination table structure already exists-it certainly won't exceed for Excel, because the maximum behavior of the Excel file 65536--can be replicated in all data, Then use the Plsql Developer tool.
1 Enter SELECT * FROM test for update in Plsql Developer SQL window;
2 Press F8 to execute
3 Open the Lock, and then click the plus sign. The first column of the mouse point to the column header, so that all the selected state, and then paste, the last commit (check) to submit

Method Four

Can find this tool online Xlstoora, small and easy to use, cracked version was not found, I only English version, can import 1000.