Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-440

Proper csv export from Oracle

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Solution:
      Hide

      Oracle DB

      Option 1)

      Use the Oracle SQL Developer:

      • Connect to the database you want to export
      • Select the table you want to export
      • Open context menu by right clicking on the table
      • Select Export to start the integrated Export Wizard
      • To guarantee a proper export, you should deselect the option Export DDL
      • Change format to csv and set the path for the exported file
      • Click Next to continue
      • The following screen allows you to specify the columns you want to export (SELECT *… by default)
      • Use the Edit-Button to change settings - otherwise you can proceed by clicking Next
      • Finally, you will receive a summary for the export process
      • Make sure that your settings are valid and complete the export by clicking Finish

      Notes:

      • Oracle SQL Developer automatically converts NULL to "" (empty string)
      • Oracle SQL Developer automatically masks double quotes ("Example" -> ""Example"")

      Option 2)

      Using SQLcl:
      SQLcl is a free tool provided by Oracle.
      (Download: http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html
      (Short documentation about how to get started and some of its features:
      https://mikesmithers.wordpress.com/2015/04/12/sqlcl-the-new-sqlplus/)

      • Connect to the database you want to export
      • To guarantee a proper export of your data, you have to change some of the system variables of SQLcl:
        o SET SQLFORMAT csv (automatic conversion into proper csv)
        o SET TERMOUT OFF (suppresses output on display - this option is only necessary when using scripts)
        o SET FEEDBACK OFF (suppresses displaying the number of records returned by a query)
      • To apply each option, just press ENTER after every single command
        Note: These options have to be set every time SQLcl is started!
      • Use the integrated spooler with the appended file path to start the process
      • SQLcl will write the result set of your SELECT-statement to table1.csv
      • To finish the process, you have to stop the spooler by using the command: spool off

      Notes:
      o You are not able to access the exported csv file until you stopped the spooler
      o SQLcl automatically masks double quotes (“Example” -> “”Example””)
      o SQLcl automatically converts NULL to “” (empty string)

      Show
      Oracle DB Option 1) Use the Oracle SQL Developer : Connect to the database you want to export Select the table you want to export Open context menu by right clicking on the table Select Export to start the integrated Export Wizard To guarantee a proper export, you should deselect the option Export DDL Change format to csv and set the path for the exported file Click Next to continue The following screen allows you to specify the columns you want to export (SELECT *… by default) Use the Edit-Button to change settings - otherwise you can proceed by clicking Next Finally, you will receive a summary for the export process Make sure that your settings are valid and complete the export by clicking Finish Notes: Oracle SQL Developer automatically converts NULL to "" (empty string) Oracle SQL Developer automatically masks double quotes ("Example" -> ""Example"") Option 2) Using SQLcl : SQLcl is a free tool provided by Oracle. (Download: http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html (Short documentation about how to get started and some of its features: https://mikesmithers.wordpress.com/2015/04/12/sqlcl-the-new-sqlplus/ ) Connect to the database you want to export To guarantee a proper export of your data, you have to change some of the system variables of SQLcl : o SET SQLFORMAT csv (automatic conversion into proper csv) o SET TERMOUT OFF (suppresses output on display - this option is only necessary when using scripts) o SET FEEDBACK OFF (suppresses displaying the number of records returned by a query) To apply each option, just press ENTER after every single command Note: These options have to be set every time SQLcl is started! Use the integrated spooler with the appended file path to start the process SQLcl will write the result set of your SELECT-statement to table1.csv To finish the process, you have to stop the spooler by using the command: spool off Notes: o You are not able to access the exported csv file until you stopped the spooler o SQLcl automatically masks double quotes (“Example” -> “”Example””) o SQLcl automatically converts NULL to “” (empty string)
    • Category 1:
      ETL / Data Integration
    • Category 2:
      3rd Party Tools - ETL

      Attachments

        Issue Links

        1. Oracle9.png
          Oracle9.png
          3 kB
        2. Oracle8.png
          Oracle8.png
          3 kB
        3. Oracle7.png
          Oracle7.png
          7 kB
        4. Oracle6.png
          Oracle6.png
          5 kB
        5. Oracle5.png
          Oracle5.png
          70 kB
        6. Oracle4.png
          Oracle4.png
          63 kB
        7. Oracle3.png
          Oracle3.png
          76 kB
        8. Oracle2.png
          Oracle2.png
          53 kB
        9. Oracle1.png
          Oracle1.png
          25 kB

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: