Any Ideas

[nabeel]

I want to create a control file to upload data in smp.dat file
, the sample of dat file is below:-

1;Khalid Naim Ali Yousef A.;2300;03-05-1951

2;Santhosh Kumar Beaspal;2500;26-08-1943

3;Fakhrudin Rangwala Ohd. Hu;850;27-11-1960

4;Walid Ohd Abdulfateh Nazza;850;08-01-1967

5;Nabil Ali Asad Asfour;1450;01-01-1957

6;Tariq Ali Farhat;950;03-09-1956

7;Mohd. Suneiti;900;12-02-1943

8;Jacob K. Mathew Thomas;250;12-11-1974

9;Hidalgo R. Sotero;200;08-08-1973

10;Mohd. Shaique A;300;04-07-1973

11;Sanjoy Marik. J;350;08-11-1975

12;Devendra D.Mayekar;350;14-06-1976

13;AL Refaey Amin;700;28-08-1949

14;Ali Ben Amira Jazire;325;19-09-1969

15;Moh'D Ammar Ayesh;300;01-11-1966

16;Wesam M.I.Itieh;475;24-12-1966

17;Husam K. Abdulnoor;825;01-09-1961

18;Xrumainyagom Makesh;450;19-09-1970

19;Bashir Ahmed Ali;175;18-02-1967

20;Yousuf Elgendy Labib Elgen;450;23-12-1961

The problem is that i have a blank line after each record! How could i eliminate the blank line and just insert the line with the record, the control file i create is :-

LOAD DATA
INFILE sant.csv
INTO TABLE TT_EMP
FIELDS TERMINATED BY ';'
(QEMP_ID, QEMP_EXTRA, QEMP_NAME, QEMP_DOB)

I think that i should add TRAILING NULLCOLS or something like that



Please help!

[jmodic]

I suppose your QEMP_ID column is defined with NOT NULL constraint. In this case you simply set your ERRORS parameter to a very high value (the default is 50, set it to at least the number of blank lines you have in your file. This way Oracle will simply put all blank lines in the REJECTED file and load only the nonblank lines into your table.

Or, you can use WHEN clause in your controlfile, instructing the SQL*Loader to skip all records that has CR/LF code at the beginning of the line.

LOAD DATA
INFILE sant.csv
INTO TABLE TT_EMP
WHEN (1) != CHR(10)
FIELDS TERMINATED BY ';'
(QEMP_ID, QEMP_EXTRA, QEMP_NAME, QEMP_DOB)

Replace CHR(10) with the appropriate character you have as the first (nonprintable) charaacter in your blank line.

[nabeel]

Replace CHR(10) with the appropriate character you have as the first (nonprintable) charaacter in your blank line

What will be the nonprintable chars??

[jmodic]

Originally posted by nabeel
What will be the nonprintable chars??

Open the file with a hex editor and find for yourself!

CHR(10) or CHR(13) are good canditates, I belive...

[nabeel]

Thanks alot dude, I tried the ERRORS parameter after increassin it to the value max to my recors and it worked!