OneDrive Connector for Power BI

In this article you will learn how to integrate OneDrive data in Power BI without coding in just a few clicks (live / bi-directional connection to OneDrive). OneDrive Connector can be used to integrate OneDrive and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete OneDrive data in a few clicks!.

Using OneDrive Connector you will be able to connect, read, and write data from within Power BI. Follow the steps below to see how we would accomplish that.

Download Documentation

Video Tutorial - Integrate OneDrive data in Power BI

This video covers following and more so watch carefully. After watching this video follow the steps described in this article.

  • How to download / install required driver for OneDrive integration in Power BI
  • How to configure connection for OneDrive
  • Features about API Driver (Authentication / Query Language / Examples / Driver UI)
  • Using OneDrive Connection in Power BI

Create ODBC Data Source (DSN) based on ZappySys API Driver

Step-by-step instructions

To get data from OneDrive using Power BI we first need to create a DSN (Data Source) which will access data from OneDrive. We will later be able to read data using Power BI. Perform these steps:

  1. Install ZappySys ODBC PowerPack.

  2. Open ODBC Data Sources (x64):
    Open ODBC Data Source

  3. Create a User Data Source (User DSN) based on ZappySys API Driver

    ZappySys API Driver
    Create new User DSN for ZappySys API Driver
    You should create a System DSN (instead of a User DSN) if the client application is launched under a Windows System Account, e.g. as a Windows Service. If the client application is 32-bit (x86) running with a System DSN, use ODBC Data Sources (32-bit) instead of the 64-bit version.
  4. When the Configuration window appears give your data source a name if you haven't done that already, then select "OneDrive" from the list of Popular Connectors. If "OneDrive" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:

    OnedriveDSN
    OneDrive
    ODBC DSN Template Selection

  5. Now it's time to configure the Connection Manager. Select Authentication Type, e.g. Token Authentication. Then select API Base URL (in most cases, the default one is the right one). More info is available in the Authentication section.

    Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access. Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox, all scenarios involving user actions should use delegated access. [API reference]

    Steps how to get and use OneDrive credentials

    Follow these simple steps below to create Microsoft Entra ID application with delegated access:

    WARNING: To automate your company's processes, make sure you use a system/generic account (e.g. automation@my-company.com). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will start to fail.
    1. Navigate to the Azure Portal and log in using your credentials.
    2. Access Microsoft Entra ID.
    3. Register a new application by going to App registrations and clicking on New registration button: Start new app registration in Microsoft Entra ID
      INFO: Find more information on how to register an application in Graph API reference.
    4. When configuration window opens, configure these fields:
      • Supported account type
        • Use Accounts in this organizational directory only, if you need access to data in your organization only.
      • Redirect URI:
        • Set the type to Public client/native (mobile & desktop).
        • Use https://zappysys.com/oauth as the URL.
      Register app in Microsoft Entra ID
    5. After registering the app, copy the Application (client) ID for later: Copy client ID of Microsoft Entra ID app
    6. Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs to use later in the configuration: Copy Auth and Token URLs in Microsoft Entra ID app
    7. Now go to SSIS package or ODBC data source and use the copied values in User Credentials authentication configuration:
      • In the Authorization URL field paste the OAuth authorization endpoint (v2) URL value you copied in the previous step.
      • In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
      • In the Client ID field paste the Application (client) ID value you copied in the previous step.
      • In the Scope field use the default value or select individual scopes, e.g.:
        • email
        • offline_access
        • openid
        • profile
        • User.Read
        • Files.Read.All
        • Files.ReadWrite.All
    8. Press Generate Token button to generate Access and Refresh Tokens.
    9. Optional step. Choose Default Site Id from the drop down menu.
    10. Click Test Connection to confirm the connection is working.
    11. Done! Now you are ready to use the API Connector!

    Fill in all required parameters and set optional parameters if needed:

    OnedriveDSN
    OneDrive
    User Credentials [OAuth]
    https://graph.microsoft.com/v1.0
    Required Parameters
    Authorization URL Fill-in the parameter...
    Token URL Fill-in the parameter...
    Client ID Fill-in the parameter...
    Scope Fill-in the parameter...
    Optional Parameters
    Client Secret
    Refresh Token File Path
    Return URL https://zappysys.com/oauth
    Default Group or User Id (additional Scopes needed to list - If fails enter manually)
    Default Drive Id (Select after clicking **Generate Token**) me
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429|503|423
    RetryCountMax 5
    RetryMultiplyWaitTime True
    Login Prompt Option
    Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)
    Extra Headers (e.g. Header1:AAA||Header2:BBB)
    IsAppCred 0
    ODBC DSN Oauth Connection Configuration

    Application-only access is broader and more powerful than delegated access (User Credentials), so you should only use app-only access where needed. Use it when: 1. The application needs to run in an automated way, without user input (for example, a daily script that checks emails from certain contacts and sends automated responses). 2. The application needs to access resources belonging to multiple different users (for example, a backup or data loss prevention app might need to retrieve messages from many different chat channels, each with different participants). 3. You find yourself tempted to store credentials locally and allow the app to sign in 'as' the user or admin. [API reference]

    Steps how to get and use OneDrive credentials

    Follow these simple steps below to create Microsoft Entra ID application with application access permissions. Using following steps, you can grant very granular app permissions to access Full site(s), List(s), List Item(s) and File(s). Choose permission based on your need.

    Create OAuth app

    1. Navigate to the Azure Portal and log in using your credentials.
    2. Access Microsoft Entra ID.
    3. Register a new application by going to App registrations and clicking on New registration button: Start new app registration in Microsoft Entra ID
      INFO: Find more information on how to register an application in Graph API reference.
    4. When configuration window opens, configure these fields:
      • Supported account type
        • e.g. select Accounts in this organizational directory only if you need access to data in your organization only.
      • Redirect URI:
        • Set the type to Public client/native (mobile & desktop).
        • Leave the URL field empty.
      Register app in Microsoft Entra ID
    5. After registering the app, copy the Application (client) ID for later: Copy client ID of Microsoft Entra ID app
    6. Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs: Copy Auth and Token URLs in Microsoft Entra ID app
    7. Continue and create Client secret: Add Client secret for Microsoft Entra ID app
    8. Then copy the Client secret for later steps: Copy Client secret
    9. Continue by adding permissions for the app by going to the API permissions section, and clicking on Add a permission: Start adding permissions to Microsoft Entra ID app
    10. Select Microsoft Graph: Select Graph API permissions for Microsoft Entra ID app
    11. Then choose Application permissions option: Select app permissions for Microsoft Entra ID app
    12. Continue by adding these Files (OneDrive) permissions (Just search for "Site" and then select desired permissions): Select OneDrive scopes
    13. Move on by addding these Sites (SharePoint) permissions (You can search for "Site" and then select desired permissions): Select SharePoint Online application scopes
    14. Finish by clicking Add permissions button: Add permissions to Microsoft Entra ID app
    15. Now it's time to Grant admin consent for your application: Grant admin consent for Microsoft Entra ID app
    16. Confirm all the permissions are granted: Admin consent granted successfully in Entra ID
    17. Now go to SSIS package or ODBC data source and use the copied values in Application Credentials authentication configuration:
      • In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
      • In the Client ID field paste the Application (client) ID value you copied in the previous step.
      • In the Client Secret field paste the Client secret value you copied in the previous step.
      • Optional step. Choose Default Site Id from the drop down menu.
    18. Click Test Connection to confirm the connection is working.
    19. Done! Let's move on to the next step.

    Step-2 (Method#1 - New Approach): Grant permission to app using permission API (UI is not available yet)

    In year 2024 microsoft launched

    *.Selected
    permission scopes and new APIs to grant granular permissions to the resource for app access scenario. Unfortunatly, there is no User interface available yet to control this access so you have to call Admin API or use PowerShell script to control this. [Click here to learn more]. Follow these simple steps to configure permissions for app we created earlier (steps assume you are the owner of the resource and like to grant some permissions e.g. read, write, full, owner):

    1. Open PowerShell (Run as Admin user)
    2. Install Graph API module by running this command
      Import-Module Microsoft.Graph.Sites
    3. Now you can call following PowerShell code to set "read" and "write" permission for the app we created earlier (assuming app id is 89ea5c94-7736-4e25-95ad-3fa95f62b66e).
      
      #More info https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=powershell
      
      #Step-1: Install module if not found
      #Install-Module Microsoft.Graph.Sites
      
      #Step-2: set permission for app 89xxx6e and site zappysys.xx.efcdd21xxxxe2
      Import-Module Microsoft.Graph.Sites
      
      #Step-3: Login
      Connect-MgGraph
      
      #Step-4: Set Parameters for API Call (set Permissions, SiteId and AppId - Replace under #TODO)
      
      You can find SiteId by visiting this URL in browser (assuming you visted SharePoint site and already logged in) https://{your-company}.sharepoint.com/_api/site --OR-- for sub-site use https://{your-company}.sharepoint.com/sites/{your-site}/_api/site. Find  Id from the response (e.g. <Id m:type="Edm.Guid">)
      $siteId="yourcompany.sharepoint.com,efcddxxxxxxx104d8b5e3,8c9c6xxxxxxxx84e2"
      #--OR-- simple id
      #$siteId="efcddxxxxxxx104d8b5e3"
      
      
      $params = @{
      	roles = @(
              #possible options are read, write, fullcontrol, owner (write includes read too)
      		#TODO: Change here
      		"write"
      	)
      	grantedTo = @{
      		application = @{
                  #find this Client Id (Application Id) from the Azure Portal - Application page (https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade)
      			#TODO: Change here
      			id = "c3e9xxxxxx-xxxxx-xxxxx-xxxx-xxxxxb30c1"
      		}
      	}
      }
      
      #Step-5: Grant Permission
      New-MgSitePermission -SiteId $siteId -BodyParameter $params
      
      

    Fill in all required parameters and set optional parameters if needed:

    OnedriveDSN
    OneDrive
    Application Credentials [OAuth]
    https://graph.microsoft.com/v1.0
    Required Parameters
    Token URL Fill-in the parameter...
    Client ID Fill-in the parameter...
    Client Secret Fill-in the parameter...
    Default Group or User Id (additional Scopes needed to list - If fails enter manually) Fill-in the parameter...
    Default Drive Id Fill-in the parameter...
    Optional Parameters
    Scope https://graph.microsoft.com/.default
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429|503|423
    RetryCountMax 5
    RetryMultiplyWaitTime True
    Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)
    Extra Headers (e.g. Header1:AAA||Header2:BBB)
    IsAppCred 1
    ODBC DSN Oauth Connection Configuration

  6. Once the data source has been configured, you can preview data. Select the Preview tab and use settings similar to the following to preview data:
    ODBC ZappySys Data Source Preview

  7. Click OK to finish creating the data source.

Video instructions

Read OneDrive data in Power BI using ODBC

Importing OneDrive data into Power BI from table or view

  1. Once you open Power BI Desktop click Get Data to get data from ODBC:
    Power Bi Get Data

  2. A window opens, and then search for "odbc" to get data from ODBC data source:
    Power Bi ODBC Get Data

  3. Another window opens and asks to select a Data Source we already created. Choose OnedriveDSN and continue:

    OnedriveDSN
    Power Bi Select ZappySys Driver DSN

  4. Most likely, you will be asked to authenticate to a newly created DSN. Just select Windows authentication option together with Use my current credentials option:

    OnedriveDSN
    Power Bi DSN Authenticaation

  5. Finally, you will be asked to select a table or view to get data from. Select one and load the data!
    Power Bi Load DSN Table Data

  6. Finally, finally, use extracted data from OneDrive in a Power BI report:
    Power Bi Extracted DSN Table Data

Importing OneDrive data into Power BI using SQL query

If you wish to import OneDrive data from SQL query rather than a table then you can use advanced options during import steps (as below). After selecting DSN you can click on advanced options to see SQL Query editor.

OnedriveDSN
                SELECT
                    ProductID,
                    ProductName,
                    SupplierID,
                    CategoryID,
                    QuantityPerUnit,
                    UnitPrice
                FROM Products
                WHERE UnitPrice > 20
            
Get REST API data in Power BI Desktop using SQL query and ODBC
Consider using Custom Objects feature in ODBC data source to encapsulate SQL query in a Virtual Table. This way, you can see a virtual table in Power BI table list where you can import multiple objects using the same connection rather than creating a new connection for each custom SQL query.

Using a full ODBC connection string

In the previous steps we used a very short format of ODBC connection string - a DSN. Yet sometimes you don't want a dependency on an ODBC data source (and an extra step). In those times, you can define a full connection string and skip creating an ODBC data source entirely. Let's see below how to accomplish that in the below steps:

  1. Open ODBC data source configuration and click Copy settings:
    ZappySys API Driver - OneDrive
    OneDrive Connector can be used to integrate OneDrive and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete OneDrive data in a few clicks!
    OnedriveDSN
    Copy connection string for ODBC application
  2. The window opens, telling us the connection string was successfully copied to the clipboard: Successful connection string copying for ODBC application
  3. Then just paste the connection string into your script:
    OnedriveDSN
    DRIVER={ZappySys API Driver};ServiceUrl=https://graph.microsoft.com/v1.0;Provider=Custom;OAuthVersion=OAuth2;ScopeSeparator={space};GrantType=Default;Scope=[$Scope$];ScopeSeparator={space};
    Use full connection string in Power BI Desktop to read API data
  4. You are good to go! The script will execute the same way as using a DSN.
The DSN defined in the Data source name (DSN) field will be ignored.

Have in mind that a full connection string has length limitations.

Proceed to the next step to find out the details.

Limitations of using a full connection string

Despite using a full ODBC connection string may be very convenient it comes with a limitation: it's length is limited to 1024 symbols (or sometimes more). It usually happens when API provider generates a very long Refresh Token when OAuth is at play. If you are using such a long ODBC connection string, you may get this error:

"Connection string exceeds maximum allowed length of 1024"

But there is a solution to this by storing the full connection string in a file. Follow the steps below to achieve this:

  1. Open your ODBC data source.
  2. Click Copy settings button to copy a full connection string (see the previous section on how to accomplish that).
  3. Then create a new file, let's say, in C:\temp\odbc-connection-string.txt.
  4. Continue by pasting the copied connection string into a newly created file and save it.
  5. Finally, the last step! Just construct a shorter ODBC connection string using this format:
    DRIVER={ZappySys API Driver};SettingsFile=C:\temp\odbc-connection-string.txt
  6. Our troubles are over! Now you should be able to use this connection string in Power BI with no problems.
This feature requires ODBC PowerPack v1.9.0 or later.

Editing query for table in Power BI

There will be a time you need to change the initial query after importing data into Power BI. Don't worry, just right-click on your table and click Edit query menu item:

Edit query in Power BI to get REST API data
Refer to Power Query M reference for more information on how to use its advanced features in your queries.

Using parameters in Power BI (dynamic query)

In the real world, many values of your REST / SOAP API call may be coming from parameters. If that's the case for you can try to edit script manually as below. In below example its calling SQL Query with POST method and passing some parameters. Notice below where paraAPIKey is Power BI Parameter (string type). You can use parameters anywhere in your script just like the normal variable.

To use a parameter in Power BI report, follow these simple steps:

  1. Firstly, you need to Edit query of your table (see previous section)

  2. Then just create a new parameter by clicking Manage Parameters dropdown, click New Parameter option, and use it in the query:

    						
                                = Odbc.Query("dsn=OnedriveDSN",
                                             "SELECT ProductID, ProductName, UnitPrice, UnitsInStock
                                              FROM Products
                                              WHERE UnitPrice > " & Text.From(MyParameter) & "
                                              ORDER BY UnitPrice")
                            
                    
    Use parameter in Power BI to get REST API data
    Refer to Power Query M reference for more information on how to use its advanced features in your queries.

Using DirectQuery Option rather than Import

So far we have seen how to Import OneDrive data into Power BI but what if you have too much data and you dont want to import but link it. Power BI Offers very useful feature for this scenario. Its called DirectQuery Option. In this section we will explore how to use DirectQuery along with ZappySys Drivers.

Out of the box ZappySys Drivers wont work in ODBC Connection Mode so you have to use SQL Server Connection rather than ODBC if you wish to use Live data using DirectQuery option. See below step by step instructions to enable DirectQuery mode in Power BI for OneDrive data.

Basically we will use ZappySys Data Gateway its part of ODBC PowerPack. We will then use Linked Server in SQL Server to Link API Service and then we will issue OPENROWSET queries from Power BI to SQL Server and it will then call OneDrive via ZappySys Data Gateway.

  1. First read this article carefully, How to query OneDrive API in SQL Server.
  2. Once linked server is configured we are ready to issue API query in Power BI.
  3. Click Get Data in Power BI, select SQL Server Database
  4. Enter your server name and any database name
  5. Select Mode as DirectQuery
  6. Click on Advanced and enter query like below (we are assuming you have created OneDrive Data Source in Data Gateway and defined linked server (Change name below).
    SELECT * FROM OPENQUERY([ONEDRIVE_LINKED_SERVER], 'SELECT * FROM Customers')
    SELECT * FROM OPENQUERY([ONEDRIVE_LINKED_SERVER], 'SELECT * FROM Customers')
    DirectQuery option for Power BI (Read OneDrive Data Example using SQL Server Linked Server and ZappySys Data Gateway)


    DirectQuery option for Power BI (Read OneDrive Data Example using SQL Server Linked Server and ZappySys Data Gateway)

  7. Click OK and Load data ... That's it. Now your OneDrive API data is linked rather than imported.

Publishing Power BI report to Power BI service

Here are the instructions on how to publish a Power BI report to Power BI service from Power BI Desktop application:

  1. First of all, go to Power BI Desktop, open a Power BI report, and click Publish button:

    Publish Power BI report to Power BI service
  2. Then select the Workspace you want to publish report to and hit Select button:

    Publish Power BI report to workspace
  3. Finally, if everything went right, you will see a window indicating success:

    Successful Power BI report publishing

    If you need to periodically refresh Power BI semantic model (dataset) to ensure data accuracy and up-to-dateness, you can accomplish that by using Microsoft On-premises data gateway. Proceed to the next section - Refreshing Power BI semantic model (dataset) using On-premises data gateway - and learn how to do that.

Refreshing Power BI semantic model (dataset) using On-premises data gateway

Power BI allows to refresh semantic models which are based on data sources that reside on-premises. This can be achieved using Microsoft On-premises data gateway. There are two types of On-premises gateways:

  • Standard Mode
  • Personal Mode

Standard Mode supports Power BI and other Microsoft Data Fabric services. It fits perfectly for Enterprise solutions as it installs as a Windows Service and also supports Direct Query feature.

Personal Mode, on the other hand, can be configured faster, but is designed more for home users (you cannot install it as a Windows Service and it does not support DirectQuery). You will find a detailed comparison in the link above.

We recommend to go with Personal Mode for a quick POC solution, but use Standard Mode in production environment.

Below you will find instructions on how to refresh semantic model using both types of gateways.

Refresh using On-premises data gateway (standard mode)

Here are the instructions on how to refresh a Power BI semantic model using On-premises data gateway (standard mode):

  1. Go to Power BI My workspace, hover your mouse cursor on your semantic model and click Settings:

    Configure Power BI semantic model settings
  2. If you see this view, it means you have to install On-premises data gateway (standard mode):

    On-premises data gateway is not installed
  3. Install On-premises data gateway (standard mode) and sign-in:

    signing in into on-premises data gateway standard
    Use the same email address you use when logging in into Power BI account.
  4. Register a new gateway (or migrate an existing one):

    registering or migrating on-premises data gateway standard
  5. If you are creating a new gateway, name your gateway, enter a Recovery key, and click Configure button:

    naming on-premises data gateway standard
  6. Now, let's get back to your semantic model settings in Power BI portal. Refresh the page and you should see your newly created gateway. Click arrow icon and then click on Add to gateway link:

    ODBC{"connectionstring":"dsn=OnedriveDSN"}
    Using On-premises Data Gateway Standard for Power BI Semantic Model
  7. Once you do that, you will create a new gateway connection. Give it a name, set Authentication method, Privacy level, and click Create button:

    dsn=OnedriveDSN
    Create new connection in Power BI On-premises data gateway
    In this example, we used the least restrictive Privacy level.

    If your connection uses a full connection string you may hit a length limitation when entering it into the field. To create the connection, you will need to shorten it manually. Check the section about the limitation of a full connection string on how to accomplish it.

    On-premises data gateway (personal mode) does not have this limitation.

  8. Proceed by choosing the newly created connection:

    ODBC{"connectionstring":"dsn=OnedriveDSN"}
    Selecting gateway connection in Power BI semantic model
  9. Finally, you are at the final step where you can refresh the semantic model:

    Refreshing Power BI semantic model using On-premises Data Gateway

Refresh using On-premises data gateway (personal mode)

Here are the instructions on how to refresh a Power BI semantic model using On-premises data gateway (personal mode):

  1. Go to Power BI My workspace, hover your mouse cursor on your semantic model and click Settings:

    Configure Power BI semantic model settings
  2. If you see this view, it means you have to install On-premises data gateway (personal mode):

    On-premises data gateway is not installed
  3. Install On-premises data gateway (personal mode) and sign-in:

    Sign-in to On-premises data gateway personal
    Use the same email address you use when logging in into Power BI account.
  4. Again, go to your semantic model Settings, expand Data source credentials, click Edit credentials, select Authentication method together with Privacy level, and then click Sign in button:

    dsn=OnedriveDSN
    Use On-premises data gateway personal for Power BI semantic model
  5. Finally, you are ready to refresh your semantic model:

    Refreshing Power BI semantic model using On-premises Data Gateway

Advanced topics

Create Custom Stored Procedure in ZappySys Driver

You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.

Steps to create Custom Stored Procedure in ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here

  1. Go to Custom Objects Tab and Click on Add button and Select Add Procedure:
    ZappySys Driver - Add Stored Procedure

  2. Enter the desired Procedure name and click on OK:
    ZappySys Driver - Add Stored Procedure Name

  3. Select the created Stored Procedure and write the your desired stored procedure and Save it and it will create the custom stored procedure in the ZappySys Driver:
    Here is an example stored procedure for ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here

    CREATE PROCEDURE [usp_get_orders]
        @fromdate = '<<yyyy-MM-dd,FUN_TODAY>>'
     AS
        SELECT * FROM Orders where OrderDate >= '<@fromdate>';
    

    ZappySys Driver - Create Custom Stored Procedure

  4. That's it now go to Preview Tab and Execute your Stored Procedure using Exec Command. In this example it will extract the orders from the date 1996-01-01:

    Exec usp_get_orders '1996-01-01';

    ZappySys Driver - Execute Custom Stored Procedure

  5. Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
    As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.

    SELECT * FROM OPENQUERY([MY_API_SERVICE], 'EXEC usp_get_orders @fromdate=''1996-07-30''')

    ZappySys Driver - Generate SQL Server Query

  6. Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.
    ZappySys Driver - Generate SQL Server Query

Create Custom Virtual Table in ZappySys Driver

ZappySys API Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you don't have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.

If you're dealing with Microsoft Access and need to import data from an SQL query, it's important to note that Access doesn't allow direct import of SQL queries. Instead, you can create custom objects (Virtual Tables) to handle the import process.

Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 URLs with slight variations you can create virtual tables with just URL as Parameter setting.

  1. Go to Custom Objects Tab and Click on Add button and Select Add Table:
    ZappySys Driver - Add Table

  2. Enter the desired Table name and click on OK:
    ZappySys Driver - Add Table Name

  3. And it will open the New Query Window Click on Cancel to close that window and go to Custom Objects Tab.

  4. Select the created table, Select Text Type AS SQL and write the your desired SQL Query and Save it and it will create the custom table in the ZappySys Driver:
    Here is an example SQL query for ZappySys Driver. You can insert Placeholders also. Read more about placeholders here

    SELECT
      "ShipCountry",
      "OrderID",
      "CustomerID",
      "EmployeeID",
      "OrderDate",
      "RequiredDate",
      "ShippedDate",
      "ShipVia",
      "Freight",
      "ShipName",
      "ShipAddress",
      "ShipCity",
      "ShipRegion",
      "ShipPostalCode"
    FROM "Orders"
    Where "ShipCountry"='USA'

    ZappySys Driver - Create Custom Table

  5. That's it now go to Preview Tab and Execute your custom virtual table query. In this example it will extract the orders for the USA Shipping Country only:

    SELECT * FROM "vt__usa_orders_only"

    ZappySys Driver - Execute Custom Virtual Table Query

  6. Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
    As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.

    SELECT * FROM OPENQUERY([MY_API_SERVICE], 'EXEC [usp_get_orders] ''1996-01-01''')

    ZappySys Driver - Generate SQL Server Query

  7. Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.
    ZappySys Driver - Generate SQL Server Query

Actions supported by OneDrive Connector

OneDrive Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.
 List Drives
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
Myself (only use for User Credentials) /me
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
 Read Drive
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
 List Root (Non Recursive)
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
 Lists Folder Items (Non-Recursive Children)
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
Folder Id or Path (e.g. root: -OR- root:/myfolder: ) - Max 200 items listed Specify a Folder Id -OR- Path. Syntax: [{folder-id}] -OR- [root:/{folder-path}:] (Example: root:/myfolder/subfolder: )
 List Files
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (Default=Recursive) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive - All Files (Blank) - For User Creds Only search(q='')
Non-Recursive / Children - All Files - For Non-Shared Items Only children
Shared With Me (For DriveId 'me') - For User Creds Only sharedWithMe
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Path (Default=Root Folder) - Max 200 listed
Order By Field(s) - Only for Recursive SearchType Order by Field(s) for output. You can order by single or multiple fields and use filed suffix asc for ascending and desc for descending order. For example to order by lastModifiedDateTime in descending order use lastModifiedDateTime desc. To order by nested field you can supply lastModifiedBy/user/email asc
Option Value
name name
name desc name desc
size size
size desc size desc
createdDateTime createdDateTime
createdDateTime desc createdDateTime desc
lastModifiedDateTime lastModifiedDateTime
lastModifiedDateTime desc lastModifiedDateTime desc
 List Excel Files
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (Default=Recursive) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive search(q='.xls')
Non-Recursive (Only for Non-Shared Items) children
Shared With Me (For DriveId 'me') sharedWithMe
Search Path (Default=Root Folder)
Order By Field(s) - Only for Recursive SearchType Order by Field(s) for output. You can order by single or multiple fields and use filed suffix asc for ascending and desc for descending order. For example to order by lastModifiedDateTime in descending order use lastModifiedDateTime desc. To order by nested field you can supply lastModifiedBy/user/email asc
Option Value
name name
name desc name desc
size size
size desc size desc
createdDateTime createdDateTime
createdDateTime desc createdDateTime desc
lastModifiedDateTime lastModifiedDateTime
lastModifiedDateTime desc lastModifiedDateTime desc
 Get Users
Get the list of users.    [Read more...]
Parameter Description
 Get Groups
Get the list of groups.    [Read more...]
Parameter Description
 List Excel Worksheets of a File (i.e. List Tabs)
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
FileId -OR- Path Specify a file Id or Path. Syntax: [items/{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/myfile.xlsx: )
 Read Excel Worksheet (Auto Detect Range)
Use this endpoint to read data from a Sheet without supplying address range (e.g. A1:D100). This endpoint auto detects address range based on used cells in a sheet.    [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
Excel File Id or Path (e.g. root:/somefolder/myfile.xlsx: ) - Max 200 Listed Specify a file Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xlsx: )
Sheet Id or Name (e.g. {00xx-xxx-xx123} -OR- Sheet1 ) Specify a sheet id or name (e.g. {00000000-0001-0000-0000-000000000000} -OR- Sheet1
AutoDetectByValue Auto detect cell range based on values only and do not consider cell formatting (for empty values) to detect the range.
Option Value
true true
false false
 Read Excel Worksheet (From Exact Range)
   [Read more...]
Parameter Description
Drive Id / Shared Folder Specify a drive
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
Excel File Id or Path (e.g. root:/somefolder/myfile.xlsx: ) - Max 200 Listed Specify a file Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xlsx: )
Sheet Id or Name (e.g. {00xx-xxx-xx123} -OR- Sheet1 ) Specify a sheet id or name (e.g. {00000000-0001-0000-0000-000000000000} -OR- Sheet1
Range
 Read CSV File (Generic)
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )
 Create / Update CSV File (Generic)
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )
 Read JSON File (Generic)
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )
 Create / Update JSON File (Generic)
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )
 Download File
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )
Convert to new format (e.g. pdf or html) Use this option to download file and convert to another format (e.g. download excel file as pdf). See https://learn.microsoft.com/en-us/graph/api/driveitem-get-content-format?view=graph-rest-1.0 for more information. Supported Options are Convert to PDF from [csv, doc, docx, odp, ods, odt, pot, potm, potx, pps, ppsx, ppsxm, ppt, pptm, pptx, rtf, xls, xlsx]. Convert to HTML from supported formats are [loop, fluid, wbtx]
Option Value
Do not convert
Convert To PDF pdf
Convert To HTML (source file must be *.loop, *.fluid, *.wbtx) html
 Upload File
Upload a file to the specified path    [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Disk File Path (e.g. c:\somefolder\file.xlsx) Specify a disk file path
Target File Path / Id (e.g. file.xlsx -OR- myfolder/file.xlsx -OR- 0Zxxxx1234) Specify Target file path or exising file Id (for Overwrite scenario)
Option Value
Example #1 (Save On Root) file.xlsx
Example #2 (Save Under Folder) myfolder/myfile.xlsx
Example #3 (Save Under Child Folder) myfolder/subfolder/myfile.xlsx
Example #4 (Overwrite Exising File by ID) 0Zxxxx1234
Conflict Behavior (What to do if file exists)
Option Value
fail fail
replace replace
rename rename
Use Source File Created/Modified DateTime rather than Uploaded Time Set this property if you like to set file creation/modified date as original file. By default its set to uploaded date/time. This may not be desired if file was actually created on different date than uploaded. If you do not set then current date/time used (Upload time).
Option Value
Use Uploaded Time (Blank)
Use Source File Time true
 Get Item (File or Folder)
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
File/Folder Id or Path (e.g. root:/myfolder: -OR- root:/myfolder/file.xyz: ) - Max 1000 Listed Specify a File/Folder Id or Path (e.g. root:/myfolder/file.xyz: )
 Delete Item (File or Folder)
   [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Drive Id / Shared Folder Specify a drive
File/Folder Id or Path (e.g. root:/myfolder: -OR- root:/myfolder/file.xyz: ) - Max 1000 Listed Specify an item Id you like to delete
Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
 Move or rename item (File or Folder)
Move or rename an item (File or Folder) to a different location in the same drive or another drive    [Read more...]
Parameter Description
Operation
Option Value
Move Move
Rename Rename
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Source Drive Id Specify a drive
Source File or Folder Id you like to move or rename Specify an item Id you like to move or rename
Target Drive Id (Select if different than source DriveId) Specify a drive
Target Folder Id (e.g. 01R65Qxxxxxxx123 -OR- root) - Max 200 items listed Specify a target folder where item should move after move or rename operation. If you are doing inplace rename and not doing move after rename then keep it blank
Target Item Name (Optional for Move - e.g. Myfile.pdf or MyFolder) Specify target item name you like to give after move or rename operation. If its move operation and you like to keep same item name as source then keep this blank.
Source Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Source Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
Target Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Target Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
Prefer Async Operation (Enable if you get error)
Option Value
None
Async (Needed for Large files, Cross Site) respond-async
 Copy item (File or Folder)
Copy an item (File or Folder) to a different location in the same drive or another drive (e.g. different User's drive)    [Read more...]
Parameter Description
Default Group or User Id (additional Scopes needed to list - If fails enter manually) To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option Value
My self (Not Valid for Application Credentials)
For any group /groups/ENTER-GROUP-EMAIL-OR-ID
For any user /users/ENTER-USER-EMAIL-OR-ID
Source Drive Id Specify a drive
Source File or Folder Id you like to copy Specify an item Id you like to copy
Target DriveId (Optional - Select if different than source DriveId) Specify a drive
Target Folder Id (e.g. 01R65QTTRxxxxx -OR- root) Specify a target folder where item should be copied
Target Item Name (Optional) Specify target item name you like to give after copy operation. Keep it blank if you like to keep same name as source.
Source Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Recursive *.xlsx Files - For User Creds Only search(q='.xls')
Recursive *.csv Files - For User Creds Only search(q='.csv')
Recursive *.json Files - For User Creds Only search(q='.json')
Recursive Custom Extension Files - For User Creds Only search(q='.myext')
Source Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
Target Search Type (For UI Only - Default=Recursive - i.e. Blank) If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option Value
Recursive All Files (Keep Blank) - For User Creds Only
Non-Recursive / Children - All Files children
Target Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
Prefer Async Operation (Enable if you get error)
Option Value
None
Async (Needed for Large files, Cross Site) respond-async
Conflict Behavior (What to do if file exists at target location) Default ConflictBehavior is fail if file exists. The conflictBehavior is not supported for OneDrive Consumer. Also it will not fail if TargetItemName same as source file name
Option Value
fail fail
replace replace
rename rename
 Generic Request
This is generic endpoint. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL.    [Read more...]
Parameter Description
Url API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
Body Request Body content goes here
IsMultiPart Set this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value) ==== Raw Upload (Content-Type: application/octet-stream) ===== To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip ) ==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) ===== To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored. If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type. See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored). file1=@c:\data\Myfile1.txt file2=@c:\data\Myfile2.json file2.Content-Type=application/json SomeField1=aaaaaaa SomeField1.Content-Type=text/plain SomeField2=12345 SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab SomeFieldStartingWithAtSign=\@MyTwitterHandle
Filter Enter filter to extract array from response. Example: $.rows[*] --OR-- $.customers[*].orders[*]. Check your response document and find out hierarchy you like to extract
Option Value
No filter
Example1 $.store.books[*]
Example2 (Sections Under Books) $.store.books[*].sections[*]
Example3 (Equals) $.store.books[?(@author=='sam')]
Example4 (Equals - Any Section) $..[?(@author=='sam')]
Example5 (Not Equals - Any Section) $..[?(@author!='sam')]
Example6 (Number less than) $.store.books[?(@.price<10)] Example7 (Regular Expression - Contains Pattern)=$.store.books[?(@author=~ /sam|bob/ )]
Example8 (Regular Expression - Does Not Contain Pattern) $.store.books[?(@author=~ /^((?!sam|bob).)*$/ )]
Example9 (Regular Expression - Exact Pattern Match) $.store.books[?(@author=~ /^sam|bob$/ )]
Example10 (Regular Expression - Starts With) $.store.books[?(@author=~ /^sam/ )]
Example11 (Regular Expression - Ends With) $.store.books[?(@author=~ /sam$/ )]
Example12 (Between) $.store.employees[?( @.hiredate>'2015-01-01' && @.hiredate<'2015-01-04' )]
Headers Headers for Request. To enter multiple headers use double pipe or new line after each {header-name}:{value} pair
 Generic Request (Bulk Write)
This is a generic endpoint for bulk write purpose. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL.    [Read more...]
Parameter Description
Url API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
IsMultiPart Set this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value) ==== Raw Upload (Content-Type: application/octet-stream) ===== To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip ) ==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) ===== To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored. If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type. See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored). file1=@c:\data\Myfile1.txt file2=@c:\data\Myfile2.json file2.Content-Type=application/json SomeField1=aaaaaaa SomeField1.Content-Type=text/plain SomeField2=12345 SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab SomeFieldStartingWithAtSign=\@MyTwitterHandle
Filter Enter filter to extract array from response. Example: $.rows[*] --OR-- $.customers[*].orders[*]. Check your response document and find out hierarchy you like to extract
Headers Headers for Request. To enter multiple headers use double pipe (||) or new line after each {header-name}:{value} pair

OneDrive Connector Examples for Power BI Connection

This page offers a collection of SQL examples designed for seamless integration with the ZappySys API ODBC Driver under ODBC Data Source (36/64) or ZappySys Data Gateway, enhancing your ability to connect and interact with Prebuilt Connectors effectively.

List All Files (Recursive)    [Read more...]

Lists all files (recursive)

SELECT * FROM Files

List All Folders (Recursive)    [Read more...]

Lists all folders recursively (exclude files)

SELECT * FROM Folders

List All Files and Folders (Recursive)    [Read more...]

Lists all files and folders recursively

SELECT * FROM FilesFolders

List All Files and Folders Shared with Me    [Read more...]

Lists all files / folders shared with me. To List shared file inside a specific folder use FilesFolders table (e.g. select * from FilesFolders WITh(DriverId='zzzzz', SearchPath='zzzz') ). Provide DriveId (Found in SharedWithMe Table - see remoteItem_parentReference_driveId column) and optionally supply SearchPath (e.g. /root:/folder/subfolder: ) from which you like to list files/folders.

SELECT * FROM SharedWithMe

List drives    [Read more...]

Lists all drives

SELECT * FROM Drives

Get drive    [Read more...]

Gets info about drive

SELECT * FROM get_drive
WITH (DriveId='b!GtLQTMU726XXXXY5F2BBNi14')
--You can get DriveId by selecting from 'Drives' table.

List root    [Read more...]

Lists items at a root level

SELECT * FROM list_root
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wa')
--You can get DriveId by selecting from 'Drives' table.

List items in a folder (Non-Recursive)    [Read more...]

Lists items in a folder

SELECT *
FROM list_folder
WITH(
	  Filter='$.value[*]' --list both files and folders
	--Filter='$.value[?(@.file.mimeType != null)]' --list only files
	--Filter='$.value[?(@.folder.childCount != null)]' --list only folders
	  ,DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
	--,FolderId='01R65QTTUIVFJ6HVJ2WBC37KCAOQRKX7PP' --exact Id of Folder
	--,FolderId='root' --default is root if FolderId is not passed
	--,FolderId='root:/Documents/My Projects:'
	--,FolderId='root:/Documents/My Projects/Sample Project:'
)		
--You can get DriveId by selecting from 'Drives' table.
--You can get FolderId by selecting from 'list_root' table OR selection from 'list_folder'.

List files in a folder path (Recursive)    [Read more...]

Use list_files endpoint to list files recursivly (list from child folder). However there are some limitations to list recursively, such as some new files may not appear right away or files not indexed wont show up.

SELECT * FROM list_files
WITH(
     Filter='$.value[*]' --list both files and folders
   --Filter='$.value[?(@.file.mimeType != null)]' --list only files
   --Filter='$.value[?(@.folder.childCount != null)]' --list only folders
	,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
  --,SearchPath='/root'	--folder path
  --,SearchPath='/root:/myfolder:'	--folder path
	,SearchPath='/root:/myfolder/subfolder:' --folder path nested
    ,OrderBy='lastModifiedDateTime desc'
)		
--You can get DriveId by selecting from 'Drives' table.
--You can get SearchPath by selecting from 'list_root' table OR selection from 'list_folder'.

List files from Group / other User's drive    [Read more...]

This example shows how to read files from Group or other User's drive. You must use SearchType='children' for this purpose (known issue: recursive option may not work correctly in some cases).

SELECT * FROM list_files
WITH(
	  GroupOrUserId='/groups/138eb458-84f0-4928-bbe6-df663bd1fcea'
	, DriveId='b!VxnFPrt3C0GQnQ70Skx-p9_Eqvvac5pGhvqzoRdUpnZb-G5HVC0WTbLU3a89b4LJ'
	, SearchType='children'
	, SearchPath='/root:/folder/subfolder:'
	--, Filter='$.value[*]' --list both files and folders
	--, Filter='$.value[?(@.folder.childCount != null)]' --list only folders
)

List folders in a folder    [Read more...]

Lists folders in a folder

SELECT * FROM list_folder
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wa',
      FolderId='atLNa26LE0eYFa2BeNi1awa',
      Filter='$.value[?(@.folder)]')
--You can get DriveId by selecting from 'Drives' table.
--You can get FolderId by selecting from 'list_root' table OR selection from 'list_folder'.

List files in a folder    [Read more...]

Lists files in a folder

SELECT * FROM list_folder
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wa',
      FolderId='atLNa26LE0eYFa2BeNi1awa',
      Filter='$.value[?(@.file)]')
--You can get DriveId by selecting from 'Drives' table.
--You can get FolderId by selecting from 'list_root' table OR selection from 'list_folder'.

List Excel files    [Read more...]

Lists Excel files in a specified drive

SELECT * FROM list_excel_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14')
--You can get DriveId by selecting from 'list_drives' endpoint.

List Excel file Worksheets    [Read more...]

Enumerates Worksheets that are part of Excel file

SELECT * FROM list_excel_worksheets
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
     ,FileId='01SUOJPKECYDDVFZWXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'list_drives' endpoint.
--FileId can be retrieved by selecting from 'list_excel_files' endpoint.

Read Excel Worksheet data - Auto Detect Range    [Read more...]

Reads an Excel file without supplying Cell Address Range (It auto detects based on first and last cell)

SELECT * FROM get_excel_worksheet_autodetect
WITH (
         DriveId='b!GtLN726LE-XXXXXXXXXXX'
  	    ,FileId='01SUOJPFXXXXXXXXXXXXXXXXXX'
	    ,SheetId='Sheet1' --Id or Name
	  --,SheetId='{00000000-0001-0000-0000-000000000000}' --Using ID is good idea if Sheet ever renamed (Id doesnt change)
	    ,AutoDetectByValue='true'
		,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
		)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.

Read Excel Worksheet data for a specified Cell Address Range    [Read more...]

Reads an Excel file from a specified Cell Range. To automatically read without cell range use other endpoint get_excel_worksheet_autodetect

SELECT * FROM get_excel_worksheet
WITH (
        DriveId='b!GtLN726LE-XXXXXXXXXXX'
  	   ,FileId='01SUOJPFXXXXXXXXXXXXXXXXXX'
	   ,SheetId='Sheet1' --Id or Name
	   ,Range='A1:K10000'
	   ,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
	   )
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
--Range should be set to Excel-type of range where data is located.

Upload a file to OneDrive    [Read more...]

Uploads a file to a OneDrive

SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
     ,SourceFilePath='C:\My files\Employees\List.xls'
	 ,TargetFilePath='List.xlsx'	 --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
   --,TargetFilePath='myfolder/List.xlsx' --Upload under some folder
   --,TargetFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
   --,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
   --,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
     )
--DriveId can be retrieved by selecting from 'list_drives' endpoint.

Upload a file to OneDrive (Overwrite file if exists)    [Read more...]

Uploads a file to a OneDrive and if file already exists then overwrite it rather than throwing error (Default is throw error)

SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
     ,SourceFilePath='C:\My files\Employees\List.xls'
     ,SourceFilePath='C:\My files\Employees\List.xls'
	 ,TargetFilePath='List.xlsx'	 --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
   --,TargetFilePath='myfolder/List.xlsx' --Upload under some folder
   --,TargetFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
     ,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
   --,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
     )
--DriveId can be retrieved by selecting from 'list_drives' endpoint.

Upload a file with original timestamp (For Created/Modified Date)    [Read more...]

Uploads a file to a OneDrive and use original creation/modified on date. By default it uses Upload time to set creation/modified on date time. Set UseSourceFileTimeStamp if you like to use original timestamp. This is useful if file was created few days back but Upoloaded later on.

SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
     ,SourceFilePath='C:\My files\Employees\List.xls'
     ,TargetFilePath='Uploads/Employees/List.xls'
     ,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
     ,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
     )
--DriveId can be retrieved by selecting from 'list_drives' endpoint.

Download a file    [Read more...]

Downloads a file from OneDrive

SELECT * FROM download_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
,FileId='01SUOJPKHXMPKD2UXXXXXXXXXXXXXXXXXX'
,TargetFilePath='C:\My files\Employees\List.xlsx')
--DriveId can be retrieved by selecting from 'list_drives' endpoint.
--FileId can be retrieved by selecting from 'list_files' endpoint.

Delete a file/folder    [Read more...]

Deletes a file or folder in OneDrive

SELECT * FROM delete_item
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
     ,ItemId='01SUOJPKHXMPKD2UXXXXXXXXXXXXXXXXXX'
	)


--Using Table Name instead of endpoint (with Id / Path in WHERE clause for simple Syntax)

--************	
--Delete File by ID	or Path
--************
--DELETE From Files Where Id='01N3NI7YU6DYBSLCEDKBB23CR4FSWZYSDJ'
--DELETE From Files Where Id='root:/test_out.csv:'
--DELETE From Files Where Id='root:/somefolder/test_out.csv:'

--************
--Delete Folder by ID or Path
--************
--DELETE From Folders Where Id='01N3NI7YU6DYBSLCEDKBB23CR4FSWZYSDJ'
--DELETE From Folders Where Id='root:/somefolder:'
--DELETE From Folders Where Id='root:/somefolder/childfolder:'
	
--DriveId can be retrieved by selecting from 'list_drives' endpoint.
--FileId can be retrieved by selecting from 'list_folder' or 'list_root' endpoints.

Create / Update a CSV File in OneDrive from External Data Source (e.g. MSSQL / ODBC)    [Read more...]

This example shows how to create / update a CSV file directly on OneDrive using streaming approach. This example reads records from Microsoft SQL Server database and writes data to CSV file on OneDrive. If file does not exists then it creates a new one.

INSERT INTO FileReaderWriterCsv
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
	  DriveId='b!XpzQciaV_k6my5II5L22J0C4iRhyz21Js89PUyZ6-w0lH0AYv_I8RJHpXZQ81efD'
	  , FileId='root:/test_out.csv:'
	--, FileId='root:/subfolder/test_out.csv:'
	--, FileId='01N3NxxxxxxxWZYSDJ'  --exising File ID
	--, ContinueOn404Error=0 --Fail if file not found (Useful for overwrite mode for exising file)
	--, FileId='01N3NI7YU6DYBSLCEDKBB23CR4FSWZYSDJ'  --exising File ID for overwrite action
	--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ...
	--, RowDelimiter='{NEWLINE}' --{LF}, {TAB}, | , \x0009 ...
	--, HasColumnHeaderRow=0 --set for header less file
    --, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'  
    --See Query Builder for more options
)

Create / Update a JSON File in OneDrive from External Data Source (e.g. MSSQL / ODBC)    [Read more...]

This example shows how to create / update a JSON file directly on OneDrive using streaming approach. This example reads records from Microsoft SQL Server database and writes data to JSON file on OneDrive. If file does not exists then it creates a new one. It uses LayoutMap to generate JSON with custom layout.

INSERT INTO FileReaderWriterJson
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
	    DriveId='b!XpzQciaV_k6my5II5L22J0C4iRhyz21Js89PUyZ6-w0lH0AYv_I8RJHpXZQ81efD'
	  , FileId='root:/test_out.json:'
	--, FileId='root:/subfolder/test_out.json:'
	--, FileId='01N3NxxxxxxxxxxWZYSDJ'  --exising File ID
	--, ContinueOn404Error=0 --Fail if file not found (Useful for overwrite mode for exising file)	
	  , JsonOutputFormat='Multicontent' --Default , Array2D, ArrayLines ...
	--, DoNotOutputNullProperty='True'
	--, Encoding='UTF8' --UTF8WithoutBOM
  --, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'
	
	--Example#1: Output all columns
	, LayoutMap='<?xml version="1.0"  ?>
<settings>
  <dataset id="root" main="True" readfrominput="True" />
  <map src="*" />  
</settings>'

	--Example#2: Nested JSON (Records under an array)
	/*
	, LayoutMap='<?xml version="1.0" ?>
<settings singledataset="True">
  <dataset id="root" main="True" readfrominput="True" />
  <map name="MyArray" dataset="root" maptype="DocArray">
    <map src="OrderID" name="OrderID" />
    <map src="OrderDate" name="OrderDate" />
  </map>
</settings>'
   */
   
	--Example#3: Nested JSON (Records under a sub document section)
	/*	
	, LayoutMap='<?xml version="1.0" ?>
<settings>
  <dataset id="dsRoot" main="True" readfrominput="True" />
  <map name="NestedSection">
    <map src="OrderID" name="OrderID_MyLabel" />
    <map src="OrderDate" name="OrderDate_MyLabel" />
  </map>
</settings>'
	*/
)

Read CSV File from OneDrive (Parse Rows and Columns without downloading)    [Read more...]

This example shows how to read a CSV file from OneDrive without downloading on the Disk (Streaming approach).

SELECT * from get_csv_file
WITH(
	  DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
	, FileId='root:/dump.csv:'
    --, FileId='01N3NI7YRUO2UHV2TUMBGJ4H4QQMWCG6DA'			
	--, FileId='root:/myfolder/dump.csv:'	
	--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ... default is comma (",")
	--, HasColumnHeaderRow=0 --set for header less file
	--, FileId='root:/myfolder/test_out.csv.gz:'
	--, FileCompressionType='GZip' --None, GZip, Zip
)         

--SELECT * FROM get_csv_file WITH(FileId='01N3NI7YQA6I2F7YAXBBCZCLLPTRFLANAX')
--SELECT * FROM get_csv_file WITH(FileId='root:/dump.csv:')
--SELECT * FROM get_csv_file WITH(FileId='root:/Documents/dump.csv:')
--SELECT * FROM get_csv_file WITH(FileId='root:/Documents/SubFolder/dump.csv:')

Read Zip/Gzip Compressed CSV File from OneDrive (Parse Rows and Columns without downloading)    [Read more...]

This example shows how to read a compressed CSV file (Zip or Gzip) from OneDrive without downloading on the Disk (Streaming approach).

SELECT * from get_csv_file
WITH(
	  DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
	, FileId='root:/myfolder/dump.csv.zip:'
  --, FileId='01N3NI7YRUO2UHV2TUMBGJ4H4QQMWCG6DA'		
  --, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ... default is comma (",")
  --, HasColumnHeaderRow=0 --set for header less file
  --, FileId='root:/MyFolder/dump.csv.gz:'
    , FileCompressionType='Zip' --None, GZip, Zip
)

Read JSON File from OneDrive (Parse Rows and Columns without downloading)    [Read more...]

This example shows how to read a JSON file from OneDrive without downloading on Disk (Streaming approach).

SELECT * from get_json_file
WITH(
	  DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
	, FileId='root:/myfolder/dump.json:'
  --, FileId='01N3NI7YRUO2UHV2TUMBGJ4H4QQMWCG6DA'		
    , Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
    --to read compressed file use below way
	--, FileId='root:/myfolder/dump.json.gz:'
	--, FileCompressionType='GZip' --None, GZip, Zip
)         

--SELECT * FROM get_json_file WITH(FileId='01N3NI7YQJMKXUWUAQGJEJJJNSGVT7QSJ3')
--SELECT * FROM get_json_file WITH(FileId='root:/dump.json:')
--SELECT * FROM get_json_file WITH(FileId='root:/Documents/dump.json:')
--SELECT * FROM get_json_file WITH(FileId='root:/Documents/SubFolder/dump.json:')

Read Zip/Gzip Compressed JSON File from OneDrive (Parse Rows and Columns without downloading)    [Read more...]

This example shows how to read a compressed JSON file (Zip or Gzip) from OneDrive without downloading on the Disk (Streaming approach).

SELECT * from get_json_file
WITH(
	DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
  , FileId='root:/myfolder/dump.json.zip:'
--, FileId='01N3NI7YRUO2UHV2TUMBGJ4H4QQMWCG6DA'
  , Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
  , FileCompressionType='Zip' --None, GZip, Zip
)

Move a File or Folder in OneDrive    [Read more...]

This example shows how to move a file or folder to a different location in OneDrive. It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>

SELECT * FROM move_rename_item
WITH(
    --Source Drive
	  DriveId='me'
	--OR--
	--DriveId='b!7HBaTtrbekqMYJQ-OqV5Q3hrcOIQoyhGiAoWjqWFenIlIJ-Us7DMQ6jvyrsWMJPx'
	
	--Source Item
	--Item Id (File or Folder) you like to move or rename
	, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'

	--Target Drive Id (Optional): Only needed if moving file to a different Drive (e.g. another User's drive)
	--, TargetDriveId='me'
	--OR
	--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'

	--Target Folder
	, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
	--OR (for moving to root)
	--, TargetFolderId='root'
	
	--Target Item Name (New Name) after Move (Optional) - If you do not wish to change name after Move then do not supply below
	--, TargetItemName='new_file_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf' 
	
	--Uncomment Below if you get error. Usually needed for large file or cross site operations
	--, Prefer='respond-async'	
)

Rename a File or Folder in OneDrive    [Read more...]

This example shows how to rename a file or folder and you can also move to a different location in OneDrive.. It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>

SELECT * FROM move_rename_item
WITH(
    --Source Drive
	DriveId='me'
	--OR--
	--DriveId='b!7HBaTtrbekqMYJQ-OqV5Q3hrcOIQoyhGiAoWjqWFenIlIJ-Us7DMQ6jvyrsWMJPx'

	--Source Item you like to rename or move
	, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'

	--Target Drive Id (Optional): Only needed if moving file to a different Drive (e.g. another User's drive)
    --, TargetDriveId='me'
	--OR
	--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
	
	--Target Folder (Optional): If you are doing Rename and Move both in one action then supply new Folder Id too else keep it blank for just rename
	--, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
	--, TargetFolderId='root'
	
	--Target Item Name (New name)
	, TargetItemName='new_file_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf' 
	
	--Uncomment Below if you get error. Usually needed for large file or cross site operations
	--, Prefer='respond-async'	
)

Copy a File or Folder in OneDrive    [Read more...]

This example shows how to copy a file or folder to a different location in OneDrive (In same Drive or different User's drive). It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>

SELECT * FROM copy_item
WITH(
	--Source Drive
	DriveId='me'
	--OR--
	--DriveId='b!7HBaTtrbekqMYJQ-OqV5Q3hrcOIQoyhGiAoWjqWFenIlIJ-Us7DMQ6jvyrsWMJPx'

	--Source Item you like to copy
	, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'

	--Target Drive Id (Optional): Only needed if copying file to a different Drive (e.g. another User's drive)
	--, TargetDriveId='me'
	--OR--
	--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
	
	--Target Folder where item will be copied
	, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
	--OR--
	--, TargetFolderId='root'

	--Target item name (Optional) - Only needed if new name needed after copy
	, TargetItemName='new_file_or_folder_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf' 

	--,ConflictBehavior='replace' --fail, rename (Ignored if TargetItemName is not supplied)

	--Uncomment Below if you get error. Usually needed for large file or cross site operations
	--, Prefer='respond-async'	
)

Download File and convert to PDF or HTML file format (convert xlsx, docx, pptx and many more) in OneDrive    [Read more...]

This example shows how to download a file with different format (PDF or HTML). If conversion not supported you will get an error.

SELECT "Status"
FROM download_file
WITH(
     DriveId='me'
     --OR--
     --DriveId='b!7HBaTtrbekqMYJQ-OqV5Q3hrcOIQoyhGiAoWjqWFenIlIJ-Us7DMQ6jvyrsWMJPx'

	, FileId='01R65QTTTF7H7WMCHCKRFJGEJTXAEC7RGX'
	
	--Supported Source Formats: csv, doc, docx, odp, ods, odt, pot, potm, potx, pps, ppsx, ppsxm, ppt, pptm, pptx, rtf, xls, xlsx
	, ConvertTo='pdf'
	, TargetFilePath='C:\temp\converted.pdf'
	
	--OR--
	--Supported Source Formats: loop, fluid, wbtx
	--, ConvertTo='html'
	--, TargetFilePath='C:\temp\converted.html'
)

Conclusion

In this article we discussed how to connect to OneDrive in Power BI and integrate data without any coding. Click here to Download OneDrive Connector for Power BI and try yourself see how easy it is. If you still have any question(s) then ask here or simply click on live chat icon below and ask our expert (see bottom-right corner of this page).

Download OneDrive Connector for Power BI Documentation 

More integrations

Other application integration scenarios for OneDrive

Other connectors for Power BI


Download OneDrive Connector for Power BI Documentation

  • How to connect OneDrive in Power BI?

  • How to get OneDrive data in Power BI?

  • How to read OneDrive data in Power BI?

  • How to load OneDrive data in Power BI?

  • How to import OneDrive data in Power BI?

  • How to pull OneDrive data in Power BI?

  • How to push data to OneDrive in Power BI?

  • How to write data to OneDrive in Power BI?

  • How to POST data to OneDrive in Power BI?

  • Call OneDrive API in Power BI

  • Consume OneDrive API in Power BI

  • OneDrive Power BI Automate

  • OneDrive Power BI Integration

  • Integration OneDrive in Power BI

  • Consume real-time OneDrive data in Power BI

  • Consume real-time OneDrive API data in Power BI

  • OneDrive ODBC Driver | ODBC Driver for OneDrive | ODBC OneDrive Driver | SSIS OneDrive Source | SSIS OneDrive Destination

  • Connect OneDrive in Power BI

  • Load OneDrive in Power BI

  • Load OneDrive data in Power BI

  • Read OneDrive data in Power BI

  • OneDrive API Call in Power BI