SQLLoader Use Summary (2)

2011-03-22  来源:本站原创  分类:Database  人气:80 

In the data file to import, it may encounter file special case (used in the experiment table or articles on the FRUITMARKET)

1. If you want to import the file contains double quotes

1,Apple,¥4.00,"A_3" 2,Pear,¥3.00,"B_2" 3,Grape,¥6.00,"C_5" 4,Banana,¥6.00,"D_2"

fruit2.dat

If you do not want to store these quotes in the database, it is necessary to join OPTIONALLY ENCLOSED BY '"' statement

Write control file

LOAD DATA
INFILE fruit2.dat
TRUNCATE INTO TABLE FRUITMARKET
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(FID,FNAME,PRICE,ADDRESS)

fruit2.ctl

Executive SQLLDR command SQLLDR SCOTT / tiger CONTROL = fruit2.ctl

Results:
SQLLoader Use Summary (2)

(Do not know how in 10g ¥ symbols can not be properly recognized, this problem later date, we can note the double quotes in the data file has been filtered out)

(2) import the data file is not obvious separator

1     Apple     ¥3.00      A_3
2     Pear      ¥2.00      B_2
3     Grape     ¥4.00      C_5
4     Banana    ¥6.00      D_2

fruit3.dat

Write control file

LOAD DATA
INFILE fruit3.dat
TRUNCATE INTO TABLE FRUITMARKET
(
 FID position(1),
 FNAME position(7:12),
 PRICE position(17:21),
 ADDRESS position(28:32)
)

fruit3.ctl

This position is a position to tell SQLLDR, where where is a field, that field is the beginning and end, even more troublesome is the number of the number of spaces

Results:

SQLLoader Use Summary (2)

You can also use the * in the position statement, the above substitution is equivalent to the control file

LOAD DATA
INFILE fruit3.dat
TRUNCATE INTO TABLE FRUITMARKET
(
 FID position(1),
 FNAME position(*+5:12),
 PRICE position(*+4:21),
 ADDRESS position(*+6:32)
)

* Represents a position on the field plus one end, such as the * second line represents the 1 +1 = 2, which is 2 +5:12

3. Or example, if you want to import data fruitmarket fruit table when the address did not know how to do fruit

1     Apple     $3.00
2     Pear      $2.00
3     Grape     $4.00
4     Banana    $6.00

Write control file

LOAD DATA
INFILE fruit4.dat
TRUNCATE INTO TABLE FRUITMARKET
(
 FID position(1),
 FNAME position(7:12),
 PRICE position(17:21),
 ADDRESS "NULL"
)

ADDRESS values ​​can now be temporarily set to NULL, in this position is to allow the value 0, but here will be strange ADDRESS 0

Results:

SQLLoader Use Summary (2)

4 If the data file in the case of redundant data

1     Apple     $3.00       zhangsan    A_3
2     Pear      $2.00       lisi                 B_2
3     Grape     $4.00       zhangsan    C_5
4     Banana    $6.00     lisi              D_2

(In the editor can not align the back of the character, but was formally introduced into the best time to align, otherwise it will increase the workload)

fruit5.dat

Write control file:

LOAD DATA
INFILE fruit5.dat
TRUNCATE INTO TABLE FRUITMARKET
(
   FID position(1),
   FNAME position(7:12),
   PRICE position(17:21),
   ADDRESS position(39:41)
)

So long as the length of the specified field can be a

Also to note that if there is a separator that is the case there is extra data

1,Apple,$3.00,zhangsan, A_3
2,Pear,$2.00,lisi,B_2
3,Grape,$4.00,zhangsan,C_5
4,Banana,$6.00,lisi,D_2

So we must modify the control file

LOAD DATA
INFILE fruit5.dat
TRUNCATE INTO TABLE FRUITMARKET
FIELDS TERMINATED BY ","
( FID,FNAME,PRICE,FILLER,ADDRESS ) 

With the FILLER keyword to filter out unwanted fields

相关文章
  • SQLLoader Use Summary (3) 2011-03-23

    When using SQLLoader, may encounter multiple data files, or data file format is not standardized conditions, the following we will discuss these solutions 1 encounter multiple data files If you encounter multiple data files at the same time you want

  • SQLLoader Use Summary (2) 2011-03-22

    In the data file to import, it may encounter file special case (used in the experiment table or articles on the FRUITMARKET) 1. If you want to import the file contains double quotes 1,Apple,¥4.00,"A_3" 2,Pear,¥3.00,"B_2" 3,Grape,¥6.00,

  • sql * Loader to use summary 2011-04-03

    Recent sqlLoader import data, has just begun the first study to the contents of the summary: 1 First of all, I sqlLoader is run in dos (or shell) in the. 2 Syntax: loader.ctl load data infile " File path / file name .txt.." -- File path can be d

  • JUnit Unit Testing Summary 2009-03-01

    Read some of the unit test article, from the article in the interception of a number of information, which could be considered in the learning process, a summary of it! See future! Unit testing code is not used to prove you are right, but in order to

  • Ruby Operators Summary 2009-03-17

    Personal sense of "Ruby Programming Language" is indeed a good book, it will have bits and pieces of the Ruby point up. Here, some of my experiences from their own point of Ruby language school operator to be a notebook-style summary. Welcome to

  • Abroad in 2008 the best Web design / development techniques. Script and resource summary 2009-03-20

    Today is the last day of 2008, Bin Go for the U.S. offer this year's final feast, this article will summarize U.S. foreign countries in 2008 some good front-end development-related tutorials, tips, scripts, examples and tools, resources, etc. Have so

  • Summary of design 2009-03-23

    Abstract: This article is a summary of the design at the practice and study some of the experiences with the study notes, I hope to share with you if there is inappropriate please correct me. Keyword: Summary of design, structural, OOD Body: At the d

  • JavaScript access to HTML DOM node element method summary 2009-03-30

    At Web application development in particular Web2.0 often to access a page element, then update the elements of style, content, etc.. How to obtain the elements to be updated, is the first problem to be solved. It is gratifying that the use of JavaSc

  • javascript access to the html dom element node Ways summary 2009-04-01

    At Web application development in particular Web2.0 often to access a page element, then update the elements of style, content, etc.. How to obtain the elements to be updated, is the first problem to be solved. It is gratifying that the use of JavaSc

  • 2008 Best Web Design / front-end development skills. Script and resource summary 2009-04-11

    Tools & Web Application 13 may make you say "Thank You" the essential open-source application 14 free tools that let you know why people give up access to your website 40 + CSS Generator 74 we may have forgotten the fit of the great web desi

  • Mantis maintenance project summary 2009-04-18

    Maintaining the company's recent Mantis, done several times before adjustment, are different, this project obviously nervous than ever. Projects, but in the small projects I learned a lot through it I can clearly feel an even bigger "under high press

  • A software engineer summary of 6 years 2009-05-15

    "And upon graduation a year" to see groups of students to leave the ivory tower life and embark on their respective roles; think himself once high-spirited, smug, Without our realizing it, sigh million ... ... in this article are his own 6-year

  • Summary of the official API 2009-05-18

    When our development is often used to investigate how different a way, what interface, this time want to query on how to use the API, this manual should be regarded as an essential tool bar Recommend the following summary of two focus on the API webs

  • struts + spring + hibernate summary 2009-05-25

    struts + spring + hibernate summary 1. Myeclipse in order to load struts, spring, hibernate 2. Add filter Add WEN-INF/web.xml <filter> <filter-name> encodingfilter </ filter-name> <filter-class> com.yourcompany.EncodingFilter </

  • Summary of TDD design ideas 2009-06-07

    1. Whether or not it when a demand for a summary of the design to be prepared, if prepared a summary of the design, a summary of the contents of the design? After discussion, a summary of the design is still necessary, set aside TDD mode, a summary o

  • Summary of Java articles Design Pattern 2009-06-11

    Summary of Java articles Design Pattern

  • jQuery grammar summary and Notes 2009-06-20

    1, Introduction 1.1, an overview of WEB2.0 and ajax with the idea at the rapid development of Internet communication, one after another there have been some excellent Js framework, there is one of the most famous Prototype, YUI, jQuery, mootools, Bin

  • Overview Summary of the design of how to do that - the design of structured methods and object-oriented design methods 2009-07-02

    Overview Summary of the design of how to do that - the design of structured methods and object-oriented design methods At 23:52 on February 12, 2008 Overview Summary of the design of how to do that - the design of structured methods and object-orient

  • On the use of hibernate from sqlserver database to read out the image type data processing summary 2009-07-18

    A project has recently encountered from sqlserver database using hibernate to read out the image of the type of data. In this summary, At the same time would also like to thank the help lovewhzlq and huangnetian. Summary: The procedure used in the pi

  • Summary of learning J2EE 2009-07-22

    Summary of learning J2EE J2EE learning for some time, today javaEye sum up, when we look back to learn some things themselves, while summing up the work must be under the hope of learning java from a friend there. Technology-related (and more time to