Connecting to Microsoft Excel Online via SQL

You can use SQL to connect to Microsoft Excel Online in the Data Virtuality Studio or the Code Editor in the Web UI.

Pre-requisites

You need Client Id, Client Secret, and Refresh Token.

For information on how to obtain the Client Id and Client Secret, please refer to the official documentation.

How to Connect

Run the following statements in the Data Virtuality Studio or the Web UI (remember to enter your credentials):

SQL

CALL SYSADMIN.createConnection (
    name => 'excel_online_src'
    ,jbossCLITemplateName => 'ws'
    ,connectionOrResourceAdapterProperties => 'EndPoint=https://graph.microsoft.com/v1.0,ClientId=&#60;ClientID&#62;,ClientSecret=&#60;ClientSecret&#62;,RefreshToken=&#60;RefreshToken>'
);;
 
CALL SYSADMIN.createDataSource (
    name => 'excel_online_src'
    ,translator => 'excel_online'
    ,modelProperties => 'parseTablesOrWorksheets=ALL,cleanupMethod=DELETE,onlyFolders=<folder>|<folder1>,tenantId=<tenantId>,excludeFolders=<exclude_folder>|<exclude_folder1>,viewTemplate=<{path}_{file_name}_{entity_name}>,sites=<sites>,users=<users>,groups=<groups>,drives=<drives>'
    ,translatorProperties => ''
);;