Importing csv files

Toad for Oracle
1

Message from: DanD

I’m new to toad and oracle. I have some small files I want to import - a few
hundred to a few thousand records. In sql server I could use insert into
statements but when I try to put multiple insert into statements in toad, I get
an error message about illegal character.

I’ve read about sql loader but I apparently don’t have it one my system.
I tried to use database>import> import sourec files but I couldn’t
figure out how to get the data loaded after adding the file to the dialog. I
loaded it into the editor but didn’t know what to do after that.

Any help will be greatly appreciated.

Thanks

Historical Messages

Author: Dan Donahue
Date: Thu May 27 11:24:38 PDT 2010

I’m new to toad and oracle. I have some small files I want to import - a few
hundred to a few thousand records. In sql server I could use insert into
statements but when I try to put multiple insert into statements in toad, I get
an error message about illegal character.

I’ve read about sql loader but I apparently don’t have it one my system.
I tried to use database>import> import sourec files but I couldn’t
figure out how to get the data loaded after adding the file to the dialog. I
loaded it into the editor but didn’t know what to do after that.

Any help will be greatly appreciated.

Thanks

__

2

Evening Dan,

this is more of a Toad question than a ToadSQL question, so I've CC'd
the Toad group as well - just in case anyone else wants to ring in.

I'm new to toad and oracle.
Which version of Toad are you using?

I have some small files I want to import - a
few hundred to a few thousand records.
Shouldn't be a problem. The subject mentions that these are CSV files -
is this the case?

In sql server I could use insert
into statements but when I try to put multiple insert into statements in
toad, I get an error message about illegal character.
It depends on the version, but you could do it like this:

INSERT INTO table_name (Col_1, Col_2, ...)
VALUES (Value_1, Value_2, ...) ;

INSERT INTO table_name (Col_1, Col_2, ...)
VALUES (Value_1, Value_2, ...) ;

...

COMMIT;

You need the semi-colon at the end of each insert. When you have filled
the editor with all the required statements, you press F5 (Execute as
script) to run the whole lot as a script.

I've read about sql loader but I apparently don't have it one my system.
You will need to install the Oracle Clinet on your desktop/laptop. There
are a few options in that installer - the full set, a developer set, and
end user set. It sounds like you have the end user set - which gives you
the barest essentials for connecting to a database using an application.

The developer set should, I think, have sql*loader in it. The full admin
version certainly does.

I tried to use database>import> import sourec files but I couldn't
figure out how to get the data loaded after adding the file to the
dialog. I loaded it into the editor but didn't know what to do after that.
I'm away from my desk at the moment (it evening in the UK!) so I'm
unable to advise you accurately on this point. However, there are plenty
people around on the TOAD list who will be at their desks right now who
will probably help!

If not, tomorrow.

Any help will be greatly appreciated.
Hope the above gets you started.

Cheers,
Norm. [TeamT]

3

Very simply:
image001.jpeg

4

Very simply:
image001.jpeg

5

Very simply:
image001.jpeg

6

Dan,

I tried to use database>import> import sourec files but I couldn't
figure out how to get the data loaded after adding the file to the
dialog. I loaded it into the editor but didn't know what to do after
that.

Import Source File I think was used to execute script files like a file
with your insert statements. I don't see that option in Toad 10.5. I
fired up 9.7 and see that option. I've never used it so can't comment
on if it is still around. Anyways it sounds like you were trying to use
that with your CSV file which will not work since it's not a script file.

To import a CSV file into an Existing table just find the table in the
schema browser right click on it and select "Import from Text, Excel or
Access" I'm using the Toad 9.7 terminology since it sounds like you are
on an older version. In Toad 10.5 it is named "Import Data".

That brings up the Table Data Wizard. From there click through the
wizard to select your file and map fields.

I think Norm answered your other questions. If not let us know if you
are still have questions.

Ed
[TEamT]

On 5/27/2010 2:22 PM, Norman Dunbar wrote:

Evening Dan,

this is more of a Toad question than a ToadSQL question, so I've CC'd
the Toad group as well - just in case anyone else wants to ring in.

I'm new to toad and oracle.
Which version of Toad are you using?

I have some small files I want to import - a
few hundred to a few thousand records.
Shouldn't be a problem. The subject mentions that these are CSV files -
is this the case?

In sql server I could use insert
into statements but when I try to put multiple insert into statements in
toad, I get an error message about illegal character.
It depends on the version, but you could do it like this:

INSERT INTO table_name (Col_1, Col_2, ...)
VALUES (Value_1, Value_2, ...) ;

INSERT INTO table_name (Col_1, Col_2, ...)
VALUES (Value_1, Value_2, ...) ;

...

COMMIT;

You need the semi-colon at the end of each insert. When you have filled
the editor with all the required statements, you press F5 (Execute as
script) to run the whole lot as a script.

I've read about sql loader but I apparently don't have it one my system.
You will need to install the Oracle Clinet on your desktop/laptop. There
are a few options in that installer - the full set, a developer set, and
end user set. It sounds like you have the end user set - which gives you
the barest essentials for connecting to a database using an application.

The developer set should, I think, have sql*loader in it. The full admin
version certainly does.

I tried to use database>import> import sourec files but I couldn't
figure out how to get the data loaded after adding the file to the
dialog. I loaded it into the editor but didn't know what to do after that.
I'm away from my desk at the moment (it evening in the UK!) so I'm
unable to advise you accurately on this point. However, there are plenty
people around on the TOAD list who will be at their desks right now who
will probably help!

If not, tomorrow.

Any help will be greatly appreciated.
Hope the above gets you started.

Cheers,
Norm. [TeamT]