AD

SQLLoader Use Summary (2)

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
标签: apple, banana, tiger, double quotes, address position, truncate, pear, position statement, load data infile
分类: Database
时间: 2011-03-22

相关文章

  1. SQLLoader Use Summary (3)

    When using SQLLoader, may encounter multiple data files, or data file format is not standardized conditions, t ...
  2. sql * Loader to use summary

    Recent sqlLoader import data, has just begun the first study to the contents of the summary: 1 First of all, I ...
  3. JUnit Unit Testing Summary

    Read some of the unit test article, from the article in the interception of a number of information, which cou ...
  4. Ruby Operators Summary

    Personal sense of "Ruby Programming Language" is indeed a good book, it will have bits and pieces of ...
  5. Abroad in 2008 the best Web design / development techniques. Script and resource summary

    Today is the last day of 2008, Bin Go for the U.S. offer this year's final feast, this article will summarize ...
  6. Summary of design

    Abstract: This article is a summary of the design at the practice and study some of the experiences with the s ...
  7. JavaScript access to HTML DOM node element method summary

    At Web application development in particular Web2.0 often to access a page element, then update the elements o ...
  8. javascript access to the html dom element node Ways summary

    At Web application development in particular Web2.0 often to access a page element, then update the elements o ...
  9. 2008 Best Web Design / front-end development skills. Script and resource summary

    Tools & Web Application 13 may make you say "Thank You" the essential open-source application 14 ...
  10. Mantis maintenance project summary

    Maintaining the company's recent Mantis, done several times before adjustment, are different, this project obv ...
  11. A software engineer summary of 6 years

    "And upon graduation a year" to see groups of students to leave the ivory tower life and embark on t ...
  12. Summary of the official API

    When our development is often used to investigate how different a way, what interface, this time want to query ...
  13. struts + spring + hibernate summary

    struts + spring + hibernate summary 1. Myeclipse in order to load struts, spring, hibernate 2. Add filter Add ...
  14. Summary of TDD design ideas

    1. Whether or not it when a demand for a summary of the design to be prepared, if prepared a summary of the de ...
  15. Summary of Java articles Design Pattern

    Summary of Java articles Design Pattern
  16. jQuery grammar summary and Notes

    1, Introduction 1.1, an overview of WEB2.0 and ajax with the idea at the rapid development of Internet communi ...
  17. Overview Summary of the design of how to do that - the design of structured methods and object-oriented design methods

    Overview Summary of the design of how to do that - the design of structured methods and object-oriented design ...
  18. On the use of hibernate from sqlserver database to read out the image type data processing summary

    A project has recently encountered from sqlserver database using hibernate to read out the image of the type o ...
  19. Summary of learning J2EE

    Summary of learning J2EE J2EE learning for some time, today javaEye sum up, when we look back to learn some th ...