SQL LOADER for TOAD for ORACLE

Toad for Oracle
1

Hello,

I am struggling to use SQL LOADER with a CSV file. I have used the SQL LOADER wizard in hopes that it will create the control file and load the data into the table. I am not too sure what I am doing wrong. I am placing my altered version of the control file. I noticed that when I use the wizard nothing happens or the data is not loaded.

OPTIONS ( SILENT=(HEADER))
RECOVERABLE
LOAD DATA
INFILE 'C:...\Streamlyne MySQL Replica Export\TOAD sql loader\affiliation_type.csv'
BADFILE 'C:...\Streamlyne MySQL Replica Export\TOAD sql loader\affiliation_type.bad'
DISCARDFILE 'C:...\Streamlyne MySQL Replica Export\TOAD sql loader\affiliation_type.dsc'

INSERT
INTO TABLE "UNM_STREAMLYNE_STAGING"."AFFILIATION_TYPE"
INSERT
FIELDS TERMINATED BY EOF
(VER_NBR,
AFFILIATION_TYPE_CODE,
DESCRIPTION,
UPDATE_TIMESTAMP,
UPDATE_USER,
OBJ_ID,
ACTIVE_FLAG)

image
image1253×60 2.02 KB

2

I'll test this if you give me a little more:

  1. a "Create Table" statement for the AFFILIATION_TYPE table
  2. a couple rows worth of data in the CSV file (even if it's made-up data)

You can email it to me if you don't want to post it here: john.dorlon@quest.com

1 Like
3

Hi John,

I have already created the table before attempting to learn more about SQL LOADER. Here is the SQL for that:
CREATE TABLE UNM_STREAMLYNE_STAGING.affiliation_type (
VER_NBR number(8,0) DEFAULT '1' NOT NULL,
AFFILIATION_TYPE_CODE number(3,0) DEFAULT '0' NOT NULL,
DESCRIPTION varchar2(200 char) NOT NULL,
UPDATE_TIMESTAMP date NOT NULL,
UPDATE_USER varchar2(60 char) NOT NULL,
OBJ_ID varchar2(36 char) NOT NULL,
ACTIVE_FLAG char(1 char) NOT NULL,
PRIMARY KEY (AFFILIATION_TYPE_CODE)
) ;

Here is a picture of some of the date from the CSV file.

image
image980×142 6.5 KB

4

I saw that last line too late darn!

5

Please send the actual file, don't make me type it. :frowning:

6

I sent it over via email

7

I got your file.
I am having trouble getting it to import with SQL Loader too.
I was going to suggest not using SQL Loader, but to use Toad's data import wizard instead....but I am having a problem with that using CSV files. Toad is missing the first character of each line.

Try this:

  1. Open your file in Excel and save to XLSX
  2. Right-click on your table in Toad's schema browser and choose "Data -> Import"
  3. Hit Next in the data import wizard.
  4. change file type to Excel, then select your file
  5. Hit Next and set "First row" to 2.
  6. Next, Next Next through the wizard and your data will be imported.
8

I see. I am familiar with that import wizard. I wanted to test the SQL LOADER import wizard in particular because we will be needing to load large tables from a 3rd party app. I was testing in a smaller subset of data. Is the SQL LOADER in TOAD something that is not viable?

9

As far as I know, SQL Loader works just fine. I never use it because it's somewhat complicated, so I am probably doing something wrong too.

10

Thanks John, I appreciate the time on this :slightly_smiling_face:

11

I noticed that your file has line feeds of just CRs. Windows line feeds are CRLF, and Unix is just LF. Toad can handle both of these just fine. The fact that your line feeds are CR only is the cause of the problem in the data import window, and maybe also SQL Loader (I haven't started looking at SQL Loader yet).

But you might want to try saving with CRLF and see if that solves your problem.