Oracle CSV Export from Table or Query Data

ZappyShell for DB can export your Oracle data to CSV, PDF, Excel in one line command. This is just single exe less than 5 MB.. CSV export allows you to export data in overwite or append mode. You can specify header, escape sequence, qualifiers and some other advanced options for CSV export.

Download here. Now Lets check how to do this.

Download and Install ZappyShell Command Line Tools

Now next step is we need to Download and Install ZappyShell Command Line tools from hereYou can Download MSI Installer or XCopy Zip file which you can simply extract and copy (see below).

ZappyShell Command Line Tools – Export CSV, Excel, JSON, PDF

Download and Install Oracle Drivers

If you don’t have oracle drivers installed then just get correct ODBC or OLEDB Driver for Oracle from below link
http://www.oracle.com/technetwork/database/windows/index-098976.html

Sample Oracle ODBC Connection String

Driver={Oracle in OraClient11g_home1};Dbq=myTNSServiceName;Uid=myUsername; Pwd=myPassword;
Driver={Oracle in OraClient11g_home1};Server=serverSID;Uid=myUsername; Pwd=myPassword;
Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=199.199.199.199)(PORT=1523))(CONNECT_DATA=(SID=dbName))); Uid=myUsername;Pwd=myPassword;

Sample Oracle OLEDB Connection String

--Connection using UserID/Password
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
--Trusted Connection
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;

More connection strings found here

Oracle CSV Export – Single table

c:\oracle\tools>db.exe export customers --out c:\oracle-data\customers.csv --csv --overwrite --connstr "Driver={oracle in oradb10g_home1};uid=scott;pwd=tiger;server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

Oracle CSV Export – Multiple tables

In the following example we are exporting 3 tables (seperated by vertical bar)

c:\oracle\tools>db.exe export customers|orders|products --out c:\oracle-data\customers.csv --csv --overwrite --connstr "Driver={oracle in oradb10g_home1};uid=scott;pwd=tiger;server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

Oracle CSV Export – Multiple tables (using pattern)

In below example all tables where name staring with cust will be exported from sales schema.

c:\oracle\tools>db.exe export cust% --schema Sales --out c:\oracle-data\customers.csv --csv --overwrite --connstr "Driver={oracle in oradb10g_home1};uid=scott;pwd=tiger;server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

Oracle CSV Export – SQL Query

In the following example we are exporting output of SQL Query to CSV file. If you have complex query with multi line then you can use Map file with one dataset node.

c:\oracle\tools>db.exe export "select * from customers where country like 'US%'" --out c:\oracle-data\customers.csv --csv --overwrite --connstr "Driver={oracle in oradb10g_home1};uid=scott;pwd=tiger;server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

Oracle CSV Export – Split Files

Sometimes you may have need to split large amount of data into multiple files. With ZappyShell command line you can split data three different ways. Lets check each example.

Oracle CSV Export – Split By Row Count

In below example we are exporting Oracle customer table to CSV files and splitting files with maximum 1000 records per file option.

c:\oracle\tools>db.exe export customers --out c:\oracle-data\cust.csv --split --splitrows 1000 --connstr "Driver={oracle in oradb10g_home1};uid=scott;pwd=tiger;server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

Oracle CSV Export – Split By File Size

In below example we are exporting Oracle customer table to CSV files and splitting files with maximum file size = 10MB option. You also use other size specifier like KB, GB, BYTES etc.

c:\oracle\tools>db.exe export customers --out c:\oracle-data\cust.csv --split --splitsize 10MB --connstr "Driver={oracle in oradb10g_home1};uid=scott;pwd=tiger;server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

Oracle CSV Export – Split By Group By Column

In this example we have exported customers in to separate file by country.

c:\oracle\tools>db.exe export customers --out c:\oracle-data\cust.csv --groupby Country --connstr "Driver={oracle in oradb10g_home1};uid=scott;pwd=tiger;server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

Oracle CSV Export – Table to CSV, Split files by Country Column

Exporting Compressed CSV files

If you want to output compressed CSV (GZIP format) then use below command.

c:\oracle\tools>db.exe export customers --out c:\oracle-data\cust.csv --gzip --connstr "Driver={oracle in oradb10g_home1};uid=scott;pwd=tiger;server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

Using date time place holders in CSV file name

If you want to use date time place holders in file name so it automatically uses specified date time parts use below command simply putting specifiers in file name anywhere.

c:\oracle\tools>db.exe export customers --out c:\oracle-data\cust_{{YYYY}}_{{MM}}_{{DD}}_{{HH24}}{{MI}}{{SS}}.csv --connstr "Driver={oracle in oradb10g_home1};uid=scott;pwd=tiger;server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

Conclusion

In this article we have gone through few ways to use ZappyShell Command Line Tools to export data from Oracle to CSV. You can also use same command line to export in Excel, JSON, PDF, HTML format.

Reference Links

Download ZappyShell

Introduction