asked on

Importing data from Excel into Oracle

I have an Excel form that has a few 100 records that I need to import into an oracle database for my ASP.NET application (VB).

What is the best way of doing this? I guess it is easy when your database is SQL server (using DTS).I prefer to be from my ASP.NET application.
I want to INSERT them all at once. My code currently reads from Excel and displays the records in a DataGrid.

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

mandar0

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jetaasen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ddandekar

You can also use external table feature in Oracle 9 to load data from .csv files created as an option in Excel. Create a dictionary entry on the server and then the data in the table is available for selection as
create table Table_external
( OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(18),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
(
records delimited by newline skip 21
fields terminated by '|'
missing field values are null
( owner ,object_name ,subobject_name ,object_id ,data_object_id ,object_type
,created date 'dd-mon-yy' ,last_ddl_time date 'dd-mon-yy'
,"TIMESTAMP" ,status ,temporary ,generated ,secondary
)
)
location ('big_table.dat')
)
/

kannankumara

Visit www.oraxcel.com and get SQL*XL tool. Its simple and handy.