Large volume data import database, DBF import oracle,csv Import Oracle

Excel 07-2003 A worksheet can have up to 65536, rows can have up to 256 columns ,Excel 2007 and later versions, A sheet can have up to 1048576 rows and 16384 columns .

A small batch of data processing:

Method One: You can open the DBF or CSV file directly with Excel, and the database table for update directly copies the column values you want in the Excel table directly to the corresponding fields in PL/SQL.

Method Two: If the dbf file can be converted to a CSV file using the Dbfplus tool, open the tools > Text Importer in PL/SQL, select the table under "Data to Oracle" to import which user, click on the top left corner ' Open ' You have prepared the CSV file, this action is generally repeated again, the left is the CSV ' field ', the right side is the Import table field and data type, select the corresponding field to import, click ' Import ' on it. (This method can be used for both small-batch data and large-volume data, refer to http://blog.163.com/sunhaigang200699%40126/blog/static/121592632010811267359/)

Second, Excel maximum row data:

I used the above method two processing about 4 million of the data, there have been many times the phenomenon of death, took a day to solve. Here's how:

1. Prepare the compare.csv, put it under E:\Data\, and create a new Compare.ctl control file, compare. log file , compare. Bad is not a good line compare. Dis is a discarded row .

2. Focus: Edit the Compare.ctl file

OPTIONS (bindsize=8388608,readsize=8388608,errors=-1,rows=50000)

LOAD DATA

INFILE ' E:\Data\compare.csv '

Badfile E:\Data\compare.bad '

Discardfile ' E:\Data\compare.dis '

APPEND

Into TABLE Cc_compare

Fields TERMINATED by ', '

(

COM01,

COM02,

COM03,

COM04,

COM05,

Com06,

Com07,

COM08)

Note that the Cc_compare table in the database corresponds to the Compare.csv column, and if the COM08 column has a value that is empty, the entire row of data is not allowed, and a column that does not have a new value and is not NULL can be added.

(Note:rows controls every 500000 rows of commits, and errors controls how many rows will stop importing if an error occurs,-1 is not stopped regardless of how many rows are wrong )

3. Open cmd to execute the command.

C:\>sqlldr Username/[email protected] Control=e:/data/compare.ctl Log=e:/data/compare.log

Ok! Success! Look at your e:\Data\!

I'm using all of these methods, no matter how big the data can be.

Large volume data import database, DBF import oracle,csv Import Oracle