Quick intro: Execute SQL queries against Excel

@Suniel gave me this idea with his post here. It seems to that using the power of SQL combined with the power of Excel might open up a world of possibilities. I’ve barely scratched the surface.

The query here returns the rows where the Department name is ‘Hospitality’. Then it converts the rows into ad hoc CSV.

I’ve included some links that were useful to me in the code.

Excel sheet I used as a guinea pig (obviously real data :smiling_imp::):

OfficeSheet

Code screenshot:

Code:

Database.Connect                ConnectionString:  "Provider=Microsoft.ACE.OLEDB.16.0; \
                                Data Source=C:\Users\YourNameHere\Documents\Robin\db\RobinXL.xlsx; \
                                Extended Properties='Excel 12.0 Xml;HDR=YES';" \
                                Connection=> SQLConnection
# Connect to Excel 
# See e.g. https://www.connectionstrings.com/excel/

Database.Execute                Connection:  SQLConnection \
                                Statement:  "SELECT * from [Office$]  Where [Department] = 'Hospitality'"\
                                Timeout:30 \
                                Result=> QueryResult

# Set a counter variable to take index-based operations into account
set x to QueryResult.rows.count -1
# Set a string variable
set str to ""
# Console.Write Message: # For debugging

loop i from 0 to x # outer loop
    loop j from 0 to 4 # inner loop
        if j = 4 then # last-column manipulation loop
            set str to str + QueryResult[i][j] # gets the column value
            Text.AppendLine Text:  str LineToAppend:'' Result=> str # if it's the last column, add a newline
        else
            set str to str + QueryResult[i][j] + "," # get column value, add a comma, i.e. ad hoc CSV
        end # end 
    end
end
Console.Write Message: str

Database.Close                  Connection: SQLConnection
# SQL queries against an Excel sheet may add lots of functionality.
# See e.g. 
# https://querysurge.zendesk.com/hc/en-us/articles/205766136-Writing-SQL-Queries-against-Excel-files-Excel-SQL-

The result:

excel-result

Regards,
burque505

5 Likes

@burque505 great!! Thank You!

1 Like