Importing Excel Data to Oracle

A. There is a difficult but successful method.
1. Create an Access database locally and import Excel Data to access. You can right-click Import and choose import from the shortcut menu. Select the Excel file to be imported as the file type, or use the transferspreadsheet function to create a macro.
2. Create ODBC locally and connect to the Oracle database. Create a table to be imported to Oracle in local access using a chain table (do not use an import table ).
3. Create an insert query in the ACCESS database and import the imported Excel Data to Oracle.

This method is successful.

B. You can also import SQL Server to Oracle using its export tool, but it does not support some image fields. If
This is fine.

Using the data export and import function provided by ms_sqlserver, you can easily import EXCEL to the Oracle database.

C. A method:
Set excelease to A. csvformat file, such as test.csv, and then write an insert. CTL file.
Use sqlldr for import!
The insert. CTL content is as follows:
Load data -- 1. Control File ID
Infile 'test.csv '-the name of the data file to be input is test.csv
Append into Table table_name -- 3. append a record to table table_name
Fields terminated by ',' -- 4. The field ends at ',', which is a comma.
(Field1,
Field2,
Field3,
...
Fieldn) ----- define the sequence of Columns

Note that the field order in the brackets must correspond to that in the CSV file.
Then you can execute the following command:
Sqlldr user/password control = insert. CTL

Sqlldr Problems

Control File: Input. CTL. The content is as follows:
Load data -- 1. Control File ID
Infile 'test.txt '-the name of the data file to be input is test.txt
Append into table test -- 3. append records to Table Test
Fields terminated by X '09' -- 4. The field is terminated on X '09' and is a tab)
(ID, username, password, SJ) ----- defines the column Sequence

Append is the data loading method, and there are other options:
A. insert, which is the default mode. The table is required to be empty when data loading starts.
B. append: Add a new record to the table
C. Replace: delete old records and replace them with newly loaded records.
D. truncate, same as above

Q: Can sqlldr retain the original data in the table? If duplicate keys are found, update the corresponding records?

D. What should I do first? Excel file ?? Into a TXT file
Use sqlloader again

E. Save the Excel file in *. CSV format. Then use sqlload to import the table.

Sqlload is a built-in data import tool in Oracle, and there should be no visualization tool (at least I have been using command line ).
You can directly input sqlldr in the command line to get help.

F. If your single file is not large (less than 100000 rows), you can select copy and use PL/SQL .:
Select * from table for update. open the lock and click the plus sign. point the mouse to the first space and paste it. commit. (TIPS: Oracle does not support it, but it is easy to use and won't be a problem)

G and golden imp/exp tools can directly import text files, which is very simple

Which of the following methods is applicable:
Rest assured: a maximum of 65536 records can be found in an Excel file.
Don't worry: I once exported this, but an exception may occur when there is a Chinese field (PL/SQL dev5)

I used a, OK, and the time was too short. I didn't try anything else.

Http://www.itpub.net/101803.html this post said:

You may use the following methods:
1. Save it as a TXT file and use SQL * Loader
2. Save the TXT file and use a third-party tool (such as Pb, Delphi, Toad, PL/SQL Dev) to import it.

SQL * loader is difficult to use. It is difficult to install a third-party tool for small demands.
View my methods.

For example, the file contains the ID and name columns (column A and column B respectively), and the table to be imported is person (person_id, person_name)

In the rightmost column of the sheet in an Excel file, add a column and write the following content to the cell corresponding to the data in the first row:

= "Insert into person (person_id, person_name) values ('" & A1 & "', '" & B1 &"');"

Copy this row to the columns corresponding to all data rows.

Copy the content of this column and save it as a script that can be run directly under SQL * Plus.

Method 1? Date type? Bit not applicable

A. Import the Excel file to acess and open the table for data import.

Open PLSQL Dev, open an SQL window, and execute
Select * from [Tab] for update

Copy & Paste from acess in one column.

B. Excel> copy> PL/SQL developer> Paste

In actual operation, we found that the use of copy/paste often causes the problem of Mo Yun. Especially when you copy data to Oracle through access (ODBC.

Execl --> Save As *. CSV (comma delimited). Then load it directly.

CTL file example
------------
Load data
Infile 'e: \ aa.csv'
Truncate
Into Table AA
Fields terminated ','
(,
B)

C. Map the Oracle table in access and insert it directly. It is very convenient, and no data error is found.
There was a very troublesome long raw problem, and this solution was also used.

D. use SQL * XL to directly import the Excel file to Oracle, which is very convenient. I have been using SQL * XL.

E,The common method is to copy data from Excel to ultraedit, and add insert into... to the column editing function.

F,With PLSQL developer, it's easy to use, just copy it.

In PL/SQL developer, select multiple field names for the table, and then click paste.

G. Data pipelines with ODBC and Pb can transmit data between different databases (texts ).

H. ODBC + Pb

Use different ODBC driversProgramConnected database
Then, start the data pipeline in the Pb environment and use the data pipeline to transmit data

I. Convert. It is convenient to use the Delphi Database Desktop to import the DBF file to Oracle. It also supports importing null values.