Solved

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

Posted on 2004-03-31
7
9,982 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
[X]
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
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

622 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