OraFAQ Forum: Forms » Import data from excel sheet to database tables...

Home » Developer & Programmer » Forms » Import data from excel sheet to database tables...

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Import data from excel sheet to database tables... [message #225971]

Thu, 22 March 2007 02:31

rdhaprakasam
Messages: 37
Registered: February 2007
Location: Coimbatore

Member

hi friends,
i am using oracle 9i forms and oracle 9i database..
i want to import data from excel spreadsheet into tables...i don`t know how to do it.. please tell me

Report message to a moderator

Re: Import data from excel sheet to database tables... [message #225972 is a reply to message #225971]

Thu, 22 March 2007 02:39

ritikaindu
Messages: 30
Registered: March 2007
Location: asas

Member

read the excel cells using ole2 ot text_io and then use sql* loader to load data into table

Report message to a moderator

Re: Import data from excel sheet to database tables... [message #226017 is a reply to message #225971]

Thu, 22 March 2007 05:30

rdhaprakasam
Messages: 37
Registered: February 2007
Location: Coimbatore

Member

hi ritikaindu,
i am new to oracle forms.. can you explain how can i do it with example..
thanks in advance..

Report message to a moderator

Re: Import data from excel sheet to database tables... [message #226044 is a reply to message #226017]

Thu, 22 March 2007 07:11

rdhaprakasam
Messages: 37
Registered: February 2007
Location: Coimbatore

Member

I have tried by using sqlldr...but it show the error...
Actually i don`t know how it works i got this from forum only...

I have attached my sample excel book with this and paste the control file which i used here below

Also here we are using multiple databases one of the database name is sical...

Control file:

LOAD DATA
CHARACTERSET WE8ISO8859P1
TRUNCATE
INTO TABLE example
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(COL1,COL2,COL3,COL4)

Error in CMD

D:\excel>SQLLDR SCOTT/TIGER@SICAL CONTROL=TEST.CTL

SQL*Loader: Release 9.2.0.1.0 - Production on Sun Apr 22 15:34:36 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL*Loader-500: Unable to open file (TEST.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Attachment: book2.csv
(Size: 0.08KB, Downloaded 1014 times)

Report message to a moderator

Re: Import data from excel sheet to database tables... [message #226047 is a reply to message #226044]

Thu, 22 March 2007 07:18

Frank
Messages: 7901
Registered: March 2000

Senior Member

READ the message.
Do you have a file D:\excel\TEST.dat ?

Report message to a moderator

Re: Import data from excel sheet to database tables... [message #226053 is a reply to message #226047]

Thu, 22 March 2007 07:29

rdhaprakasam
Messages: 37
Registered: February 2007
Location: Coimbatore

Member

no frank, no file with that name... i don`t know how to create that

Report message to a moderator

Re: Import data from excel sheet to database tables... [message #226055 is a reply to message #226053]

Thu, 22 March 2007 07:39

Frank
Messages: 7901
Registered: March 2000

Senior Member

Where did you learn about what to put in your control file and how to start sql-loader?

Report message to a moderator

Re: Import data from excel sheet to database tables... [message #226059 is a reply to message #226055]

Thu, 22 March 2007 07:43

rdhaprakasam
Messages: 37
Registered: February 2007
Location: Coimbatore

Member

in forums only .. i just type the command sqlldr with username and password..if u have another way tell me...

Report message to a moderator

Re: Import data from excel sheet to database tables... [message #226068 is a reply to message #226059]

Thu, 22 March 2007 07:58

Frank
Messages: 7901
Registered: March 2000

Senior Member

Read the sql-loader manual.

Report message to a moderator

Re: Import data from excel sheet to database tables... [message #226325 is a reply to message #226068]

Fri, 23 March 2007 04:46

rdhaprakasam
Messages: 37
Registered: February 2007
Location: Coimbatore

Member

hi friend,
the sql loader now work nicely.. but i need to get the excel file name dynamically .. please tell me how can i do it

Report message to a moderator

Re: Import data from excel sheet to database tables... [message #226890 is a reply to message #226325]

Mon, 26 March 2007 19:22

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Have a look at 'webutil' ('web_util') through which the browser can open a Microsoft Windows 'open window'. You can get the file name from the returned data.

David

Report message to a moderator

Previous Topic:	Prakash-Forms Doubt
Next Topic:	How to find User (who is connected to AS) in 3-Tier Architecture

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 13 00:24:29 CST 2025