Toad World® Forums

Import Excel Table & SQL script

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.

  1. 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).

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

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Import Excel Table & SQL script

RE: Import Excel Table & SQL script

Reply by John Dorlon

  1. 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).

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

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Import Excel Table & SQL script

RE: Import Excel Table & SQL script

Reply by John Dorlon

  1. 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).

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

To: toadoracle@toadworld.com

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