SQL*LDR issue - consecutive blank flds in tab-delimited file

[delayne]

I ran into a problem using SQL*LDR that I've never experienced before. It appears that when the tab-delimited input file has consecutive fields that are null, they are treated as one field rather than several fields.

Here is a sample from my input file:

00222988<TAB><TAB><TAB>999999999999<TAB>lcroy<TAB>12/19/07
00223077<TAB><TAB><TAB>999999999999<TAB>lcroy<TAB>12/19/07

(Tabs obviously denoted by <TAB>)

Here is the .CTL file specs ...

FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(FWTFINB_ASSET_BARCODE_NUM,
FWTFINB_MAKE,
FWTFINB_MODEL,
FWTFINB_SERIAL_NUM,
FWTFINB_USER_ID,
FWTFINB_LAST_INVENTORY_DATE DATE "MM/DD/YY hh24:mi" ,
FWTFINB_ACTIVITY_DATE SYSDATE
)

When I loaded the file, the "9999..." field loaded into the FWTFINB_MAKE column, instead of into FWTFINB_SERIAL_NUM. This threw off the loading of the remainder of the data as well (everything being off by 2 columns).

How can I get SQL LDR to load this file correctly?

 

[taupirho]

Try removing the optionally enclosed by bit


In order to understand recursion, you must first understand recursion.

 

[Dagon]

If you have no spaces or tabs in the actual data, try "delimited by whitespace".

 

[Dagon]

I mean "terminated by whitespace".

 

[DrD123]

Hi delayne,

Is that field always blank in your tab-delimited-input file?

If so, just remove that column from the CTL file, and all should go in since it's treating it as one long TAB.

Good Luck
DrD