Using SQL LOADER in Oracle to import CSV file

Question

I m pretty new to databases and programming  I m not very good with the computer lingo so stick with me  I have a csv file that I m trying to load into my Oracle database  It contains account information such as name  telephone number  service dates etc  I ve installed Oracle 11g Release 2  This is what I ve done so far step by step    1  Ran SQL Loader  I created a new table with the columns that I needed  For example  create table Billing   TAP ID char 10   ACCT NUM char 10   MR ID char 10   HOUSE NUM char 10   STREET char 30   NAME char 50    2  It prompted me that the Table was created  Next I created a control file for the data in notepad which was located in the same directory as my Billing table and has a  ctl extension  GIS csv is the file im getting the data from and is also in the same directory and named it Billing ctl  which looked like so    load data infile GIS csv into table Billing fields terminated by      TAP ID  ACCT NUM  MR ID  HOUSE NUM  STREET  NAME    3  Run sqlldr from command line to use the control file  sqlldr myusername mypassword  Billing ctl   This is where I am stuck  Ive seen video tutorials of exactly what I m doing but I get this error   SQL Loader-522  lfiopn failed for file  Billing log    Any ideas on what I could be doing wrong here    Update  I just moved the files into a separate directory and I suppose I got past the previous error   By the way yes Billing ctl and GIS csv are in the same directory    But now I have another error    SQL Loader-350  Syntax error at line 1    Expecting keyword LOAD  found  SERV TAP ID    SERV TAP ID   ACCT NUMBER   MTR ID   SERV HOUSE   SERV STREET   SERV       I dont understand why its coming up with that error  My billing ctl has a load   LOAD data infile GIS csv into table Billing fields terminated by      TAP ID  ACCT NUM  MTR ID  SERV HOUSE  SERV STREET  SERV TOWN  BIL NAME  MTR DATE SET  BIL PHONE  MTR SIZE  BILL CYCLE  MTR RMT ID    Any thoughts

User · Answer

I hade a csv file named FAR T SNSA csv that i wanted to import in oracle database directly  For this i have done the following steps and it worked absolutely fine  Here are the steps that u vand follow out   HOW  TO IMPORT CSV FILE IN ORACLE DATABASE     Get a  csv format file that is to be imported in oracle database  Here this is named    FAR T SNSA csv    Create a table in sql with same column name as there were in  csv file  create table Billing   iocl id char 10   iocl consumer id char 10    Create a Control file that contains sql loder script  In notepad type the script as below and save this with  ctl extension  in selecting file type as All Types     Here control file is named as Billing  And the Script is as Follows   LOAD DATA INFILE  D FAR T SNSA csv  INSERT INTO TABLE Billing FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY     TRAILING NULLCOLS   iocl id  iocl consumer id    Now in Command prompt run command   Sqlldr UserId Password Control      ControlFileName     -------------------------------- Here ControlFileName is Billing

User · Answer

You need to designate the logfile name when calling the sql loader     sqlldr myusername mypassword control Billing ctl log Billing log   I was running into this problem when I was calling sql loader from inside python  The following article captures all the parameters you can designate when calling sql loader http   docs oracle com cd A97630 01 server 920 a96652 ch04 htm

User · Answer

LOAD DATA  INFILE  D  CertificationInputFile csv   INTO TABLE CERT EXCLUSION LIST  FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY        CERTIFICATIONNAME  CERTIFICATIONVERSION

User · Answer

-- Step 1  Create temp table      create table Billing   TAP ID char 10   ACCT NUM char 10     SELECT   FROM BILLING    -- Step 2  Create Control file   load data infile IN DATA txt into table Billing fields terminated by      TAP ID  ACCT NUM   -- Step 3  Create input data file  IN DATA txt file content  100 15678966  -- Step 4  Execute command from run      client bin sqlldr username db-sis  id password control  Billing ctl

User · Answer

If your text is  Joe said   Fred was here with his  Wife     This is saved in a CSV as    Joe said    Fred was here with his   Wife         Rule is double quotes go around the whole field  and double quotes are converted to two double quotes    So a simple Optionally Enclosed By clause is needed but not sufficient   CSVs are tough due to this rule   You can sometimes use a Replace clause in the loader for that field but depending on your data this may not be enough   Often pre-processing of a CSV is needed to load in Oracle   Or save it as an XLS and use Oracle SQL Developer app to import to the table - great for one-time work  not so good for scripting

User · Answer

Sqlldr wants to write a log file in the same directory where the control file is  But obviously it can t  It probably doesn t have the required permission   If you re on Linux or Unix  try to run the following command the same way you run sqldr   touch Billing log   It will show whether you have the permissions   Update  The proper command line is   sqlldr myusername mypassword control Billing ctl

User · Answer

Line 1  - maybe something about windows vs unix newlines   as i saw windows 7 mentioned above

User · Answer

Try this  load data infile  datafile location  into table schema tablename fields terminated by     optionally enclosed by      field1 field2 field3       In command prompt   sqlldr system databasename password control  control file location

[sql] Using SQL LOADER in Oracle to import CSV file

Examples related to sql

Examples related to csv

Examples related to database-design

Examples related to oracle11g

Examples related to database-connection