vijnanamatrata
New Member
- Joined
- Oct 30, 2012
- Messages
- 2
I am making a conversion from traditional way of creating reports in Excel, and I have been stuck on recreating NTILE function when running SQL in Excel (because NTILE function does not exist there), even though I have succeeded in recreating NTILE function when running in Oracle PL/SQL Developer.
Traditional way:
- SQL scripts are stored in an Excel sheet.
- I write macro to connect to Oracle database, then open a recordset with SQL scripts, and copy that recordset to another Excel sheet.
New way:
- SQL scripts are stored in an Excel sheet.
- A table in Oracle database is saved as a csv file on a hard disk.
- I write macro to connect to the csv file (not open it) using Microsoft.Jet.OLEDB.4.0, then open a record set with SQL scripts, and copy that recordset to another Excel sheet.
In the new way, SQL scripts are modified because some functions and statements in Oracle do not exist when running SQL in Excel (example: NTILE, REMAINDER, TRUNC, CASE). NTILE is rewritten; REMAINDER becomes MOD; TRUNC becomes INT; CASE becomes IIF.
Below is my SQL script of:
1. Creating a sample table
2. NTILE result
3. Recreating NTILE function in Oracle (as intermediate step)
4. Recreating NTILE function in Excel SQL
In summary, SQL script in the traditional way is the part of -- desired NTILE result above.
And SQL script in the new way is the part of -- Recreating NTILE function in Excel SQL above.
Make sure that file testntile.csv is available as description of testntile table above.
Finally, here are VBA sheets, subs and functions.
For sheets I use their code name, not sheet name.
- shSQLList : contain SQL script (of new way) in cell A1
- shResult : store result data
For subs and functions, just put them all in a module.
While I can run SQL script of recreating NTILE function in Oracle PL/SQL Developer successfully (same result as NTILE function), I always get error when running SQL script in Excel, even though I tried to shorten SQL statement to only one simple SELECT IIf( ).
I have spent a lot of time on this task but without success. Please help me and I really appreciate your kind support.
Traditional way:
- SQL scripts are stored in an Excel sheet.
- I write macro to connect to Oracle database, then open a recordset with SQL scripts, and copy that recordset to another Excel sheet.
New way:
- SQL scripts are stored in an Excel sheet.
- A table in Oracle database is saved as a csv file on a hard disk.
- I write macro to connect to the csv file (not open it) using Microsoft.Jet.OLEDB.4.0, then open a record set with SQL scripts, and copy that recordset to another Excel sheet.
In the new way, SQL scripts are modified because some functions and statements in Oracle do not exist when running SQL in Excel (example: NTILE, REMAINDER, TRUNC, CASE). NTILE is rewritten; REMAINDER becomes MOD; TRUNC becomes INT; CASE becomes IIF.
Below is my SQL script of:
1. Creating a sample table
2. NTILE result
3. Recreating NTILE function in Oracle (as intermediate step)
4. Recreating NTILE function in Excel SQL
Code:
CREATE TABLE testntile
(
appid NUMBER(2),
sample VARCHAR2(50),
score NUMBER(3)
);
INSERT ALL
INTO testntile (appid, sample, score) VALUES (5, 'A', 4)
INTO testntile (appid, sample, score) VALUES (2, 'B', 6)
INTO testntile (appid, sample, score) VALUES (3, 'C', 8)
INTO testntile (appid, sample, score) VALUES (1, 'C', 1)
INTO testntile (appid, sample, score) VALUES (4, 'B', 2)
INTO testntile (appid, sample, score) VALUES (8, 'C', 3)
INTO testntile (appid, sample, score) VALUES (6, 'C', 8)
INTO testntile (appid, sample, score) VALUES (9, 'B', 9)
INTO testntile (appid, sample, score) VALUES (7, 'C', 7)
INTO testntile (appid, sample, score) VALUES (10, 'B', 5)
INTO testntile (appid, sample, score) VALUES (14, 'A', 2)
INTO testntile (appid, sample, score) VALUES (12, 'C', 7)
INTO testntile (appid, sample, score) VALUES (13, 'C', 7)
INTO testntile (appid, sample, score) VALUES (11, 'A', 9)
INTO testntile (appid, sample, score) VALUES (15, 'C', 3)
INTO testntile (appid, sample, score) VALUES (16, 'C', 12)
INTO testntile (appid, sample, score) VALUES (18, 'C', 12)
INTO testntile (appid, sample, score) VALUES (17, 'C', 16)
INTO testntile (appid, sample, score) VALUES (19, 'C', 12)
INTO testntile (appid, sample, score) VALUES (20, 'D', 14)
INTO testntile (appid, sample, score) VALUES (21, 'D', 11)
SELECT * FROM dual;
COMMIT;
[I]-- desired NTILE result[/I]
SELECT
NTILE(3) OVER (PARTITION BY sample ORDER BY score) RangeList,
s.*
FROM testntile s;
[I]-- Recreating NTILE function in Oracle[/I]
[I]-- (as intermediate step to recreate NTILE function in Excel SQL)[/I]
SELECT
CASE WHEN REMAINDER(
(SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample)
,3) <> 0
THEN CASE WHEN ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3) < 1
THEN (SELECT COUNT(*) +1
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
)
ELSE 1 + TRUNC((SELECT COUNT(*)
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
) / ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3))
END
ELSE CASE WHEN ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3) = 1
THEN (SELECT COUNT(*) +1
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
)
ELSE 1 + TRUNC((SELECT COUNT(*)
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
) / ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3))
END
END RangeList,
t1.appid,
t1.sample,
t1.score
FROM
testntile t1
ORDER BY
t1.sample,
t1.score,
t1.appid
[I]-- Recreating NTILE function in [/I][I]Excel SQL[/I]
SELECT
IIf(MOD(
(SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample)
,3) <> 0
, IIf( ((SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample) / 3) < 1
, (SELECT COUNT(*) +1
FROM &Selected_Table t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
)
, 1 + INT((SELECT COUNT(*)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
) / ((SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample) / 3))
)
, IIf( ((SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample) / 3) = 1
, (SELECT COUNT(*) +1
FROM &Selected_Table t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
)
, 1 + INT((SELECT COUNT(*)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
) / ((SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample) / 3))
)
) RangeList,
t1.appid,
t1.sample,
t1.score
FROM
&Selected_Table t1
ORDER BY
t1.sample,
t1.score,
t1.appidIn summary, SQL script in the traditional way is the part of -- desired NTILE result above.
And SQL script in the new way is the part of -- Recreating NTILE function in Excel SQL above.
Make sure that file testntile.csv is available as description of testntile table above.
Finally, here are VBA sheets, subs and functions.
For sheets I use their code name, not sheet name.
- shSQLList : contain SQL script (of new way) in cell A1
- shResult : store result data
For subs and functions, just put them all in a module.
Code:
Option Explicit
Public strPath As String
Public strTable As String
Public Contn As New ADODB.Connection
Sub test()
Dim SQL As String
Dim rRange As Range
'let the user choose files needed for making a report
strPath = BrowseFileExplorer(, , ThisWorkbook.Path)
If strPath = vbNullString Then 'user cancelled
MsgBox "No file selected."
Else 'user picked a file
strPath = RemoveTrailingSlash(strPath)
strTable = Right(strPath, Len(strPath) - InStrRev(strPath, "\"))
'if file name contains underscore, ask the user to remove underscore
If InStr(1, strTable, "_", vbTextCompare) Then
MsgBox "SQL in Excel cannot run with underscore(s) in file name." & vbCrLf & _
"Please remove all underscores from the csv file, then run the macro again.", _
vbCritical + vbOKOnly, "Error"
Exit Sub
End If
End If
Application.ScreenUpdating = False
SQL = ""
Set rRange = Nothing
SQL = shSQLList.Range("A1").Value
SQL = Replace(SQL, "&Selected_Table", "[" & strTable & "]")
'table name needs enclosing in [ ] if it has space or special characters
'to run SQL in Excel, table name cannot contain underscore
Set rRange = shResult.Range("A1")
Call ImportCSVfile(SQL, strPath, rRange.Cells(1, 1))
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Function BrowseFileExplorer(Optional DialogTitle As String = "Select a file", _
Optional ViewType As Office.MsoFileDialogView = MsoFileDialogView.msoFileDialogViewSmallIcons, _
Optional InitialDirectory As String) As String
'Other option:
'ViewType As Office.MsoFileDialogView = msoFileDialogViewList
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.Title = DialogTitle
.InitialView = ViewType
.ButtonName = "&Select"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "CSV", "*.csv"
If Dir(InitialDirectory, vbDirectory) <> vbNullString Then
If Right(InitialDirectory, 1) <> "\" Then
InitialDirectory = InitialDirectory & "\"
End If
.InitialFileName = InitialDirectory
Else
.InitialFileName = CurDir
End If
If .Show = True Then
' user picked a file
BrowseFileExplorer = .SelectedItems(1)
Else
' user cancelled
BrowseFileExplorer = vbNullString
End If
End With
End Function
Public Function TrailingSlash(strFolder As String) As String
If Len(strFolder) > 0 Then
If Right(strFolder, 1) = "\" Then
TrailingSlash = strFolder
Else
TrailingSlash = strFolder & "\"
End If
End If
End Function
Public Function RemoveTrailingSlash(strFile As String) As String
If Len(strFile) > 0 Then
If Right(strFile, 1) = "\" Then
RemoveTrailingSlash = Left(strFile, Len(strFile) - 1)
Else
RemoveTrailingSlash = strFile
End If
End If
End Function
Sub ImportCSVfile(SQL As String, sPath As String, Destination As Range)
'---------------------------------------------------------------------------------------
' NOTE: Requires reference to ADO library:
' 1. Open the Visual Basic Editor (Alt + Fll)
' 2. Choose Tools | References
' 3. Select the checkbox for Microsoft ActiveX Data Object 2.5 Library (or higher)
'---------------------------------------------------------------------------------------
Dim RcdSet As ADODB.Recordset
Dim fldRS As ADODB.Field
Dim i As Integer
Set Contn = New ADODB.Connection
'for before Excel 2007 to open .xls file
' Contn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"
'for Excel 2007 or later to open .xls file
' Contn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & sPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"
'for Excel 2007 or later to open .xlsx file
' Contn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & sPath & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
'for all Excel versions to open .csv file
'get the folder path with trailing slash
sPath = Left(sPath, InStrRev(sPath, "\", , vbTextCompare))
Contn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & ";" & _
"Extended Properties=""Text;HDR=YES;FMT=Delimited"";"
'HDR=Yes indicates that the first row contains column names, not data
Set RcdSet = New ADODB.Recordset
'If you are using an SQL statement with an ADO recordset,
'make sure that the final parameter for the Open method is adCmdText (not adCmdTable)
RcdSet.Open SQL, Contn, CursorTypeEnum.adOpenForwardOnly, _
LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText
Destination.CopyFromRecordset RcdSet
RcdSet.Close
Set RcdSet = Nothing
Contn.Close
Set Contn = Nothing
End SubWhile I can run SQL script of recreating NTILE function in Oracle PL/SQL Developer successfully (same result as NTILE function), I always get error when running SQL script in Excel, even though I tried to shorten SQL statement to only one simple SELECT IIf( ).
I have spent a lot of time on this task but without success. Please help me and I really appreciate your kind support.
Last edited:
