Recreating NTILE function on running SQL in Excel

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

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

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.

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 Sub

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.
 
Last edited:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

vijnanamatrata

New Member
Joined
Oct 30, 2012
Messages
2
Can anyone help me? Simply copy the VBA code to one module, then copy SQL script to cell A1 in a sheet with code name shSQLList, and add another sheet with code name shResult. Finally, run the sub Test and you will see the error.

Please let me know how can I make SQL script run smoothly in Excel. Thank you in advance.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top