# HELP....importing large text file

**URL:** <https://forums.toadworld.com/t/help-importing-large-text-file/10193>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [February 2, 2011, 11:36am UTC](https://forums.toadworld.com/t/help-importing-large-text-file/10193 "2011-02-02T11:36:50Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [February 2, 2011, 11:36am UTC](https://forums.toadworld.com/t/help-importing-large-text-file/10193/1 "2011-02-02T11:36:50Z")

</div>

I am running TOAD 10.6

I am trying to load a text file to populate a table I have just created. the  
Size of the file is 309,930 KB.

I tried to import data and got an out of memory error.  
Is there another way to populate the table?

Sparkie

And always remember…when life hands you Lemons, ask for tequila and salt and  
call me over

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [February 2, 2011, 11:42am UTC](https://forums.toadworld.com/t/help-importing-large-text-file/10193/2 "2011-02-02T11:42:11Z")

</div>

Use SQL Loader for large files. It may take a little longer to set up, but that  
time will be saved (and then some) when the process runs.

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [February 2, 2011, 11:43am UTC](https://forums.toadworld.com/t/help-importing-large-text-file/10193/3 "2011-02-02T11:43:35Z")

</div>

Do try the SQLLDR wizard. May take you a bit longer at first to get set up  
right, but the loader FLIES compared to the import process. Import process is  
absolutely lovely for smaller files.

Dan

Daniel B Madvig  
Computer Technologies

Northwestern College & Northwestern Media  
3003 Snelling Ave.  
St. Paul, MN 55113  
www.nwc.edu

651.631.5323  
 ![image001.jpeg](https://global.discourse-cdn.com/quest/original/2X/e/e116b4c84f27bd77fd3220f8a4f05f089f0a582f.jpeg)

---

<div class="post-metadata">

**Author:** ![Bert\_Scalzo](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/bert_scalzo/32/274_2.png) [@Bert\_Scalzo](https://forums.toadworld.com/u/Bert_Scalzo)\
**Post date:** [February 2, 2011, 11:54am UTC](https://forums.toadworld.com/t/help-importing-large-text-file/10193/4 "2011-02-02T11:54:18Z")

</div>

For those who want to get the benefits of sql loader without the syntax issues  
– consider external tables. You could create an external table to point to  
the data file and then simply issue a insert as select with parallel hints to  
make it fast J  
 ![image001.jpeg](https://global.discourse-cdn.com/quest/original/2X/e/e116b4c84f27bd77fd3220f8a4f05f089f0a582f.jpeg)

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [February 2, 2011, 12:53pm UTC](https://forums.toadworld.com/t/help-importing-large-text-file/10193/5 "2011-02-02T12:53:20Z")

</div>

SEE ATTACHMENT  
 ![image001.jpeg](https://global.discourse-cdn.com/quest/original/2X/e/e116b4c84f27bd77fd3220f8a4f05f089f0a582f.jpeg)

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [February 2, 2011, 1:05pm UTC](https://forums.toadworld.com/t/help-importing-large-text-file/10193/6 "2011-02-02T13:05:04Z")

</div>

SEE ATTACHMENT  
 ![image001.jpeg](https://global.discourse-cdn.com/quest/original/2X/e/e116b4c84f27bd77fd3220f8a4f05f089f0a582f.jpeg)

---

<div class="post-metadata">

**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\
**Post date:** [February 2, 2011, 2:31pm UTC](https://forums.toadworld.com/t/help-importing-large-text-file/10193/7 "2011-02-02T14:31:45Z")

</div>

I can’t assist from the Toad side… I still use the command interface for  
this task. Perhaps if I outline what I do, that might help you resolve what  
you’re running in to.

I tend to use a separate control file and run file as follows.

Run file contents:

Sqlldr control=$HOME/.ctl, log=$HOME/.log, bad=$HOME/.log, \

Discard=$HOME/.dis, data=$HOME/.dat;

The is a “your choice” option. For example, I use a generic  
load\_file.ctl, load\_file.dat, etc for my one-off kind of things (that way I only  
need modify the control file and ensure the data file is in place).

Control file contents:

LOAD DATA TRUNCATE

INTO TABLE

FIELDS TERMINATED BY ‘!’  
quotes

TRAILING NULLCOLS

(,

,

sequence)

Meanings:

Termination value can be literally any character in the character set you use.  
In the above example, I wanted to load each line into a single column so I  
picked a character that did not show up anywhere in the file.

Trailing allows you to populate a field value that does not otherwise appear in  
your data file. In the above example, it increments an index value so you can  
order the data in the exact order it appeared in the data file if that is a  
requirement.

To be able to specify a fixed file, you’d have the following changes in the  
control file:

Remove termination line. Replace the column definitions with:

( position (001:004),

position (005:063))

Hope this helps!

Roger S.  
 ![image001.jpeg](https://global.discourse-cdn.com/quest/original/2X/e/e116b4c84f27bd77fd3220f8a4f05f089f0a582f.jpeg)
