load data from Excel sheet to an Oracle database

Toad for Oracle
1

Dear Toad experts,

I need to load data from Excel sheet to an Oracle database (a temp table). Can
anyone shed some light on how to accomplish it?

Thank in advance,

Lisa

2

Database>Import>Import Table Data
image001.jpeg

3

Database>Import>Import Table Data
image002.jpeg

4

You’ve got several options.

Right click on your table in the schema browser > Import Data

Point to your XLS file

Map the columns

Go.

If it’s a really big file, you may want to save as a CSV and setup a
SQL*Loader session to do the import.

5

You also can create an external table that references the CSV file and then use
a simple insert into my_table from csv_table;

6

Thanks all for the quick feedbacks. All options worked perfectly .

Lisa

7

Lisa.

Toad is very good at doing that and makes it easy.

First go to the menu and choose [DATABASE], [CREATE], [TABLE], follow the
prompts and Toad will create the table based on the ccolumn names in your
excel spreadssheet. ((rules apply... no spaces in table names). Look at the
layout before you save it in case you need to adjust column size or
datatype.
Now go back to [DATABASE], [IMPORT], [IMPORT TABLE DATA] ... at this point
you tell it which file you want and there is an import wizzard.

If you have any more questions, feel free to contact me directly via email.

Have a great day!

Regards

On Tue, Jun 21, 2011 at 1:05 PM, Lisa Hu wrote:

Dear Toad experts,

 

I need to load data from Excel sheet to an Oracle database (a temp table). 
Can anyone shed some light on how to accomplish it?

Thank in advance,

Lisa

–

Gene L. Bradley Jr.

Software Developer I

Information Systems & Integration

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

8

Hai Toad expert I am strugling to load data into a table and i have created a table it exist no I can’t load data into it

9

Go to database menu>>Import>>import table data

choose your desired table name in object name>> show data >>next

choose your file types(*excel)>> browse and enter your file name>>next

choose your first row and last row(you can leave last row field as blank) and you

can set various options>> next

choose the active sheet or only checked excel sheets to import data, mapping of your data is shown ( you can change it to auto map by clicking on auto map button) >>next

describe your key constraints >> next >>next

choose commit behavior and action >> Execute

Now you are DONE !

In general I just use excel file types and give my location of files and press next next next next and choose commit one after all records execute… and DONE…! :slight_smile: