Showing posts with label table export. Show all posts
Showing posts with label table export. Show all posts

Thursday, June 23, 2016

Export Table Data From Database into Multiple Excel Files Using Alteryx

For this exercise  you need Oracle or any other database and Alteryx tool.

Get Alteryx from here -> http://www.alteryx.com/

Design the workflow as follows

In the 1st step, we are going to use "Input Data" tool to connect to Database and to read the data from table.

Create sample employee table using following sql

create table employees as 
    select level empno,
           'name' || level ename,
           case
              when level <= 10 then 10
              when level between 11 and 20 then 20
              when level between 21 and 30 then 30
              when level between 31 and 40 then 40
              when level between 41 and 50 then 50
           end
              deprt_id
      from dual
connect by level <= 50



In Step-2, use Alteryx "Formula" tool to name the files dynamically

In Step-3, use "Output Data" to write the files.








Thursday, June 16, 2016

Export Oracle table into Excel Using SQLPlus Spool command

The following code exports data from dba_tables table into .CSV file

After the table export into .CSV file, if we open the file with Excel we can see each table column data into separate columns in excel.

set term off
set echo off
set underline off
set colsep ,
set linesize 100
set pagesize 0
set lines 1000 pages 1000
set trimspool on
set feedback off
set heading on
set newpage 0
set headsep off

spool h:\tmp\table_export_into_file.csv
-- Exporing the table dba_tables into csv file
select owner, table_name, tablespace_name
from dba_tables
where rownum <=10;

spool off

Output looks like as follows