#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    SQL Loader: Null columns when using terminated and optionally enclosed delimiters


    I regularly upload various tab-delimited text data files with SQL Loader. The control files always specify TERMINATED BY X'09'. Certain columns in those data files may be null for some rows, i.e. there is no character between two subsequent tabs. It always works like a clock.

    Now, I have run into a specific case where I have to strip a data column of double quotes that may or may not enclose the actual data (side effect of a text export from Excel).

    I tried simply adding OPTIONALLY ENCLOSED BY '"' behind the terminated delimiter and it did work for the file and column in question. Still, with this new option, files with null values are no longer decoded correctly. The loader seems to simply skip those values, which provokes column shifts and results in either a corrupt load, or a load failure.

    For now, I've got a workaround dropping the new option and executing an SQL script eliminating double quotes directly in the database, but that obviously cannot last.

    Any ideas?
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Yep I've used different combinations in the "access parameters" section for the fields 'terminated by', 'enclosed by' values, can you post your control file and a few lines of the data your having a problem with.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by spacebar208
    Yep I've used different combinations in the "access parameters" section for the fields 'terminated by', 'enclosed by' values, can you post your control file and a few lines of the data your having a problem with.
    Initial control file goes like this (I had to tamper with it a bit to paste here) :

    Load data
    into table FORMATING_OPTIONS
    append
    fields terminated by X'09'
    trailing nullcols
    ( DTYPE,
    ID,
    VERSION,
    LABEL,
    VALUE
    )


    The data file resembles the following pattern:

    A 10 0 MyLabel 1
    A 20 0 5
    B 30 0 MyLabel "2,5"
    B 40 0 0

    This works perfectly but I'm unhappy with those double quotes.

    But if I add the optionally enclosed delimiter, the quotes are dropped all right, but the lines without the label fail.
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    If I understood you correctly, This test worked for me:
    Code:
    create table myschema.test ( 
      dtype         	varchar2(  1   ),
      id             number(   10,0 ),
      version        number(   10,0 ),
      label         	varchar2( 30   ),
      value         	varchar2( 30   ) )
    
    
    > cat /tmp/test.txt
    A	10	0	MyLabel	1
    A	20	0	5
    B	30	0	MyLabel	"2,5"
    B	40	0	0
    
    
    load data
    infile       "/tmp/test.txt" "str '\n'"
    badfile      "/tmp/test.bad"
    discardfile  "/tmp/test.dis"
    append
    into table myschema.test
    fields terminated by X'09'
    optionally enclosed by '"'
    trailing nullcols
    ( dtype, id, version, label, value )
    
    select * from myschema.test
    dtype id version label        value
    A     10       0 MyLabel      1
    A     20       0 5            (null)
    B     30       0 MyLabel      2,5
    B     40       0 0            (null)
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2014
    Posts
    2
    Rep Power
    0
    Originally Posted by spacebar208
    If I understood you correctly, This test worked for me:
    Code:
    create table myschema.test ( 
      dtype         	varchar2(  1   ),
      id             number(   10,0 ),
      version        number(   10,0 ),
      label         	varchar2( 30   ),
      value         	varchar2( 30   ) )
    
    
    > cat /tmp/test.txt
    A	10	0	MyLabel	1
    A	20	0	5
    B	30	0	MyLabel	"2,5"
    B	40	0	0
    
    
    load data
    infile       "/tmp/test.txt" "str '\n'"
    badfile      "/tmp/test.bad"
    discardfile  "/tmp/test.dis"
    append
    into table myschema.test
    fields terminated by X'09'
    optionally enclosed by '"'
    trailing nullcols
    ( dtype, id, version, label, value )
    
    select * from myschema.test
    dtype id version label        value
    A     10       0 MyLabel      1
    A     20       0 5            (null)
    B     30       0 MyLabel      2,5
    B     40       0 0            (null)
    Hi, I came across this issue as well. Was the OP happy with the above? Just curious, as I had the same issue - my 2 differences to the above was addition of the following lines:
    CHARACTERSET WE8MSWIN1252
    'NULLIF COLUMN1 = BLANKS' in the columns definition.

    Removing these made no difference, tab delimiters next to each other were treated as one, leading to bad column import. If I removed 'optionally enclosed by '"'', it was fine again.

    My sanitised ctl file:
    LOAD DATA

    INFILE 'filename'
    PRESERVE BLANKS
    INTO TABLE yyyy
    FIELDS TERMINATED BY '\t'
    OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (COLUMN1 CHAR(4000) NULLIF COLUMN1 = BLANKS, COLUMN2 CHAR(4000) NULLIF COLUMNS2 = BLANKS,...)

    IS there anything in here that wil upset sqlldr?

    cheers,
    Slaybells.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2014
    Posts
    2
    Rep Power
    0
    Note that if I change the delimiter to a semi-colon, it works fine.
  12. #7
  13. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    853
    Rep Power
    387

    Talking


    Originally Posted by slaybells
    Note that if I change the delimiter to a semi-colon, it works fine.
    Well, that is your answer: use a delimiter other than "tab".

    For the past 10 years I myself have avoided using "tab" as delimiter in sql loader files to avoid this kind of issues. My preference is "pipe" ( | ), comma ( , ) or semi-colon ( ; ).
    Last edited by LKBrwn_DBA; March 14th, 2014 at 08:12 AM.

IMN logo majestic logo threadwatch logo seochat tools logo