Querying Oracle DB: Formatting and saving results from SQlPlus in TXT & CSV

SQL*Plus: Release 8.1.7.0.0 – Production on Thu Jan 24 14:25:37 2013

Connected to:

Oracle8i Enterprise Edition Release 8.1.6.1.0 – Production

With the Partitioning option

JServer Release 8.1.6.0.0 – Production

SQL>

SQL> SET UNDERLINE OFF

SQL> SET COLSEP ‘,’

SQL> SET LINES 100 PAGES 100

SQL> SET FEEDBACK off

SQL> SET LINESIZE 1000

SQL> SPOOL C:TestFolderMyUsers-DeletedData.txt

SQL>Select * from EmpData where usergroup = 11771

SQL> SPOOL OFF

Open the C:TestFolderMyUsers-DeletedData.txt file and verify that data is the one you are looking for
Open an Excel Sheet and Import

Setting up SQLPlus environment variables:

SET UNDERLINE OFF

SET LINES 100 PAGES 100

SET COLSEP ‘,’ — (specify the delimiter you like to have for separating the columns in the results)

SET FEEDBACK off

SET HEADING off –(If you don’t want column headings in CSV file)

SET LINESIZE 5000

SET PAGESIZE 0

SET ECHO OFF

clear break
clear comp
clear col

Spool C:TempFolderEMP.csv — File will be in-use by SQlPLus till you run Spool OFF

SELECT * FROM EMP; — Now the actual query

Spool OFF

set trimspool on
set tab off
set echo off
set feedback off
set recsep off