import data from file csv to Oracle using sql

Question

I'm using Oracle 10g , SQL Developer I want to know if there is any way to import data from CSV file , which the fields separated by ',' and the lines terminated by '\n' using sql query i tried this query

LOAD DATA INFILE 'C:/tmp.csv' INTO TABLE CSVTEST2 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 lines (ID,FIRSTNAME,LASTNAME,BIRTHDATE);

But it didn't work and i always get error message from sql Developer telling me Unknow command

Solution 1

You need to use sqlldr utility in order to load data. Firstly create a control file (ends with an extension of .ctl) as per your requirements like mentioned below.

load data
infile 'path_where_file_is_placed_followed_by_file_name'
into table table_name_where_you_want_to_insert_the_data_of_csv_file
fields terminated by ','  lines terminated by '\n' 
(
field1 datatype,
field2 datatype,
field3 datatype
)

Now execute sqlldr utility to load data as mentioned below.

sqlldr userid=database_username/password@instance_name control=path_where_control_file_is_placed_followed_by_control_file_name LOG=path_for_log_file BAD=path_for_bad_records Discard=path_for_discard_records

Solution 2

You should be using Oracle SQL Loader for that, not sqlplus or SQL Developer

Alternatively you can use external tables:

-- this command must be executed on the Oracle server machine, NOT on the client:
create directory ext_tab_dir as '/path/to/dir/where/you/will/put/your/csv/files';

CREATE TABLE emp_load (
    employee_number         CHAR(5), 
    employee_last_name      CHAR(20),
    employee_first_name     CHAR(15),
    employee_middle_name    CHAR(15)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_dir
    ACCESS PARAMETERS (RECORDS FIXED 62 
        FIELDS (
            employee_number CHAR(2),
            employee_dob CHAR(20),
            employee_last_name CHAR(18),
            employee_first_name CHAR(11),
            employee_middle_name CHAR(11)
        )
    )
    LOCATION ('tmp.csv'));

Solution 3

In Oracle SQL developer you can simply go to the list of tables on the left which should be under connections.

Then Double click the table > actions > import data

You can then use the import wizard with your desired settings. I'm not sure if the feature was present in Oracle SQL developer when you asked this question, but it is available now.

Solution 4

Example on windows 10 and Oracle 12c

if you have a text file with records of each table delimited by comma, you can do this:

Create a control file for each table, called table_name.ctl (C:\Users\user\Desktop\directory\table_name.ctl)

load data 
infile 'C:\Users\user\Desktop\directory\table_name.txt' 
append
into table table_name
fields terminated by ","
(id, field2,field3)

After, In windows you should open Cmd and load data in each table, and then load data remotely for example in a aws server.

sqlldr userid=USER@AWS_PDB1/password
control='C:\Users\user\Desktop\directory\table_name.ctl' log='C:\Users\user\Desktop\directory\table_name.log'

or

sqlldr control='C:\Users\user\Desktop\directory\table_name.ctl' log='C:\Users\user\Desktop\directory\table_name.log'
and then ask them the user and password

If you have the following error:“The program can’t start because oranfsodm12.dll is missing from your computer. Try reinstalling the program to fix this problem.”

it is because SQL * Loader is disabled and can not be used in the console windows, this is solved enabling the following steps (as http://www.dallasmarks.com/installing-two-oracle-12c-clients-on-one-server/):

1. Should go to the folder C:\oracle\client\user\product\12.1.0\client_1\BIN

2. Make a copy of oraodm12.dll file, calling the new file oranfsodm12.dll, and paste it in the same BIN folder.

3. Run the command again from cmd.

View more solutions

Comments

[Meher Jebali]

can you explain more about the use of SQL*Loader , i want it simply with query ? the command LOAD DATA INFILE doesn't work?

[MaxU - stop genocide of UA]

@Mahran, there is NO such sql command like "LOAD DATA ..." - it's an instruction for the SQL Loader tool. So it might be used only with SQL Loader. BUT you can use external tables as an alternative - i'll update my answer with an example

[Meher Jebali]

Thank you so much for the answer , i already solve my problem, i did the same as you write +1 for the right Answer