Solved

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

Posted on 2004-03-31
7
9,781 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 47

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

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.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now