Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10161
  • Last Modified:

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

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
rehman123
Asked:
rehman123
1 Solution
 
riazpkCommented:
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
 
riazpkCommented:
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
 
schwertnerCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
seazodiacCommented:
@rehman:

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

do you know java?
0
 
rajnadimpalliCommented:
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
 
rehman123Author Commented:
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
 
oleggoldCommented:
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now