I am struggling to find out how I can 1) import an Excel table into my schema and 2) import SQL script to run automatically. I recently upgraded from an old version (TOAD 8.0.0.47) to TOAD 12.8; I knew how to do it in the old version, but I am struggling with the new one.
-
go to Automation Designer (the toolbar icon for it looks like a lightning bold with a pencil). You can set up an “Import Table Data” action there. Once it’s set up you can rt-click it to run it, or to run from command line, the command would be something like “Toad.exe –a “Import Table Data1”” (or whatever you named the action).
-
I’m not sure what you mean by “run automatically”. If you mean by command line - there is an “Execute Script” action that can be set up in the automation designer and run from command line in a similar way to executing the data import from command line. If you meant something else, let us know.
Thanks…I’ll give it a shot!
_________________________________________
Robert J. Casaletta
Nissan North America, Inc.
Senior Analyst
Warranty Department
robert.casaletta@nissan-usa.com
Phone: +1 615.725.0973
Mobile: +1 615.720.6501
eFax: +1.615.967.2151
From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]
Sent: Wednesday, February 10, 2016 1:46 PM
Subject: RE: [Toad for Oracle - Discussion Forum] Import Excel Table & SQL script
RE: Import Excel Table & SQL script
Reply by John Dorlon
-
go to Automation Designer (the toolbar icon for it looks like a lightning bold with a pencil). You can set up an “Import Table Data” action there. Once it’s set up you can rt-click it to run it, or to run from command line, the command would be something like “Toad.exe –a “Import Table Data1”” (or whatever you named the action).
-
I’m not sure what you mean by “run automatically”. If you mean by command line - there is an “Execute Script” action that can be set up in the automation designer and run from command line in a similar way to executing the data import from command line. If you meant something else, let us know.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
By automation, I would like to run a series of scripts at different times. Can this be done?
_________________________________________
Robert J. Casaletta
Nissan North America, Inc.
Senior Analyst
Warranty Department
robert.casaletta@nissan-usa.com
Phone: +1 615.725.0973
Mobile: +1 615.720.6501
eFax: +1.615.967.2151
From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]
Sent: Wednesday, February 10, 2016 1:46 PM
Subject: RE: [Toad for Oracle - Discussion Forum] Import Excel Table & SQL script
RE: Import Excel Table & SQL script
Reply by John Dorlon
-
go to Automation Designer (the toolbar icon for it looks like a lightning bold with a pencil). You can set up an “Import Table Data” action there. Once it’s set up you can rt-click it to run it, or to run from command line, the command would be something like “Toad.exe –a “Import Table Data1”” (or whatever you named the action).
-
I’m not sure what you mean by “run automatically”. If you mean by command line - there is an “Execute Script” action that can be set up in the automation designer and run from command line in a similar way to executing the data import from command line. If you meant something else, let us know.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
Yes. If you right-click an action once you set it up in the Automation Designer, one of the choices is “Schedule”. So you can schedule it that way.
If you have several scripts to run, but only want to schedule one script to run them all, you can make a “master” script that looks like:
@path_to_script1.sql;
@path_to_script2.sql;
@path_to_script3.sql;
etc…
Thanks again…
_________________________________________
Robert J. Casaletta
Nissan North America, Inc.
Senior Analyst
Warranty Department
robert.casaletta@nissan-usa.com
Phone: +1 615.725.0973
Mobile: +1 615.720.6501
eFax: +1.615.967.2151
From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]
Sent: Wednesday, February 10, 2016 2:38 PM
Subject: RE: [Toad for Oracle - Discussion Forum] Import Excel Table & SQL script
RE: Import Excel Table & SQL script
Reply by John Dorlon
Yes. If you right-click an action once you set it up in the Automation Designer, one of the choices is “Schedule”. So you can schedule it that way.
If you have several scripts to run, but only want to schedule one script to run them all, you can make a “master” script that looks like:
@path_to_script1.sql;
@path_to_script2.sql;
@path_to_script3.sql;
etc…
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
if you want I wrote this VBA code, this will create a sql file with create and insert statements for the xls.
open the xls, create a new macro, paste entire code, run sql. There are a few global vars on top to change what gets exportes, take a look.
Option Explicit
Public Const FileName = “c:\P4\sql\forex-sql-9.8.2\data\br.sql”
Public Const Delete As Boolean = True
Public Const create As Boolean = True
Public Const spool As Boolean = False
Public Const CheckBadChar As Boolean = True
Public Const RemoveBadChar As Boolean = True
Public Const OneFile As Integer = 0 '0 current sheet,1 all sheets 1 file, 2 all sheets per file
Public Const OneFileName As String = “zbr”
Public Const TablePrefix As String = “z”
Public Const AutoFilterModeoff As Boolean = True
Public Const skipSheets = “products,clearers,venues,cut”
Sub sql()
Dim i As Integer
Dim MyFile As String
Dim TblName As String
Dim FirstFile As Boolean: FirstFile = True
Dim FldPath As String
Dim myCols As String
Dim oShell As Object
If Len(FileName) = 0 Then
Application.FileDialog(msoFileDialogFolderPicker).Show
If (Application.FileDialog(msoFileDialogFolderPicker).SelectedItems.Count = 0) Then
Exit Sub
End If
FldPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & “”
End If
Select Case OneFile
Case 0
MyFile = getFileName(FldPath & TablePrefix & Replace(ActiveSheet.Name, " ", “_”) & “.sql”)
Open MyFile For Output As #1
TblName = TablePrefix & Replace(ActiveSheet.Name, " ", “_”)
If AutoFilterModeoff Then
Worksheets(ActiveSheet.Name).AutoFilterMode = False
End If
selectsheet True, ActiveSheet.Name
myCols = header(Selection, spool, create, TblName, ActiveSheet.Name, True)
data Selection, spool, create, TblName, myCols, ActiveSheet.Name
If spool Then
Print #1, “spool off”
End If
Close #1
Application.StatusBar = MyFile & " created!"
Set oShell = CreateObject(“Wscript.Shell”)
oShell.Run (MyFile)
Case 1
MyFile = getFileName(FldPath & OneFileName & “.sql”)
Open MyFile For Output As #1
TblName = OneFileName
Worksheets(ActiveWorkbook.Worksheets(1).Name).Activate
selectsheet FirstFile, ActiveWorkbook.Worksheets(1).Name
myCols = header(Selection, spool, create, TblName, ActiveWorkbook.Worksheets(1).Name, True)
For i = 1 To ActiveWorkbook.Worksheets.Count
If InStr(1, UCase(skipSheets), UCase(ActiveWorkbook.Worksheets(i).Name), 1) = 0 Then
Worksheets(ActiveWorkbook.Worksheets(i).Name).Activate
If AutoFilterModeoff Then
Worksheets(ActiveWorkbook.Worksheets(1).Name).AutoFilterMode = False
End If
selectsheet FirstFile, ActiveWorkbook.Worksheets(i).Name
data Selection, spool, create, TblName, myCols, ActiveWorkbook.Worksheets(i).Name
FirstFile = False
End If
Next i
Print #1, “commit;”
If spool Then
Print #1, “spool off”
End If
Close #1
Application.StatusBar = MyFile & " created!"
Set oShell = CreateObject(“Wscript.Shell”)
oShell.Run (MyFile)
Case 2
For i = 1 To ActiveWorkbook.Worksheets.Count
If InStr(1, UCase(skipSheets), UCase(ActiveWorkbook.Worksheets(i).Name), 1) = 0 Then
Worksheets(ActiveWorkbook.Worksheets(i).Name).Activate
If AutoFilterModeoff Then
Worksheets(ActiveWorkbook.Worksheets(1).Name).AutoFilterMode = False
End If
MyFile = getFileName(FldPath & TablePrefix & Replace(ActiveSheet.Name, " ", “_”) & “.sql”)
Open MyFile For Output As #1
TblName = TablePrefix & Replace(ActiveSheet.Name, " ", “_”)
selectsheet FirstFile, ActiveWorkbook.Worksheets(i).Name
myCols = header(Selection, spool, create, TblName, ActiveWorkbook.Worksheets(i).Name, True)
data Selection, spool, create, TblName, myCols, ActiveWorkbook.Worksheets(i).Name
If spool Then
Print #1, “spool off”
End If
Close #1
Application.StatusBar = MyFile & " created!"
Set oShell = CreateObject(“Wscript.Shell”)
oShell.Run (MyFile)
End If
Next i
End Select
MsgBox " Done!"
Application.StatusBar = “Done”
End Sub
Sub selectsheet(goHeader As Boolean, sheet)
Dim rng As Range, cellValue As Variant, i As Integer, j As Integer, myStr As String, myCols As String, mytbl As String, fileSaveName As String, row As String, oShell As Object, rowcount As Integer
Dim mylastcell
Dim mylastcelladd
Dim myrange
Range(“A1”).Select
On Error Resume Next
Set mylastcell = Cells(1, 1).SpecialCells(xlLastCell)
mylastcelladd = Cells(mylastcell.row, mylastcell.Column).Address
myrange = “A1:” & mylastcelladd
Range(myrange).Select
Selection.Copy
End Sub
Sub data(rng, spool, create, TblName, myCols, sheet)
Dim rowcount, rownum, myStr, row, j, cellValue
rowcount = 0
For rownum = 2 To rng.Rows.Count
Application.StatusBar = "Exporting row " & rownum & “/” & rng.Rows.Count
myStr = " values (" & rownum - 1 & “,”
If sheet <> “” Then
myStr = myStr & “’” & sheet & “’,”
End If
row = “”
For j = 1 To rng.Columns.Count
If Application.WorksheetFunction.IsNA(rng.Cells(rownum, j).Value) Then
cellValue = “”
Else
cellValue = rng.Cells(rownum, j).Value
End If
cellValue = Replace(cellValue, Chr(39), Chr(39) & Chr(39))
cellValue = Replace(cellValue, Chr(12), “’||Chr(12)||’”)
cellValue = Replace(cellValue, Chr(13), “’||Chr(13)||’”)
cellValue = Replace(cellValue, Chr(10), “’||Chr(10)||’”)
cellValue = Replace(cellValue, Chr(160), “”)
cellValue = Trim(cellValue)
If CheckBadChar Then
cellValue = check_Asc(cellValue, CStr(rownum) + " " + CStr(j))
myStr = myStr
End If
row = row & Trim(cellValue)
If j = rng.Columns.Count Then
myStr = myStr & “’” & cellValue & “’);”
Else
myStr = myStr & “’” & cellValue & “’,”
End If
Next j
If row <> “” Then
rowcount = rowcount + 1
Print #1, myCols & myStr
End If
Next rownum
If OneFile <> 1 Then
'Print #1, “set term on”
Print #1, “commit;”
Print #1, "SELECT CASE WHEN COUNT() = " & rowcount & " THEN COUNT() ||’ Rows import succesfully’ ELSE ‘ORA-20000 Errors in inport, XLS: " & rowcount & ", DB:’||COUNT(*) END msg FROM " & TblName & “;”
End If
End Sub
Function header(rng, spool, create, TblName, sheet, goHeader As Boolean)
Dim mytbl, myCols, j, cellValue, rownum, colLenTmp, colLen
mytbl = "create table " & TblName & “(row_num number(12),” & Chr(10)
If sheet <> “” Then
mytbl = mytbl & " sheet varchar2(30), " & Chr(10)
End If
myCols = “insert into " & TblName & " (row_num,”
If sheet <> “” Then
myCols = colName(myCols) & " sheet, "
End If
For j = 1 To rng.Columns.Count
Application.StatusBar = "Analyzing Column " & j & “/” & rng.Columns.Count
cellValue = colName(Replace(Trim(rng.Cells(1, j).Value), " ", “_”))
If Trim(cellValue) = “” Then
cellValue = “col_” & j
End If
If OneFile <> 1 Then
For rownum = 2 To rng.Rows.Count
Application.StatusBar = CStr(rownum) + " " + CStr(j)
If Not Application.WorksheetFunction.IsNA(rng.Cells(rownum, j).Value) Then
colLenTmp = Len(rng.Cells(rownum, j).Value)
End If
If colLenTmp > colLen Then
colLen = colLenTmp
End If
Next rownum
Else
colLen = 1000
End If
If j = rng.Columns.Count Then
myCols = myCols & cellValue & “)”
mytbl = mytbl & " " & colName(cellValue) & " varchar2(" & colLen & “)” & Chr(10)
Else
myCols = myCols & cellValue & “,”
mytbl = mytbl & " " & colName(cellValue) & " varchar2(" & colLen & “),” & Chr(10)
End If
Next j
Print #1, “–set term off”
If goHeader Then
Print #1, "–FileName: " & ActiveWorkbook.Name
Print #1, "–Path: " & ActiveWorkbook.FullName
Print #1, “set define off”
Print #1, “set echo on”
If spool Then
Print #1, “spool c:\tmp.log”
End If
If create Then
Print #1, “begin execute IMMEDIATE ‘drop table " & TblName & "’; EXCEPTION WHEN OTHERS THEN null; END;”
Print #1, “/”
End If
If create Then
Print #1, mytbl & “);”
End If
'Print #1, “WHENEVER SQLERROR EXIT SQL.SQLCODE”
If Delete Then
Print #1, "delete from " & TblName & “;”
End If
End If
header = myCols
End Function
Function getFileName(pass)
If OneFile = 2 Then
getFileName = FileName + pass
Else
If Len(FileName) > 0 Then
getFileName = FileName
Else
getFileName = pass
End If
End If
End Function
Function check_Asc(s, where)
Dim i As Integer
Dim c2 As String
Dim ascInt As Integer
Dim Str As String
For i = 1 To Len(s)
c2 = Mid(s, i, 1)
ascInt = Asc(Mid(s, i, 1))
If RemoveBadChar Then
If (ascInt <= 127) Then
Str = Str & c2
End If
Else
If ascInt <> 239 And ascInt <> 188 And ascInt <> 154 And ascInt <> 194 And ascInt <> 150 And ascInt <> 226 And ascInt <> 128 And ascInt <> 147 And ascInt <> 146 And ascInt <> 201 And ascInt <> 233 And ascInt <> 191 And ascInt <> 189 And ascInt <> 153 Then
If (ascInt > 127) Then
MsgBox ("Cell has bad data " & Chr(10) & Chr(10) & s & "\n has a " & Chr(10) & Chr(10) & "Character: " & c2 & Chr(10) & Chr(10) & "Cell: " & where & Chr(10) & Chr(10) & "asc: " & ascInt)
Str = Str & “BAD!!!”
Else
Str = Str & c2
End If
End If
End If
Next i
If Not RemoveBadChar Then
Str = Replace(Str, Chr(239), “”)
Str = Replace(Str, Chr(188), “”)
Str = Replace(Str, Chr(154), “”)
Str = Replace(Str, Chr(194), “”)
Str = Replace(Str, Chr(226), “”)
Str = Replace(Str, Chr(128), “”)
Str = Replace(Str, Chr(147), “”)
Str = Replace(Str, Chr(150), “-”)
Str = Replace(Str, Chr(146), “’”)
End If
check_Asc = Str
End Function
Function colName(s)
Dim s2
s2 = s
If IsNumeric(Mid(s, 1, 1)) Then
s2 = “N” & s2
End If
colName = s2
End Function