Header Ads

  • Breaking News

    Exporting Data from a table in Oracle to Excel(csv) and then importing from Excel(csv) to another Oracle table

    Of late I had to export data from few tables from one database to another. Since the records were only in backend and not through PeopleSoft usage of Data Mover is ruled out. DB Link creation was ruled out due to lack of privileges for the given user id I connected. And even did not have third party tools like TOAD which allow exporting data from table in the format of INSERT scripts, .csv, .txt etc and I was plainly left with SQL Plus. Googling around came across few ways of importing data from a table to excel.

    Method I - Exporting data to Excel Sheet
    SQL> set feed off markup html on spool on
    SQL> spool c:\tblname_dbname.xls
    SQL> select * from tblname;
    SQL> spool off
    SQL> set markup html off spool off

    This creates a lovely spreadsheet with data for the corresponding SQL query. However it requires more work in excel spreadsheet to use this data for inserting it in another table.

    Method II - Exporting data to CSV:
    SELECT field1||','||field2||','||field3','||field4
    FROM tablename
    WHERE <conditions> ;

    Importing data from CSV to Oracle:
    1. Open the CSV file and add a new column at the beginning. Have your insert statement in the first(new) column as: INSERT INTO tablename field1, field2,... VALUES(
    2. In the last column add this );
    3. Fill both these values till the end of respective columns.
    4. Save this CSV file with a .txt extension
    5. From SQL Plus, execute the below SQL:

    SQL&gt; spool on
    SQL&gt; @c:\tblname_dbname.txt
    SQL&gt; spool off
    Review for any error messages before issuing a COMMIT statement.

    This is not the only way for importing/exporting data in a Oracle table with excel or csv. For there isn't one defined way of doing this and there are multiple options available to export data from one oracle table to another. However for faster processing, SQL Loader is the best option.

    3 comments:

    1. your method1 worked perfectly.Thanks a lot.

      ReplyDelete
    2. Will this work for numeric fields and date format as all the fields are converted to string while exporting, import to will have string value with in single quotes?

      ReplyDelete

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad

    ad728