Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

load excel data into orcale table without converting it into csv or txt

Posted on 2004-03-31
7
9,863 Views
1 Endorsement
Last Modified: 2012-08-13
I want to populate oracle table with excel file. if somebody have exact coding to insert excel file record into orcale table plz answer me as soon as poosible. here i want to clear it i dont want to use sql loader to upload data from csv file or txt file. so please dont suggest me to save excel file into csv or txt and using sql loader load that data into oracle table. only and only i want to load data of excel(*.xls) file into orcale table.
1
Comment
Question by:rehman123
7 Comments
 
LVL 13

Expert Comment

by:riazpk
ID: 10721960
Well, to my knowledge, the answer is NO...you can't do that without using SQL*Loaded (or External Tables, if you are using 9i).
0
 
LVL 13

Expert Comment

by:riazpk
ID: 10721996
Ohh wait....here are couple of ways:

I Also a tool to do that on:

http://www.oraxcel.com/projects/sqlxl/index.htm


Also From
http://asktom.oracle.com/pls/ask/f?p=4950:8:4026090959248828264::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:243814703172,

There are a couple of ways to approach this.

1) load the excel spreadsheet into the database "as is".  You can use interMedia
text to convert the .xls file into a .htm file (HTML) or use iFS (see
http://technet.oracle.com/
for
more info on that) to parse it as well.  InterMedia text will convert your XLS
spreadsheet into a big HTML table (easy to parse out what you need at that
point)

2) Using OLE automation, a program you write can interact with Excel, request
data from a spreadsheet, and insert it.  Oracle Forms is capable of doing this
for example as is other languages environments.  In this fashion, you can remove
the "manual" and "sqlldr" parts -- your program can automatically insert the
data.

3) You can write a VB script that uses ODBC or Oracle Objects for OLE (OO4O) in
Excel.  This VB script would be able to put selected data from the spreadsheet
into the database.  We would recommend OO4O.  It provides an In-Process COM
Automation Server that provides a set of COM Automation interfaces/objects for
connecting to Oracle database servers, executing queries and managing the
results.  OO4O is available from

http://technet.oracle.com

0
 
LVL 48

Expert Comment

by:schwertner
ID: 10722118
One more option (Oracle 8.1.6) is to use Heterogenious Services Generic Connectivity to query .xls thru
ODBC connection.

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76960/hs_genco.htm#173
in the document "Oracle8i Distributed Database Systems" of the Oracle 8.1.6 Documentation


The best way to get data from Oracle to Excel is
through ODBC connection which is very easy to setup.
And then there are literally 3 lines of code that you
put right into your Excel file.
 This gets any data into Excel without any middle files
or Text files or etc. Meanwhile you
can ask your network Administrator to setup ODBC to your Oracle
database.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
ID: 10723173
@rehman:

there is a 3rd party API in JAVA that can read and load excel to oracle database.

do you know java?
0
 
LVL 3

Expert Comment

by:rajnadimpalli
ID: 10728767
How many excel files your are trying to handle?. If they are few 10s You can simply try using Microsoft Query which usually comes with MS Excel to load the data.

MS Excel -> Data -> Get External Data -> New database Query : This will give you save option...

-R
0
 

Author Comment

by:rehman123
ID: 10750831
actually i  dont want to use any third party tool. just i am trying and i want to populate oracle table with excel file using d2k. if some body have any exact solution pls  give me feedback otherwise  no need to give me any suggestion.

suppose
i have excel file like this
------------------------------
dept.xls

deptno
100
200
300
-----------------------------------------------
orcale table is like this

xldept
deptno varchar2(4);

now just tell me how to insert excel records in xldept using d2k.
0
 
LVL 21

Accepted Solution

by:
oleggold earned 500 total points
ID: 10752848
I think a have a perfect solution for You,an Excell macro,You even don't need to build a table in Oracle - just follow my instructions:
1.name Your sheet  as You named the table in DB
2.name the columns of Your sheet as You like at the first line and the datatypes of Your column at the second line .
3.Go to Tools,Macros-VB Editor
4.Find Module1 for Your workbook
5.in VB Editor goto Tools->References->Choose the Microsoft ActiveX Data Objects(the latest)
6.goto Module 1 and paste the following code:
Public Sub Ins2DB()

Dim cnn As ADODB.Connection
Dim strSql, TAB_NAME, colls, colsv, coll_list, coll_listv, ENV, OWNER, CONN_STRING, AUSER As String
Dim i, myRow, sht_idx
Set cnn = New ADODB.Connection
cnn.Provider = "MSDAORA.1"
cnn.Open ("Provider=MSDAORA.1;Password=Your_pwd;User ID=Your_User;Data Source=YourDB;Persist Security Info=True")
'TAB_NAME = Application.ActiveCell.Text
TAB_NAME = Application.ActiveSheet.Name

myRow = 3
sht_idx = 3
i = 1
'  cnDB.Provider = "MICROSOFT.JET.OLEDB.4.0"
'strSql = "create table " & TAB_NAME & "(" & Range(Cells(1, 1), Cells(1, 4)) & " )"
If ThisWorkbook.ActiveSheet.Cells(1, i).Text <> "" Then
strSql = "drop table " & TAB_NAME & " "
 cnn.Execute strSql, , adCmdText

Do While ThisWorkbook.Sheets(sht_idx).Cells(1, i) <> ""
If i <> 1 Then
colsv = "," & ThisWorkbook.ActiveSheet.Cells(1, i).Text
colls = "," & ThisWorkbook.ActiveSheet.Cells(1, i).Text & ThisWorkbook.ActiveSheet.Cells(2, i).Text Else
colls = ThisWorkbook.Sheets(sht_idx).Cells(1, i) & " number "
End If
coll_list = coll_list & colls
coll_listv = coll_listv & collsv
i = i + 1
Loop
strSql = "create table " & TAB_NAME & "(" & coll_list & ")"
 cnn.Execute strSql, , adCmdText
End If
'cnn.ConnectionString = "UID=disc;Pwd=disc;DataSource=test9i"
';database=otdwh"
' Datasource is a ODBC Datasource name, Database is your oracle Sysid, UID is username, Pwd is password

   Do While ThisWorkbook.Sheets(sht_idx).Cells(myRow, "A") <> ""
'  strSql = "delete from cstasks where task_id=" & CInt(ThisWorkbook.Sheets(sht_idx).Cells(myRow, "A"))
 '  cnn.Execute strSql, , adCmdText  "(" & coll_listv & ")
       strSql = "insert into " & TAB_NAME & _
 " values(" & _
  CInt(ThisWorkbook.Sheets(sht_idx).Cells(myRow, "A")) & ",'" & _
  ThisWorkbook.Sheets(sht_idx).Cells(myRow, "B") & "','" & _
   ThisWorkbook.Sheets(sht_idx).Cells(myRow, "C") & "','" & _
   ThisWorkbook.Sheets(sht_idx).Cells(myRow, "D") & "'," & _
  "'" & ThisWorkbook.Sheets(sht_idx).Cells(myRow, "E") &_
 "')"
       
       ' tbl_lk_cntrparty_id is a sample table name, Cntrparty_id, Cntryparty_nm is fields in the table
       ' Sheet1 is a worksheet which contains Data, sheet1.cell(1,"A") wil return left top corner cell in sheet1
              cnn.Execute strSql, , adCmdText
       ' This will write a record into the Oracle database
       myRow = myRow + 1
         Loop
7.Just replace the data in connection string cnn.Open ("Provider=MSDAORA.1;Password=Your_pwd;User ID=Your_User;Data Source=YourSRC;Persist Security Info=True") with Your credentials and continue the columns in the physical column list ( "'" & ThisWorkbook.Sheets(sht_idx).Cells(myRow, "E") &_)
just as You need.

You can use the following code:
Sub ins2shared()
Dim cnn As ADODB.Connection
Dim strSql, TAB_NAME, colls, colsv, coll_list, coll_listv, ENV, OWNER, CONN_STRING, AUSER As String
Dim i, myRow, sht_idx
Set cnn = New ADODB.Connection
cnn.Provider = "MSDAORA.1"
cnn.Open ("Provider=MSDAORA.1;Password=SHARED;User ID=SHARED;Data Source=dev;Persist Security Info

=True")
'TAB_NAME = Application.ActiveCell.Text
TAB_NAME = Application.ActiveSheet.Name

myRow = 2
sht_idx = 3
i = 1
'  cnDB.Provider = "MICROSOFT.JET.OLEDB.4.0"
'strSql = "create table " & TAB_NAME & "(" & Range(Cells(1, 1), Cells(1, 4)) & " )"
If ThisWorkbook.ActiveSheet.Cells(1, i).Text <> "" Then
strSql = "drop table " & TAB_NAME & " "
 cnn.Execute strSql, , adCmdText

Do While ThisWorkbook.Sheets(sht_idx).Cells(1, i) <> ""
If i <> 1 Then
colsv = "," & ThisWorkbook.ActiveSheet.Cells(1, i).Text
colls = "," & ThisWorkbook.ActiveSheet.Cells(1, i).Text & " number "
Else
colls = ThisWorkbook.Sheets(sht_idx).Cells(1, i) & " number "
End If
coll_list = coll_list & colls
coll_listv = coll_listv & collsv
i = i + 1
Loop
strSql = "create table " & TAB_NAME & "(" & coll_list & ")"
 cnn.Execute strSql, , adCmdText
End If
'cnn.ConnectionString = "UID=disc;Pwd=disc;DataSource=test9i"
';database=otdwh"
' Datasource is a ODBC Datasource name, Database is your oracle Sysid, UID is username, Pwd is

password

   Do While ThisWorkbook.Sheets(sht_idx).Cells(myRow, "A") <> ""
'  strSql = "delete from cstasks where task_id=" & CInt(ThisWorkbook.Sheets(sht_idx).Cells(myRow,

"A"))
 '  cnn.Execute strSql, , adCmdText  "(" & coll_listv & ")
       strSql = "insert into " & TAB_NAME & _
 " values(" & _
  CInt(ThisWorkbook.Sheets(sht_idx).Cells(myRow, "A")) & ",'" & _
  ThisWorkbook.Sheets(sht_idx).Cells(myRow, "B") & "','" & _
   ThisWorkbook.Sheets(sht_idx).Cells(myRow, "C") & "','" & _
   ThisWorkbook.Sheets(sht_idx).Cells(myRow, "D") & "'," & _
   ThisWorkbook.Sheets(sht_idx).Cells(myRow, "E") & "'," & _
   ThisWorkbook.Sheets(sht_idx).Cells(myRow, "F") & "'," & _
   ThisWorkbook.Sheets(sht_idx).Cells(myRow, "G") & "'," & _
   ThisWorkbook.Sheets(sht_idx).Cells(myRow, "H") & "'," & _
   ThisWorkbook.Sheets(sht_idx).Cells(myRow, "I") & "'," & _
   ThisWorkbook.Sheets(sht_idx).Cells(myRow, "J") & "'," & _
  "'" & ThisWorkbook.Sheets(sht_idx).Cells(myRow, "K") & "')"
       
       ' tbl_lk_cntrparty_id is a sample table name, Cntrparty_id, Cntryparty_nm is fields in the

table
       ' Sheet1 is a worksheet which contains Data, sheet1.cell(1,"A") wil return left top corner

cell in sheet1
              cnn.Execute strSql, , adCmdText
       ' This will write a record into the Oracle database
       myRow = myRow + 1
         Loop
End Sub
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 115
PL SQL Search Across Columns 4 52
create a nested synonym 4 28
Email query results in HTML 6 29
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question