Shulou(Shulou.com)05/31 Report--

How to load data in sqlldr, aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

1) Save the excel data as an t.txt file, note that the file is suffixed with .txt 1 jhchen 11ap07 2005 20:04:00 2005-11-7 20:04 2 jhchen 11qqr 2005 20:04:00 2005-11-7 20:04 3 jhchen 11Charpy 2005 20:04:00 2005-11-7 20:04

2) SQL > create table t_load (id number, name varchar2 (10), dat1 date, dat2 date, dat3 date); Table created.

3) the control file t.ctl is as follows: load data infile 't.txt' badfile 't.bad' append into table t_load fields terminated by Xroom09' trailing nullcols (id, name, dat1 date "mm/dd/yyyy hh34:mi:ss", dat2 date "yyyy-mm-dd hh34:mi:ss") where Xroom09' is the tab, TAB key, and trailing nullcols means that the field of the table is allowed to be empty if there is no corresponding value.

4) C:\ Documents and Settings\ cjh > sqlldr userid=jhchen/oracle control=t.ctl SQL*Loader: Release 9.2.0.6.0-Production on Monday November 7 20:20:00 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Submit point reached, logical record count 3

5) SQL > select * from t_load ID NAME DAT1 DAT2 DAT3-1 jhchen 07-NOV-05 07-NOV-05 2 jhchen 07-NOV-05 07-NOV-05 3 jhchen 07-NOV-05 07-NOV-05 sqlldr userid=lgone/tiger control=a.ctl LOAD DATA INFILE 't.dat' / / File to import / / INFILE 'tt.date' / / Import multiple files / / INFILE * / / the content to be imported is right after the following BEGINDATA in the control file is the imported content INTO TABLE table_name / / specify the loading table BADFILE'c:\ bad.txt' / / specify the bad file address * here are 4 ways to load the table APPEND / / the original table has Add the data to the end / / INSERT / / load the empty table if the original table has data sqlloader will stop the default value / / REPLACE / / the original data will be deleted / / TRUNCATE / / the same as replace will delete the existing data with the truncate statement * the specified TERMINATED can also be at the beginning of the table. In the internal fields section of the table, FIELDS TERMINATED BY' 'OPTIONALLY ENCLOSED BY' "/ / load this data: 10 lg LG,"lg", "lg,lg" / / in the table result: 10 lg "lg" lg Lg / / TERMINATED BY X '09' / / load this data in hexadecimal format' 09': 10 lg lg TRAILING NULLCOLS * the fields of the table are allowed to be empty if there is no corresponding value * below are the fields of the table (col_1, col_2) Col_filler FILLER / / FILLER keywords the values of this column will not be loaded / / such as: lg,lg,not result lg lg) / / when FIELDS TERMINATED BY','(/ / col_1 [interger external] TERMINATED BY',', / col_2 [date "dd-mon-yyy"] TERMINATED BY', / / col_3 [char] TERMINATED BY'is not declared 'OPTIONALLY ENCLOSED BY 'lg' / /) / / when FIELDS TERMINATED BY is not declared', 'tell the field to load data / / (/ / col_1 position (1:2), / / col_2 position (3:10), / / col_3 position (*: 16), / / the start of this field is at the end of the previous field / / col_4 position (1:16) / / col_5 position (3:10) char (8) / / specify the type of field / /) BEGINDATA / / corresponding to the beginning of the INFILE * the content to be imported is in the control file 10 DEPTNO DNAME what 20 LG show = / Note there is no space before the number after begindata 1 * normal load LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "(DEPTNO, DNAME) LOC) BEGINDATA 10 sales, "USA" 20 accounting, "Virginia,USA" 30 FIELDS TERMINATED BY WHITESPACE Consultingjiggle 40 FinanceFinanceJournal 50, "Finance", "", Virginia / / loc column will be empty 60, "Finance", Virginia / / loc column will be empty 2 * FIELDS TERMINATED BY WHITESPACE and FIELDS TERMINATED BY xchang09' case LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY WHITESPACE-- FIELDS TERMINATED BY xpen09' (DEPTNO, DNAME LOC) BEGINDATA 10 Sales Virginia 3 * specifies which column LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'(DEPTNO, FILLER_1 FILLER, / / the following" Something Not To Be Loaded "will not be loaded DNAME, LOC) BEGINDATA 20 female something Not To Be Loaded,Accounting," Virginia,USA "4 * position column child LOAD DATA INFILE * INTO TABLE DEPT REPLACE (DEPTNO position (1:2), DNAME position (*: 16) / / the starting position of this field is at the end of the previous field LOC position (*: 29), ENTIRE_LINE position (1:29) BEGINDATA 10Accounting Virginia,USA 5 * use a function date expression TRAILING NULLCOLS use LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY', 'TRAILING NULLCOLS / / in fact the following ENTIRE_LINE does not directly correspond to the column value of / / in the data after BEGINDATA if the first line is changed to 10 Sales,Virginia,1/5/2000, does not need TRAILING NULLCOLS (DEPTNO, DNAME "upper (: dname)", / / use the function LOC "upper (: loc)", LAST_UPDATED date 'dd/mm/yyyy', / / one way to express the date is' dd-mon-yyyy' et al. ENTIRE_LINE ": deptno | |: dname | |: loc |: last_updated") BEGINDATA 10 Magnum Salesome Virginia Virginia Magnum 2121 6way 1999 30 Art ConsultingVirginia Create or replace function my_to_date (p_string in varchar2) time problem solved by using custom functions / / return date as type fmtArray is table of varchar2 (25) L_fmts fmtArray: = fmtArray ('dd-mon-yyyy',' dd-month-yyyy', 'dd/mm/yyyy',' dd/mm/yyyy hh34:mi:ss'); l_return date; begin for i in 1. L_fmts.count loop begin l_return: = to_date (p_string, l_fmts (I)); exception when others then null; end; EXIT when l_return is not null; end loop; if (l_return is null) then l_return: = new_time (to_date ('01011970mm mmyyyyyyy`)) + 1 GMT', 60 p_string * p_string,' GMT', 'EST'); end if; return lump; end / LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY', 'TRAILING NULLCOLS (DEPTNO, DNAME "upper (: dname)", LOC "upper (: loc)", LAST_UPDATED "my_to_date (: last_updated)" / / use custom functions) BEGINDATA 10pr. Virginia,Not a date 7 * merge multiple lines into one line LOAD DATA INFILE * concatenate 3 / / use the keyword concatenate to treat several lines of records as one line INTO TABLE DEPT replace FIELDS TERMINATED BY','(DEPTNO, DNAME "upper (: dname)", LOC "upper (: loc)", LAST_UPDATED date 'dd/mm/yyyy') BEGINDATA 10 sales, / / in fact, these three lines are regarded as a line of 10 pencils, Salesphilosophy, Virginia, and 52000 Virginia. You can also tell sqlldr to find a comma at the end of each line and append the next line to the previous line LOAD DATA INFILE * continueif this (1:1) ='-'/ / to see if there is a concatenation character at the beginning of each line. 1:1 means that there is continueif next but continueif list is ideal INTO TABLE DEPT replace FIELDS TERMINATED BY','(DEPTNO, DNAME "upper (: dname)", LOC "upper (: loc)", LAST_UPDATED date 'dd/mm/yyyy') BEGINDATA / / but it doesn't seem to work like the one on the right-10 Magi Salesjiga,-10 meme Salesjiga, 1pm 52000 1x52000-40 The line number load data infile * into table t replace (seqno RECNUM / / load line number text Position (1into table t replace 1024)) BEGINDATA fsdfasj / / automatically assigns a line number to the seqno field loaded in table t this behavior 1 fasdjfasdfl / / this behavior 2... 9 * load data with newline characters meaning: unix and windows are different\\ n & / n

Use a non-newline character LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY', 'TRAILING NULLCOLS (DEPTNO, DNAME "upper (: dname)", LOC "upper (: loc)", LAST_UPDATED "my_to_date (: last_updated)", COMMENTS "replace (: comments,'\ ncorrectdchr (10))" / / replace to help convert the newline character) BEGINDATA 10 Magi Sales.Virginia 01Liaprilek 2001 this is the Sales\ nOffice in Virginia 20, Accounting.Virginia 13 is the Consulting is the Consulting 2001 this is the Consulting\ nOffice in Virginia 30 is the Consulting is the Consulting\ nOffice in Virginia 30 reference is the Consulting nOffice in Virginia 30 reference is the Finance is the Finance nOffice in Virginia

Use the fix properties LOAD DATA INFILE demo17.dat "fix 101" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY', 'TRAILING NULLCOLS (DEPTNO, DNAME" upper (: dname) ", LOC" upper (: loc) ", LAST_UPDATED" my_to_date (: last_updated) ", COMMENTS) demo17.dat 10, this is the Sales Office in Virginia 20 AccountingMagnum Virginia Virginia PersonalAccountingMagne2001, this is the Sales Office in Virginia 20, AccountingMagneVirginiaPersonally04Lever 2001, this is the Accounting Office in Virginia 30, ConsultingMagi 14, 04pm, 2001. This is the Consulting Office in Virginia 40 fix FinanceVirginia upper 987268297 this method that loads newline characters into the database does not require a different format of data: LOAD DATA INFILE demo18.dat "Finance101" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'TRAILING NULLCOLS (DEPTNO, DNAME" upper (: dname) ", LOC" upper (: loc) ", LAST_UPDATED" my_to_date (: last_updated) " COMMENTS) demo18.dat 10 salesman Virginia dagger 01 Zhenaprilashi 2001, "This is the Sales Office in Virginia" 20pr AccountingGrampr 13x04pr 2001, "This is the Accounting Office in Virginia" 30pr Virginia14pr 04pr 2001 12:02:02, "This is the Consulting Office in Virginia" 40Precinct Virginia 987268297, "This is the Finance Office in Virginia"

Use the var attribute LOAD DATA INFILE demo19.dat "var 3" / / 3 to tell the first 3 bytes of each record that the length of the record is as long as 071 of the first record, which means that the record has 71 bytes of INTO TABLE DEPT REPLACE FIELDS TERMINATED BY', 'TRAILING NULLCOLS (DEPTNO, DNAME "upper (: dname)", LOC "upper (: loc)", LAST_UPDATED "my_to_date (: last_updated)", COMMENTS) demo19.dat 07110 This is the Sales Office in Virginia 07820,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia 08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia 07140,Finance,Virginia,987268297,This is the Finance Office in Virginia

Use the str attribute / / the most flexible one to define a new line Terminator win newline: chr (13) | | chr (10) the record in this column is a select utl_raw.cast_to_raw ending with a |\ r\ n ('|'| | chr (13) | | chr (10)) from dual Results 7C0D0A LOAD DATA INFILE demo20.dat "str Xanti7C0D0A'" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY', 'TRAILING NULLCOLS (DEPTNO, DNAME "upper (: dname)", LOC "upper (: loc)", LAST_UPDATED "my_to_date (: last_updated)", COMMENTS) demo20.dat 10pr. This is the Consulting Office in Virginia | 40 nullif FinanceVirginia 987268297 Magi this is the Finance Office in Virginia | = data like this uses the 10-jan-200002350Flipper seemed unusually hungry today clause. 10510-jan-200009945Spread over three meals. Id position (1:3) nullif / / here can be blanks or some other expression / / the 1 in the first row of another column will be null LOAD DATA INFILE * INTO TABLE T REPLACE (n position (1:2) integer external nullif nasty expressions 1, v position (3:8)) BEGINDATA 1 10 20lg in the database.

This is the answer to the question about how to load data in sqlldr. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.