Import large amounts of data into the database, dbf import oracle, csv import oracle, dbfcsv

Import large amounts of data into the database, dbf import oracle, csv import oracle, dbfcsv

In Excel 07-2003, a worksheet can have a maximum of 65536 rows and a maximum of 256 columns. In Excel 2007 and later versions, a worksheet can have a maximum of 1048576 rows and 16384 columns.

I. Small Batch Data Processing:

Method 1: You can use excel to directly open the dbf or csv file, database table for update, and directly copy the expected column values in the excel table to the corresponding fields in pl/SQL.

Method 2: If the dbf file can be converted to a csv file by using the dbfplus tool, open the tool in pl/SQL> text importer, in 'data to Oracle ', select the table under the user to be imported and click 'open' in the upper left corner. You have prepared the csv file. This operation should be repeated again, the left side is the csv 'field', and the right side is the field and Data Type of the import table. Select the corresponding field to be imported and click 'import. (Small batches of data and mass data can use this method, refer to the http://blog.163.com/sunhaigang200699%40126/blog/static/121592632010811267359)

2. Data that exceeds the largest excel row:

When I used method 2 to process about 4 million of the data, it took me one day to solve the problem. The method is as follows:

1.20.put compare.csv under e: \ Data \ and create a new compare under this path. ctl control file, compare. log File, compare. bad is a bad row (the format is incorrect and cannot be imported), compare. dis is the row discarded.

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 ','

(

Com01,

Com02,

Com03,

Com04,

Com05,

Com06,

Com07,

Com08)

Otherwise, the cc_comparetable in the data warehouse corresponds to the compare.csv column. In addition, if any value in the com08 column is empty, the entire data in the row will not be imported. You can skip this step or add a new value to a column that is not empty.

(Note: rows controls the commit of every 500000 rows, and errors controls the number of rows to be imported when an error occurs.-1 indicates that the import will not be stopped no matter how many rows are wrong)

3. Open cmd to execute the command.

C: \> sqlldr username/password @ oracle control = e:/Data/compare. ctl log = e:/Data/compare. log

 

OK! Successful! Let's take a look at your e: \ Data!

I am using these methods. These methods can be used no matter how big data is.