This discussion is archived
1 2 3 5 Previous Next 70 Replies Latest reply: Nov 20, 2012 9:33 AM by 969952 RSS

Load data from excel to Oracle Tables

969952 Newbie
Currently Being Moderated
Hi All,

I want to create a table with payment year as sysdate ( to_char(sysdate,'yyyy')) and month as 'MM' by default ( like payment_month default <(syadate,'MM')>

I am trying to do it but failed. Please help me out and the other one is want to load the data from EXCEL to ORACLE table.

I used to load the load the data by using SQL * LOADER. can you please let me know is it possible to load the data without using SQL*Loader? please provide me the solution for this.

Thanks,
Ora.
  • 1. Re: Load data from excel to Oracle Tables
    sb92075 Guru
    Currently Being Moderated
    966949 wrote:
    Hi All,

    I want to create a table with payment year as sysdate ( to_char(sysdate,'yyyy')) and month as 'MM' by default ( like payment_month default <(syadate,'MM')>

    I am trying to do it but failed. Please help me out and the other one is want to load the data from EXCEL to ORACLE table.
    I tried to make my car go, but it failed.
    Tell me how to make my car go.
    I used to load the load the data by using SQL * LOADER. can you please let me know is it possible to load the data without using SQL*Loader? please provide me the solution for this.

    Thanks,
    Ora.
    Excel can connect directly to Oracle DB via ODBC & issue SQL.


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Load data from excel to Oracle Tables
    rp0428 Guru
    Currently Being Moderated
    >
    the other one is want to load the data from EXCEL to ORACLE table.
    >
    Just use sql developer - it can load data from excel files to Oracle. The main download page is
    http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
  • 3. Re: Load data from excel to Oracle Tables
    stratmo Newbie
    Currently Being Moderated
    Hi,

    SQL*Loader or Data-Pump are normally used for a mass-load. For a better convenience you may try external tables. The technique behind the curtain is the same but the structure once set up can be handled mostly within the database itself.
    Hopefully the following example helps:
    -- external directories for your comfort
    -- additionally you may need directories for the bad- and log-files (here all files are written into the same directory)
    -- !!! AS USER SYSDBA !!!
    drop directory media_dir1;
    drop directory media_dir2;
    create directory media_dir1 as 'C:\Users\stratmo\Documents\work\work\repos\DBA\DB_DIR';
    create directory media_dir2 as 'C:\Users\stratmo\Documents\work\work\repos\DBA\DB_DIR_2';
    
    -- according grants
    GRANT READ ON DIRECTORY media_dir1 TO otn; 
    GRANT WRITE ON DIRECTORY media_dir1 TO otn; 
    
    GRANT READ ON DIRECTORY media_dir2 TO otn; 
    GRANT WRITE ON DIRECTORY media_dir2 TO otn; 
    
    
    -- !!! AS USER OTN !!!
    -- create external table
    drop table emp_ext;
    create table emp_ext (
         EMPNO    NUMBER(4),
         ENAME    VARCHAR2(10),
         JOB      VARCHAR2(9),
         MGR      NUMBER(4),
         HIREDATE DATE,
         SAL      NUMBER(7,2),
         COMM     NUMBER(7,2),
         DEPTNO   NUMBER(2)
    )
    Organization external
    (
         type oracle_loader
         default directory media_dir1 -- the FIRST directory
         access parameters (records delimited by newline
         badfile media_dir1:'empxt%a_%p.bad' 
         logfile media_dir1:'empxt%a_%p.log' 
         fields terminated by ',')
    location ('test.csv'))
    reject limit 1000;
    
    
    drop view emp_ext_imported_vw;
    
    -- a help-view
    create or replace view emp_ext_imported_vw
    as
    select      ext.*
                   -- the following virtual columns, couldn' be set up in the view
                   -- limitation of external tables
                   , to_char(sysdate,'yyyy') as payment_year
                   , to_char(sysdate,'MM') as payment_month
    from emp_ext ext;
    
    -- import data into database-table
    -- create table in an easy (create table ... as select) way first 
    -- at the first import
    drop table emp_ext_imported;
    create table emp_ext_imported
    as 
    select a.* 
    from emp_ext_imported_vw a;
    
    -- create constraints
    Alter table emp_ext_imported 
    add constraint emp_ext_imported_pk
    primary key (empno);
    
    describe emp_ext_imported
    
    select * from emp_ext_imported;
    
    -- change default directory to media_dir2
    ALTER TABLE emp_ext
    DEFAULT DIRECTORY media_dir2;
    
    insert into /* +APPEND */emp_ext_imported
    select a.* 
    from emp_ext_imported_vw a;
    
    -- 1. for the next inserts one should first move the new "test.csv"-file
    -- to the not used directory and then change the default directory again. 
    -- see above. From this point you may cycle through those both steps. 
    -- 2. one may also use the PARALLEL-hint here; see docs which other hints 
    -- may be posiible
    This seems a bit overcomplicated. I don't like swapping between those two directories, but I wasn't able to interchange the files themselves/itselves (?). Is it possible that there is a OS-handle on it kept by the DB, which rejects overwriting/changing the file?

    Regards

    stratmo
  • 4. Re: Load data from excel to Oracle Tables
    theoa Pro
    Currently Being Moderated
    sql> create table test1(id number, year number(4) default to_number(to_char(sysdate,'yyyy')), month number(2)
     default to_number(to_char(sysdate,'mm')));
    
    Table created.
    
    sql> insert into test1(id) values (1);
    
    1 row created.
    
    sql> select * from test1;
    
            ID       YEAR      MONTH
    ---------- ---------- ----------
             1       2012         11
  • 5. Re: Load data from excel to Oracle Tables
    BluShadow Guru Moderator
    Currently Being Moderated
    The forum FAQ details read/writing data from/to Excel files (and CSV files)...

    {message:id=9360007}
  • 6. Re: Load data from excel to Oracle Tables
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    Instead of keeping posting questions without reading SQL and PL/SQL FAQ, like the previous post {message:id=10684787} where after 27 replies you were not able to explain what you wanted, wouldn't it better to start closing some of your unresolved questions?
    Handle:      966949  
    Status Level:      Newbie
    Registered:      Oct 22, 2012
    Total Posts:      26
    Total Questions:      7 (7 unresolved) 
    I am trying to do it but failed.
    Well, if it failed you should at least show what you have tried and which error you are getting. Is it asking too much?
    Please help me out and the other one is want to load the data from EXCEL to ORACLE table.
    I used to load the load the data by using SQL * LOADER. can you please let me know is it possible to load the data without using SQL*Loader? please provide me the solution for this.
    Mind reading mode set, abracadabra, et voilà!! Loaded! :-) Solution provided.
    SQL*Loader does not load Excel files directly. You can save them as csv (comma separated values) and load them with SQL*Loader or define them as external tables.

    Regards.
    Al
  • 7. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Hi ,

    I have created a table and loading the data by using SQL*Loader as follows.
    LOAD DATA
    INFILE 'C:\Users\Name\Desktop\emp.csv'
    BADFILE 'C:\Users\Name\Desktop\emp.bad'
    DISCARDFILE 'C:\Users\Name\Desktop\emp.dsc'
    INTO TABLE SCOTT.EMP
    fields terminated by ","
    (ENUM,
     ENAME,    
     JOB,    
     SAL,
     PAYMENT_Year,
    Payment_Month,
     Created_by,
     updated_BY)
    by using above am trying to load the data.

    here My requirement is to load the data payment year as current year(year only) , payment_month is by default month(MM only)

    so please let me know if there are any errors in my code.

    Want to load data directly from Excel to Oracle Table.

    Thanks.
  • 8. Re: Load data from excel to Oracle Tables
    AlbertoFaenza Expert
    Currently Being Moderated
    Dear "allmyquestionsareunresolved",

    have you tried to use this with SQL loader? Did you get any error?

    If yes, please post input file, control file (which you have already provided) and command line you are executing.

    I think however that this question is more suitable for this forum: Export/Import/SQL Loader & External Tables

    And by the way, what about marking some of your unresolved questions as answered? Or is maybe nobody able to answer your questions?
    Handle:      966949  
    Status Level:      Newbie
    Registered:      Oct 22, 2012
    Total Posts:      26
    Total Questions:      7 (7 unresolved)
    Regards.
    Al
  • 9. Re: Load data from excel to Oracle Tables
    BluShadow Guru Moderator
    Currently Being Moderated
    966949 wrote:
    Hi ,

    I have created a table and loading the data by using SQL*Loader as follows.
    LOAD DATA
    INFILE 'C:\Users\Name\Desktop\emp.csv'
    BADFILE 'C:\Users\Name\Desktop\emp.bad'
    DISCARDFILE 'C:\Users\Name\Desktop\emp.dsc'
    INTO TABLE SCOTT.EMP
    fields terminated by ","
    (ENUM,
    ENAME,    
    JOB,    
    SAL,
    PAYMENT_Year,
    Payment_Month,
    Created_by,
    updated_BY)
    by using above am trying to load the data.

    here My requirement is to load the data payment year as current year(year only) , payment_month is by default month(MM only)

    so please let me know if there are any errors in my code.

    Want to load data directly from Excel to Oracle Table.

    Thanks.
    So, still you don't want to give us an example of your data?

    Let's make one thing very clear...

    Excel files are NOT_ CSV files.
    CSV files are NOT_ Excel files.

    Excel is able to read and write CSV files, but that doesn't mean that CSV files are Excel files. Excel files are typically denoted with a .xls or .xlsx file extension. Many other applications can also produce .CSV files.

    So, it looks like you're trying to load CSV data (in which case completely ignore anything to do with Excel).

    To do that, especially if you want to manipulate the data in some way, it's far easier to use External Tables than SQL*Loader (though SQL*Loader has the advantage it can be used from a client computer).

    Here's an example using External Tables...

    http://www.morganslibrary.org/reference/externaltab.html



    I have a file on my server in a folder c:\mydata called text.csv which is a comma seperated file...
    1,"Fred",200
    2,"Bob",300
    3,"Jim",50
    As sys user:
    CREATE OR REPLACE DIRECTORY TEST_DIR AS "c:\mydata";
    GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser;
    Note: creates a directory object, pointing to a directory on the server and must exist on the server (it doesn't create the physical directory).

    As myuser:
    SQL> CREATE TABLE ext_test
      2    (id      NUMBER,
      3     empname VARCHAR2(20),
      4     rate    NUMBER)
      5  ORGANIZATION EXTERNAL
      6    (TYPE ORACLE_LOADER
      7     DEFAULT DIRECTORY TEST_DIR
      8     ACCESS PARAMETERS
      9       (RECORDS DELIMITED BY NEWLINE
     10        FIELDS TERMINATED BY ","
     11        OPTIONALLY ENCLOSED BY '"'
     12        (id,
     13         empname,
     14         rate
     15        )
     16       )
     17     LOCATION ('test.csv')
     18    );
    
    Table created.
    
    SQL> select * from ext_test;
    
            ID EMPNAME                    RATE
    ---------- -------------------- ----------
             1 Fred                        200
             2 Bob                         300
             3 Jim                          50
    
    SQL>
    {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 10. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Hi,

    I have created an External Table as you suggested and got the below error
    Error at Command Line:79 Column:4
    Error report:
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 -  "missing right parenthesis"
    *Cause:    
    *Action:
    Please find the below which am trying to create External table.
    CREATE table FMS_ALL.TEMP_REAL_CNTR_ROW_MTH_INFO
     (
       EMP_ID                       varchar2(50),
       E_NUM                    varchar2(50),
       PAY_CHRG                  varchar2(50),    
       )
     Organization external
     (
          type oracle_loader
          default directory datadumps
          access parameters 
       (records delimited by newline
        FIELDS TERMINATED BY ","
        OPTIONALLY ENCLOSED BY '"'
     --     badfile 'path.bad' 
    --     logfile 'path.log' 
          -- MISSING FIELD VALUES ARE NULL
       (
       EMP_ID,
       E_NUM,
       PAY_CHRG,    
       )
       )
     LOCATION ('test.csv')
     );
    {code}
    
    Please Have a look and let me know if I anything wrong here.
    
    Thanks.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 11. Re: Load data from excel to Oracle Tables
    sb92075 Guru
    Currently Being Moderated
    Error at Command Line:79 Column:4
    which is Line 79; since the excerpt you posted was not anywhere near that long?
  • 12. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    i have executed some other queries on the same page. so it's showing the rownum like that.

    and teh other thing is I ave added some other columns as well. and I want to load the data for that particular columns as shown below..

    Joined_year = current calendar year
    Hire_month = current month - 1
    fiscal year = current fiscal year ( we have fiscal year like if Hire_month=10 or Hire_month=11 or Hire_month=12 then fiscal_year=joined_year+1 else fiscal_year=joined_year.

    please give me suggestion like how can I go and add these conditions .. for Month and Joined year I have added but am not getting how to add the condition for fiscal_year.

    Edited by: 966949 on Nov 14, 2012 1:11 PM
  • 13. Re: Load data from excel to Oracle Tables
    rp0428 Guru
    Currently Being Moderated
    Oracle already had a look and told something is wrong.

    Compare your code with Justin's sample and you should be able to spot the problem
    PAY_CHRG, 
    The 'comma' is telling Oracle there is another column coming but you never provide one.
  • 14. Re: Load data from excel to Oracle Tables
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ



    Handle:     966949
    Status Level:     Newbie
    Registered:     Oct 22, 2012
    Total Posts:     29
    Total Questions:     7 (7 unresolved)

    why do you waste time here when you NEVER get any answers to your questions?
1 2 3 5 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points