MS SQL Excel of Excel Files, Case Statements or Alternative

Question

I have an excel file I'm using to process data. It comes in via a .csv file and I wanted to output quite a few different sheets each having processed the data in different ways. Once the initial setup is complete, the person executing this task will not be me. After exploring many methods of doing this, (setting up an SQL or Access database for example/using excel functions that read and processed the data) I settled on an Excel file querying itself after importing the .csv.

I found out how using the following method: How to run a SQL query on an Excel table?

The dialect of SQL was unfamiliar to me, and the only reference I could find was: https://support.microsoft.com/en-us/help/136699/description-of-the-usage-of-joins-in-microsoft-query Which works in most cases. In some instances, Jet SQL works but other times it returns errors.

I have three columns in the table, Cust Status, 'MinDateFiledBorrower' & 'MinDateFiledCoBorrower'. Either or both of the Date fields may have data in them, or either may be NULL. I am attempting to get the Minimum date of both the Borrower and the CoBorrower. I can't seem to find syntax for Case Statements in JetSQL, and the errors I'm receiving on my attempts lead me to suspect they aren't supported.

The data:

Cust Status    'MinDateFiledBorrower'    'MinDateFiledCoBorrower'
B-1001         9/15/2004 0:00            11/1/2006 0:00
B-1002         9/17/2004 0:00            11/9/2006 0:00
B-1003         10/7/2004 0:00            NULL 
B-1004         NULL                      10/14/2004 0:00    
B-1005         9/23/2004 0:00            12/21/2006 0:00
B-1007         10/19/2004 0:00           2/12/2007 0:00
B-1008         10/22/2004 0:00           2/8/2007 0:00
B-101          NULL                      12/11/2001 0:00    
B-1010         10/25/2004 0:00           NULL
B-1011         10/28/2004 0:00           6/8/2007 0:00
B-1012         11/4/2004 0:00            6/28/2007 0:00
B-2298         5/12/2005 0:00            NULL
B-23           NULL                      2/26/2007 0:00
B-230          NULL                      5/15/2012 0:00
B-2300         NULL                      5/24/2005 0:00
B-2301         NULL                      6/30/2005 0:00

My Most recent attempt:

select
    `DischargeCombine$`.`Cust Status`
    ,`DischargeCombine$`.`'MinDateFiledBorrower'`
    ,`DischargeCombine$`.`'MinDateFiledCoBorrower'`
    ,case when `DischargeCombine$`.`'MinDateFiledBorrower'` is null then `DischargeCombine$`.`'MinDateFiledCoBorrower'`
    when `DischargeCombine$`.`'MinDateFiledCoBorrower'` is null then `DischargeCombine$`.`'MinDateFiledBorrower'`
    when `DischargeCombine$`.`'MinDateFiledBorrower'`>=`DischargeCombine$`.`'MinDateFiledCoBorrower'` then `DischargeCombine$`.`'MinDateFiledCoBorrower'`
    when `DischargeCombine$`.`'MinDateFiledBorrower'`<`DischargeCombine$`.`'MinDateFiledCoBorrower'` then `DischargeCombine$`.`'MinDateFiledBorrower'` end as FirstDischarge
from `C:\Query Exports\BK Scrub Processing File.xlsx`.`DischargeCombine$` `DischargeCombine$`

Returns the error "Didn't expect 'DischargeCombine$' after the SELECT column list." It doesn't have line markers, but I'm guessing it means the first DischargeCombine$ of the Case Statement.

Any assistance will be greatly appreciated!

Interesting you bypassed MS Access for an Excel solution when Access can read in CSV and Excel files and natively run SQL queries on its own tables. In fact, query workbooks is the *very* same engine as querying Access databases! — Parfait, Aug 07 '19 at 16:26
Back ticks are not supported by Jet driver I think. Did you instead try using square brackets? Probably it would be much better if you get the data into SQL Server, SQLite, postgreSQL ... If you would go on with access engine's SQL then at least you could use ACE driver instead of Jet (still it would be hard to constitute a query as you like). — Cetin Basoz, Aug 07 '19 at 16:27

score 1 · Accepted Answer · answered Aug 07 '19 at 17:20

MS Access Jet/ACE SQL Engine does not support the ANSI-92 CASE statement of which I raised a ticket among other missing ANSI methods to the dialect.

Instead of CASE, consider a nested IIF function for conditional logic. Below adjustment also uses a short table alias to avoid the long, repeating identifier:

select
     d.`Cust Status`
    , d.`'MinDateFiledBorrower'`
    , d.`'MinDateFiledCoBorrower'`
    , iif(d.`'MinDateFiledBorrower'` is null, d.`'MinDateFiledCoBorrower'`,
          iif(d.`'MinDateFiledCoBorrower'` is null, d.`'MinDateFiledBorrower'`,
              iif( d.`'MinDateFiledBorrower'` >= d.`'MinDateFiledCoBorrower'`, d.`'MinDateFiledCoBorrower'`, 
                  iif(d.`'MinDateFiledBorrower'` < d.`'MinDateFiledCoBorrower'`, d.`'MinDateFiledBorrower'`, null
                     )
                 )
             )
        ) as FirstDischarge
from `C:\Query Exports\BK Scrub Processing File.xlsx`.`DischargeCombine$`  d

Alternatively, consider SWITCH (an Access SQL specific method borrowed from VBA):

select
     d.`Cust Status`
    , d.`'MinDateFiledBorrower'`
    , d.`'MinDateFiledCoBorrower'`
    , switch(d.`'MinDateFiledBorrower'` is null, d.`'MinDateFiledCoBorrower'`,
             d.`'MinDateFiledCoBorrower'` is null, d.`'MinDateFiledBorrower'`,
             d.`'MinDateFiledBorrower'` >= d.`'MinDateFiledCoBorrower'`, d.`'MinDateFiledCoBorrower'`, 
             d.`'MinDateFiledBorrower'` < d.`'MinDateFiledCoBorrower'`, d.`'MinDateFiledBorrower'`
            ) as FirstDischarge
from `C:\Query Exports\BK Scrub Processing File.xlsx`.`DischargeCombine$`  d

As an aside, MS Access is actually a GUI to the Jet/ACE engine (.dll files) which is what you use to query Excel workbooks and can also query native Jet/ACE tables, and even query CSV files. In fact, one does not need the MSAccess.exe program installed to query .mdb or .accdb and likewise .xls, .xlsx, .xlsm, .xlsb files.

This is exactly what I'm looking for. Thank you! I am running into other issues where the excel file is throwing errors when I try to use an alias, or a subgroup. It wasn't doing that originally, perhaps too many queries on too many of it's own sheets. I think I'll try MS Access again. — Golden Ratio, Aug 08 '19 at 18:49

MS SQL Excel of Excel Files, Case Statements or Alternative

1 Answers1