Home » Developer & Programmer » Forms » Import data from excel sheet to database tables...
Import data from excel sheet to database tables... [message #225971] Thu, 22 March 2007 02:31 Go to next message
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
Re: Import data from excel sheet to database tables... [message #225972 is a reply to message #225971] Thu, 22 March 2007 02:39 Go to previous messageGo to next message
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
icon8.gif  Re: Import data from excel sheet to database tables... [message #226017 is a reply to message #225971] Thu, 22 March 2007 05:30 Go to previous messageGo to next message
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..
Re: Import data from excel sheet to database tables... [message #226044 is a reply to message #226017] Thu, 22 March 2007 07:11 Go to previous messageGo to next message
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 183 times)
Re: Import data from excel sheet to database tables... [message #226047 is a reply to message #226044] Thu, 22 March 2007 07:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
READ the message.
Do you have a file D:\excel\TEST.dat ?
Re: Import data from excel sheet to database tables... [message #226053 is a reply to message #226047] Thu, 22 March 2007 07:29 Go to previous messageGo to next message
rdhaprakasam
Messages: 37
Registered: February 2007
Location: Coimbatore
Member

no frank, no file with that name... i don`t know how to create that
Re: Import data from excel sheet to database tables... [message #226055 is a reply to message #226053] Thu, 22 March 2007 07:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Where did you learn about what to put in your control file and how to start sql-loader?
Re: Import data from excel sheet to database tables... [message #226059 is a reply to message #226055] Thu, 22 March 2007 07:43 Go to previous messageGo to next message
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...
Re: Import data from excel sheet to database tables... [message #226068 is a reply to message #226059] Thu, 22 March 2007 07:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Read the sql-loader manual.
Re: Import data from excel sheet to database tables... [message #226325 is a reply to message #226068] Fri, 23 March 2007 04:46 Go to previous messageGo to next message
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
Re: Import data from excel sheet to database tables... [message #226890 is a reply to message #226325] Mon, 26 March 2007 19:22 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: Prakash-Forms Doubt
Next Topic: How to find User (who is connected to AS) in 3-Tier Architecture
Goto Forum:
  


Current Time: Mon Dec 05 21:30:32 CST 2016

Total time taken to generate the page: 0.06485 seconds