Link to home
Start Free TrialLog in
Avatar of dblankman
dblankman

asked on

Bulk insert of records with mixed english and hebrew text into Oracle

I can do individual inserts into Oracle database with no problem, but when I attempt to use any bulk tools such as SQL Loader or import function in SQL Developer, the Hebrew gets garbled.  I have to import 3000+ records from each of 6  excel files. This is likely to be an annual process.

Two questions:
1. Most critical, how do I get the Hebrew to go in as Hebrew
2. Suggestions as to most appropriate tool to do the import

Sample DATA:
1      Aaronsohnia factorovskyi      ÐÔèÕàáÕàÙÙê äçØÕèÕÑáçÙ      Aaronsohnia factorovskyi          ÐÔèÕàáÕàÙê äçØèÕÑáçÙ      no      520140      existing      no      no





SQL LOADER control file

LOAD DATA
CHARACTERSET UTF8
    APPEND INTO TABLE reshut_speciies_list
    (
    id_concordance INTEGER EXTERNAL TERMINATED BY "," OPTIONALLY  ENCLOSED BY '"',
    frag_latin_number  INTEGER EXTERNAL TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    reshut_latin_name CHAR TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    reshut_hebrew CHAR TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    fragman_scientific_name CHAR TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    fragman_hebrew_name CHAR TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    matched_by_hand CHAR TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    reshut_species_code INTEGER EXTERNAL TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    new_to_reshut CHAR TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    merged CHAR TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    problematic CHAR TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
SOLUTION
Avatar of Sujith
Sujith
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dblankman
dblankman

ASKER

The oracle server is on a linux host. I was doing the sql*loader operation using sqlldr from a terminal session while logged onto the linux maching using VNC.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am interested to know what was the solution you used eventually.