Database Software

Hi there.

I am trying to use the SQL Loader to read a tab separated file. This filewas previously read into a MySQL-Database, so I pretty much just copied the existing code and changed it to match the SQL Loader. The problem is - it seems to handle my tab-separated file wrong. I have no idea why, I'll just put an example here and what's the problem :

<h2>loader.ctl</h2>
-----
OPTIONS (SKIP=1)
LOAD DATA
INFILE 'V:\bla\ECPs.txt' "str '\r\n'"
APPEND
INTO TABLE TEST.ECPS
FIELDS
TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '"'
(MODULE,FREQUENCY,PROCESSPD,EQUIPMENT,METROLOGYTYPE,PARAMETERDESCRIPTION,PRODUCT,METROLOGYPD,PARAMETER,RECIPENAME,LSL,TARGET,USL,Unit, SPECAggregation,ControlMethod,CHARTID,bla FILLER,ErrorAction,ReactionPlan,bla2 FILLER,SpecialCharacteristic,Criticality,Category,Owner,Relevant,Comment_,bla3 FILLER,bla4 FILLER,bla5 FILLER,bla6 FILLER)
------

<h2>One failing line</h2> (I hope the tabulators are posted correctly - had to use pastebin because the forum cuts them - please look at the raw paste data, the linebreak displayed there is not one.)
------
http://pastebin.com/QnHJ7m2V (There are 3 trailing tabulators without values followed cut of by pastebin - but as the problem occures much earlier this doesn't matter)
------



<h2>The error occured : </h2>
------
Record 1: Rejected - Error on table TEST.ECPS, column SPECIALCHARACTERISTIC.
Column not found before end of logical record (use TRAILING NULLCOLS)
------


I really don't understand what the problem here is - I counted the tabulators more than once and it seems correct to me. If it would be an error describing a problem with having all values before the line ended it would give a hint that there is a problem with the empty values - but it seems to find too many \t and so reaching the end of line too early - what is really strange behaviour. Am I missing anything?

/edit : oops, it's friday, it has to do with the empty values - it seems to ignore 2 following tabs if there is nothing written between them - is there an option that inserts NULL in this case?

Thanks & best regards,

Compu

Edited by: 885634 on 16.09.2011 06:14

Edited by: 885634 on 16.09.2011 07:40