Press "Enter" to skip to content

How to Export to Flat File in Toad?

To export data from Oracle table into a Flat File using Toad, follow these steps.

Export Data into Flat File in Toad

  1. Open Toad and connect to the database.
  2. Then click on the menu Database > Export > Table as Flat File.
  3. A window will appear as shown in below image.

export flat file in toad for oracle

  1. Then click on the Options tab and specify the Schema name, Table name and the flat file location as shown in below image.

table-to-export-in-toad

  1. Then again click on the Spec File tab and click on the Generate Columns button. It will generate the specification file as shown in below example.
TABLEOWNER=SCOTT
TABLENAME=EMP
DATAFILE=F:\Temp\emp.dat
LINESPERRECORD=1
COL1=EMPNO,1,1,5
COL2=ENAME,1,7,10
COL3=JOB,1,18,9
COL4=MGR,1,28,5
COL5=HIREDATE,1,34,19
COL6=SAL,1,54,9
COL7=COMM,1,64,9
COL8=DEPTNO,1,74,3
  1. You can click on the Save Spec File button to save this configuration in a file (optional).
  2. Then click on the SQL Loader tab to view the SQL loader script for this file. Below is an example of generated SQL loader file.
RECOVERABLE
LOAD DATA
INFILE "F:\Temp\emp.dat"
INSERT
INTO TABLE EMP
( EMPNO POSITION (1:5) DECIMAL EXTERNAL, 
ENAME POSITION (7:16) CHAR,
JOB POSITION (18:26) CHAR,
MGR POSITION (28:32) DECIMAL EXTERNAL, 
HIREDATE POSITION (34:52) DATE (19) "MM/DD/YYYY HH24:MI:SS",
SAL POSITION (54:62) DECIMAL EXTERNAL, 
COMM POSITION (64:72) DECIMAL EXTERNAL, 
DEPTNO POSITION (74:76) DECIMAL EXTERNAL )
  1. You can use this SQL Loader control file script to load data into the similar table in any other schema.
  2. Then finally click on the Execute button to export the Flat file into the specified location.

See also:

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *