1 2 Previous Next 18 Replies Latest reply: Sep 4, 2012 10:53 AM by 881285 RSS

    DATA MERGING

    881285
      hi people i need a pl/sql code which will help me upload data in csv format . thanks
        • 1. Re: DATA MERGING
          Hoek
          Assuming you mean to 'dump data to file in csv format':
          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056
          http://www.oracle-base.com/articles/9i/generating-csv-files.php

          Edited by: Hoek on Aug 7, 2012 3:18 PM
          • 2. Re: DATA MERGING
            Hoek
            And if you mean you want to insert data into a database table then try an external table, you can 'query' your csv file, select from it, and do the insert in one go.
            http://www.oracle-base.com/articles/9i/external-tables-9i.php
            • 3. Re: DATA MERGING
              881285
              thanks for your concern but let me explain the once more what i meant was
              i have a table in my database which already might have some data, no i want to merge data from a .csv file into that same table
              • 4. Re: DATA MERGING
                Frank Kulash
                Hi,
                Nii Moi wrote:
                thanks for your concern but let me explain the once more what i meant was
                i have a table in my database which already might have some data, no i want to merge data from a .csv file into that same table
                As Hoek said, you can create an external table, based on your csv file.
                Then you can use that extrenal table in DML statements (such as INSERT or MERGE) that change your other table.
                • 5. Re: DATA MERGING
                  881285
                  thank You all for ur concern but it seems the link elaborates more on extracting data into csv file than extracting from csv. what i want to do now is to extract from a csv file into a table
                  • 6. Re: DATA MERGING
                    Solomon Yakobson
                    Nii Moi wrote:
                    thank You all for ur concern but it seems the link elaborates more on extracting data into csv file than extracting from csv. what i want to do now is to extract from a csv file into a table
                    First question you need to answer is file location. Is file accessible from database server side of only from client side. Answer to this defines set of tools you can use. If file is accessible from client side only your best bet is SQL*Loader. You create control file and use it to load csv file data into your table. If file is accessible from database server side your best bet is external table plus insert into your table. Both SQL*Loader and external tables are well documented so read Oracle docs. And there are plenty SQL*Loader & external table examples on this forum and on the net.

                    SY.
                    • 7. Re: DATA MERGING
                      00125
                      Hi,

                      Create control file for .csv file and then by using sql loader it fetch data into oracle table or otherwise create external table and fetch data by stored procedure.

                      Thanks in advance
                      • 8. Re: DATA MERGING
                        888299
                        if your data input size is moderate (<10'000 rows), it might be convenient to read the CSV into a front-end database like MS-ACCESS. You can define a table there, with the same structure like the oracle database.
                        You copy&paste your data from CSV or EXCEL into this ACCESS-Table. This will do the basic data validation.

                        Then you connect your Oracle-Table via ODBC. You create an update query in Access. Voilà.
                        This procedure can be done by the business people.

                        Finnaly when you need some logging or other staging logic, you do that in PL/SQL, while transfering the data from the ORACLE-Staging table to target table.

                        Might be worth considering, because handling with CSV, PL/SQL und serversided File-IO is quite sensitive and normally reserved to the SYSDBA.

                        Othmar Lippuner
                        Oracle PL/SQL Professional; SQL Reporting Professional
                        • 9. Re: DATA MERGING
                          Gaff
                          PTOOO! OH you did NOT use "database" and "access" in the same sentence. You did not! :D

                          Forget the 3rd party toys and ODBC. SQL Loader, External tables. Try them, you'll like them.

                          If you must use a 3rd party tool, check out the one Oracle provides for free.

                          http://database.blogs.webucator.com/2011/02/27/importing-data-using-oracle-sql-developer/

                          ---
                          OK. I see SQL Developer does not merge even when using SQL Loader. So use SQL Loader or external tables and a merge command.

                          Edited by: Gaff on Aug 13, 2012 5:48 PM
                          • 10. Re: DATA MERGING
                            rp0428
                            >
                            OK. I see SQL Developer does not merge even when using SQL Loader. So use SQL Loader or external tables and a merge command.
                            >
                            Well it does if you use APPEND mode instead of REPLACE.
                            • 11. Re: DATA MERGING
                              Chanchal Wankhade
                              Hi nii moi,


                              you can do it by MERGE As well.

                              create a table that you have as: create table table_name_dump as select * from your_actual_table_name where any_wrong_condition;

                              it will create a table structure only.

                              now Use SQl* Loader to load the data into newly created table.


                              Now the point you are saying use merge as if the record is there then update and if not there then insert.





                              Enjoy....
                              • 12. Re: DATA MERGING
                                881285
                                thank u all please review the code below am reading a csv file from c:\temp into a table after running the code i receive a feedback as statement processed but no data gets inserted can u help me its urgent






                                DECLARE
                                DOC UTL_FILE.FILE_TYPE;
                                DATA VARCHAR2(500);
                                PORGCODE VARCHAR2(20);
                                NAME VARCHAR2(200);
                                ORGTYPE VARCHAR2(200);
                                CBS VARCHAR2(3);
                                GCC VARCHAR2(3);
                                START_DATE DATE;
                                END_DATE DATE;
                                REGIONCODE VARCHAR2(10);
                                DISTRICTCODE VARCHAR2(10);
                                DEZONE varchar2(20);
                                OPERATIONAL_AREA VARCHAR2(100);
                                COMMUNITY VARCHAR2(100);
                                MALE NUMBER ;
                                FEMALE NUMBER ;
                                clasification varchar2(20);
                                fl number;
                                ml number;

                                BEGIN
                                DOC :=UTL_FILE.FOPEN('DR','TEST1.csv','R');
                                LOOP
                                BEGIN
                                UTL_FILE.GET_LINE(DOC, DATA);
                                SELECT SUBSTR(DATA, 1 ,INSTR(DATA, ',', 1, 1)-1) INTO PORGCODE FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 1)+1, INSTR(DATA,',',1,2)-INSTR(DATA,',',1,1)-1) INTO NAME FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 2)+1, INSTR(DATA,',',2,3)-INSTR(DATA,',',1,2)-1) INTO ORGTYPE FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 3)+1, INSTR(DATA,',',3,4)-INSTR(DATA,',',1,3)-1) INTO CBS FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 4)+1, INSTR(DATA,',',4,5)-INSTR(DATA,',',1,4)-1) INTO GCC FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 5)+1, INSTR(DATA,',',5,6)-INSTR(DATA,',',1,5)-1) INTO START_DATE FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 6)+1, INSTR(DATA,',',6,7)-INSTR(DATA,',',1,6)-1) INTO END_DATE FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 7)+1, INSTR(DATA,',',7,8)-INSTR(DATA,',',1,7)-1) INTO REGIONCODE FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 8)+1, INSTR(DATA,',',8,9)-INSTR(DATA,',',1,8)-1) INTO DISTRICTCODE FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 19)+1, INSTR(DATA,',',9,10)-INSTR(DATA,',',1,9)-1) INTO DEZONE FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 10)+1, INSTR(DATA,',',10,11)-INSTR(DATA,',',1,10)-1) INTO OPERATIONAL_AREA FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 11)+1, INSTR(DATA,',',11,12)-INSTR(DATA,',',1,11)-1) INTO COMMUNITY FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 12)+1, INSTR(DATA,',',12,13)-INSTR(DATA,',',1,12)-1) INTO MALE FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 13)+1, INSTR(DATA,',',13,14)-INSTR(DATA,',',1,13)-1) INTO FEMALE FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 14)+1, INSTR(DATA,',',14,15)-INSTR(DATA,',',1,14)-1) INTO clasification FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', 1, 15)+1, INSTR(DATA,',',15,16)-INSTR(DATA,',',1,15)-1) INTO fl FROM DUAL;
                                SELECT SUBSTR(DATA, INSTR(DATA,',', -1, 1)+1) INTO ml FROM DUAL;

                                INSERT INTO PORG_NRGP(PORGCODE,NAME,ORGTYPE,CBS,GCC,START_DATE,END_DATE,REGIONCODE,DISTRICTCODE,DEZONE,OPERATIONAL_AREA,COMMUNITY,MALE,FEMALE,clasification ,fl,ml) VALUES
                                (PORGCODE||PROGCODE.NEXTVAL||'D',NAME,ORGTYPE,CBS,GCC,START_DATE,END_DATE,REGIONCODE,DISTRICTCODE,DEZONE,OPERATIONAL_AREA,COMMUNITY,MALE,FEMALE,clasification ,fl,ml);
                                EXCEPTION
                                WHEN OTHERS THEN
                                EXIT;
                                END;
                                END LOOP;
                                UTL_FILE.FCLOSE(DOC);
                                END;

                                -------table

                                CREATE TABLE PORG_NRGP
                                (
                                PORGCODE VARCHAR2(20)PRIMARY KEY,
                                NAME VARCHAR2(200),
                                ORGTYPE VARCHAR2(200),
                                CBS VARCHAR2(3) CHECK(REGEXP_LiKE(CBS,'^(YES|NO)$')),
                                GCC VARCHAR2(3) CHECK(REGEXP_LiKE(GCC,'^(YES|NO)$')),
                                START_DATE DATE,
                                END_DATE DATE,
                                REGIONCODE VARCHAR2(10),
                                DISTRICTCODE VARCHAR2(10),
                                DEZONE varchar2(20),
                                OPERATIONAL_AREA VARCHAR2(100),
                                COMMUNITY VARCHAR2(100),
                                MALE NUMBER DEFAULT 0,
                                FEMALE NUMBER DEFAULT 0,
                                clasification varchar2(20),
                                fl number default 0,
                                ml number default 0,
                                CONSTRAINTS PORG_FK FOREIGN KEY(DISTRICTCODE,REGIONCODE)
                                REFERENCES DISTRICT_NRGP(DISTRICTCODE,REGIONCODE) ON DELETE CASCADE
                                );
                                • 13. Re: DATA MERGING
                                  SomeoneElse
                                  You're re-inventing the wheel.

                                  Use external tables (or SQL Loader). If it's really "urgent" you could have been done by now.
                                  • 14. Re: DATA MERGING
                                    881285
                                    help me out with the loader then cuz i have no idea what u are talking of
                                    1 2 Previous Next