Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    47

    Unanswered: SQLLDR, is it possible to set "sub-delimiters"?

    All,

    I'm using SQLLDR to pull information in from a text file. (I can't change the format of the file, and for whatever reason, the data was not formatted as expected).

    For example ( a line from the datafile):

    123456|Admin|Holder,James,P|jph@example.com

    Ideally this would be:

    123456|Admin|Holder|James|P|jph@example.com

    So my question is, is there a way, with SQLLDR, to read the line with the pipe delimiters, and then, for the 3rd field, delimit that sub-data with comma's, and THEN push the data into the correct fields in the table?

    Thanks in advance,
    James

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    example controlfile line:
    PHP Code:
    FIELDS TERMINATED BY '|' optionally enclosed by ',' 
    it states right in the Oracle Utilities document:

    Parameter Description
    TERMINATED
    Data is read until the first occurrence of a delimiter.

    BY
    An optional word to increase readability.

    WHITESPACE
    Delimiter is any whitespace character including spaces, tabs, blanks, line feeds, form feeds, or carriage returns. (Only used with TERMINATED, not with ENCLOSED.)

    OPTIONALLY
    Data can be enclosed by the specified character. If SQL*Loader finds a first occurrence of the character, it reads the data value until it finds the second occurrence. If the data is not enclosed, the data is read as a terminated field. If you specify an optional enclosure, you must specify a TERMINATED BY clause (either locally in the field definition or globally in the FIELDS clause).

    ENCLOSED
    The data will be found between two delimiters.

    string
    The delimiter is a string.

    X'hexstr'
    The delimiter is a string that has the value specified by X'hexstr' in the character encoding scheme, such as X'1F' (equivalent to 31 decimal). "X"can be either lowercase or uppercase.

    AND
    Specifies a trailing enclosure delimiter that may be different from the initial enclosure delimiter. If AND is not present, then the initial and trailing delimiters are assumed to be the same.

    EOF
    Indicates that the entire file has been loaded into the LOB. This is valid only when data is loaded from a LOB file. Fields terminated by EOF cannot be enclosed.


    Here are some examples, with samples of the data they describe:

    TERMINATED BY ',' a data string,
    ENCLOSED BY '"' "a data string"
    TERMINATED BY ',' ENCLOSED BY '"' "a data string",
    ENCLOSED BY '(' AND ')' (a data string)

    Delimiter Marks in the Data
    Sometimes the punctuation mark that is a delimiter must also be included in the data. To make that possible, two adjacent delimiter characters are interpreted as a single occurrence of the character, and this character is included in the data. For example, this data:

    (The delimiters are left parentheses, (, and right parentheses, )).)


    with this field specification:

    ENCLOSED BY "(" AND ")"


    puts the following string into the database:

    The delimiters are left parentheses, (, and right parentheses, ).


    For this reason, problems can arise when adjacent fields use the same delimiters. For example, with the following specification:

    field1 TERMINATED BY "/"
    field2 ENCLOSED by "/"


    the following data will be interpreted properly:

    This is the first string/ /This is the second string/


    But if field1 and field2 were adjacent, then the results would be incorrect, because

    This is the first string//This is the second string/


    would be interpreted as a single character string with a "/" in the middle, and that string would belong to field1.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2006
    Posts
    47

    Thanks

    So if I understand the docs correctly, for the following file syntax:

    123456|Admin|Holder,James,P|jph@example.com

    My control file would look like:

    LOAD DATA
    APPEND
    INTO TABLE staff
    when staffid != ''
    FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
    (
    staffid,
    role,
    something FILLER CHAR TERMINATED BY ',',
    last_name,
    first_name,
    middle_name,
    email
    )

    Am I getting warm?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    try it out. that is the best way to see if it works
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    try this
    PHP Code:
    LOAD DATA
    APPEND
    INTO TABLE staff
    when staffid 
    != '' 
    FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
    (
    staffid,
    role,
    last_name CHAR TERMINATED BY ',',
    first_name CHAR TERMINATED BY ',',
    middle_name,
    email

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •