|
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
|