Max number rows for Import?

Toad for Oracle - Beta
1

I have a huge tab-delimited text file that I’m trying to import. There are 145,247 rows, no header. One of the fields is very large, it is allowed to hold up to 10,000 characters. (I am not importing that particular field.)

I have tried to import the file multiple ways, and each time, TOAD acts like the import was successful, yet each time I only get a fraction of the rows. Once 12k, once 29k, once 31k, but never more than that.

I have checked the data in the file around the last row that imported, and not only does the data not match up, but there doesn’t appear to be any problem with the text file. I have converted it to a different encoding. . . (One example is that the data in the text file appears to be sorted by country code and then ascending city name, but the imported data appears to be sorted by country code and then descending order by city name.)

Is there a limit to how many rows I can import using the import wizard? Do I need to work with our DBAs to data pump this data in?

Blessings,

Phyllis

–
Phyllis Helton

Data Magician
Digital Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org

2

Hi Phyllis,

There shouldn’t be any real limit. I’ve imported millions of rows. Are you importing with the Array DML option? If so, one bad row will prevent the entire array (default 500 rows) from importing. So you might try the non-Array-DML option and see if you get any specific errors. If you can send me the file and table DDL, I’ll be happy to try to help diagnose and fix the problem.

-John

3

A couple more thoughts:

  1. if you can’t send the entire file, maybe we can reproduce the problem with a portion of it (and sensitive fields removed or replaced with junk data)

  2. It should never act like everything worked fine if some rows are failing to import.

  3. Does it shed any light if you check “Log errors”?

4

Thanks John. I'll send you the file and the ddl for the table privately. It is a free data source, so there is no problem sending you the entire thing.

When I turn logging on, I don't get any log. I haven't used the Array DML, so it isn't that.

I appreciate your help with this. :slight_smile:

On Mon, Jul 13, 2015 at 4:49 PM, John Dorlon bounce-jdorlon@toadworld.com wrote:

RE: Max number rows for Import?

Reply by John Dorlon
A couple more thoughts:

  1. if you can't send the entire file, maybe we can reproduce the problem with a portion of it (and sensitive fields removed or replaced with junk data)
  1. It should never act like everything worked fine if some rows are failing to import.
  1. Does it shed any light if you check "Log errors"?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag this post as spam/abuse.

--
Phyllis Helton

Data Magician
Digital Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org