1 2 3 Previous Next 70 Replies Latest reply: Nov 20, 2012 11:33 AM by 969952 RSS

    Load data from excel to Oracle Tables

    969952
      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
          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
            >
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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 Previous Next