Ok, so, yesterday I had to run an import into Oracle tables with sqlldr, this is what I learned.
Try to use a tab as field separator
The pipe | is fairly good, but there are some fields that can contain that character. The tab character (\t) is much less frequent.
Double quotes text delimiter
If you are nearly sure that the fields does not contain the field separator character, avoid the double quotes. There may be some problems if there are double quotes in the fields, and there always are.
The data extracted can contain special characters. To avoid issues follow this checklist:
execute this on Oracle:
SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';
check the character set of the file with this linux command:
file -bi [filename]
correct the character set if needed:
iconv -f ascii -t utf8 [filename] > [newfilename]
Or
recode UTF-8 [filename]
Check and change your NLS_LANG settings in Linux:
echo $NLS_LANG
NLS_LANG=[selected_encoding]
export NLS_LANG=[selected_encoding]
http://support.etouch.net/cm/wiki/?id=43321
http://mindspill.net/computing/linux-notes/determine-and-change-file-character-encoding/
https://community.oracle.com/thread/650821
