More SQL Loader basics - using STR



Another random SQL Loader demo - here i'm assuming that the input file is just one long string of numbers separated by commas - i just want to load this into a single column table so i can do 'stuff' with it.

So i create a single column table

SQL> create table demo(col1 number);

Table created.

Here is my input file

# cat input.csv
1,2,3,4,5,66,77,888,99991,2,3,4,5,66,77,888,99991,2,3,4,5,66,77,888,99991,2,3,4,5,66,77,888,etc etc

And here is my controlfile

LOAD DATA
INFILE "input.csv" "STR ','"
INTO TABLE DEMO
TRUNCATE
FIELDS TERMINATED by ','
TRAILING NULLCOLS
(COL1)

The 'trick' is the use of the STR command to tell sqlloader that comma is my end or record marker (as well as being the end of field marker)

Now run that in

sqlldr demo/demo control=loader.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Wed Dec 17 14:00:12 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 64
Commit point reached - logical record count 65

Table DEMO:
  65 Rows successfully loaded.

Check the log file:
  loader.log
for more information about the load.

And there we have it

SQL> select * from demo.demo;

      COL1
----------
         1
         2
         3
         4
         5
        66
        77
       888
     99991
         2
   etc

Kind of pointless but the demo of the STR feature is useful


Comments

  1. nakliyat firması olarak görev yapan bir firma

    ReplyDelete
  2. Türkiyenin heryerine imalat ve montajımız vardır...

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Thank you for this nice article , very useful
    bilgi bankası

    ReplyDelete
  5. Blog işi zor bir iştir ama başarmışsınız.

    ReplyDelete

Post a Comment