Connectors → Microsoft OneDrive
About OneDrive
OneDrive is Microsoft’s cloud-storage service that allows you to store, share, and collaborate on files and folders from any mobile device, tablet, or computer.
About the OneDrive connector
The Incorta OneDrive connector is available for preview only.
With the OneDrive connector, you can create a data source for a OneDrive file or folder. The OneDrive connector supports the following file extensions:
.csv
.tsv
.tab
.txt
.xlsx
You can access all folders and files that you own and any folders or files that someone shares with you.
The OneDrive connector supports the following Incorta-specific functionality:
Feature | Supported |
---|---|
Chunking | |
Data Agent | |
Encryption at Ingest | |
Incremental Load | ✔ |
Multi-Source | ✔ |
OAuth | ✔ |
Performance Optimized | ✔ |
Remote | |
Single-Source | ✔ |
Spark Extraction | |
Webhook Callbacks | ✔ |
Prerequisites
- Ensure you configure OneDrive and authorize it via OAuth2.0. For more information, refer to Security → OAuth for OneDrive.
- For the first time only, ask the SuperUser to install the connector from the marketplace
Steps to Connect OneDrive and Incorta
To connect your OneDrive and Incorta, here are the high-level steps, tools, and procedures:
- Create an external data source
- Create a schema with the Schema Wizard
- or, Create a schema with the Schema Designer
- Load the schema
- Explore the schema
Create an external data source
Here are the steps to create an external data source with the OneDrive connector:
- Sign in to the Incorta Direct Data Platform.
- In the Navigation bar, select Data.
- In the Action bar, select + New → Add Data Source.
- In the Choose a Data Source dialog, in File System, select OneDrive.
- In the New Data Source dialog, specify the applicable connector properties.
- To test, select Test Connection.
- Select OK to save your changes.
If you select the lowest folder in the tree, you will see No Data in the Select Directory from dialog. You will have access to the files in this folder upon schema creation. However, you will not be able to select the parent folder.
OneDrive connector properties
Here are the properties for the OneDrive connector:
Property | Control | Description |
---|---|---|
Data Source Name | text box | Enter the name of the data source |
Client ID | textbox | Enter your Microsoft OneDrive client ID. For more information, refer to OneDrive documentation. |
Client Secret | textbox | Enter your Microsoft OneDrive client secret. |
Authorize | button | Select this button to authenticate your Microsoft account and grant Incorta read access to your OneDrive. Choose an account to use to access your OneDrive and select the Allow button. The New Data Source dialog will reappear, and the Authorize button will change to Authorized with the name of the Microsoft account to the right. |
Create a schema with the Schema Wizard
Here are the steps to create a OneDrive schema with the Schema Wizard:
- Sign in to the Incorta Direct Data Platform.
- In the Navigation bar, select Schema.
- In the Action bar, select + New → Schema Wizard
- In (1) Choose a Source, specify the following:
- For Enter a name, enter the schema name.
- For Select a Datasource, select the OneDrive external data source.
- Optionally create a description.
- In the Schema Wizard footer, select Next.
- In (2) Manage Tables, in the Data panel, navigate the directory tree as necessary to select your file.
- In the Schema Wizard footer, select Next.
- In (3) Finalize, in the Schema Wizard footer, select Create Schema.
Create a schema with the Schema Designer
Here are the steps to create a OneDrive schema using the Schema Designer:
- Sign in to the Incorta Direct Data Platform.
- In the Navigation bar, select Schema.
- In the Action bar, select + New → Create Schema.
- In Name, specify the schema name, and select Save.
- In Start adding tables to your schema, select File System.
- In the Data Source dialog, specify the various properties table data source properties.
- Select Add.
- In the Table Editor, in the Table Summary section, enter the table name.
- To save your changes, select Done in the Action bar.
OneDrive table data source properties
You can specify a single file or folder in the Data Source dialog. Both the Schema Designer and Table Editor represent a single file and folder data source as a single-source table. To select a folder in your My Drive, you must enable Union Files.
This release has limited support for Union Files for Excel (.xlsx
) Workbook files. The Loader Service only loads Worksheets with the same name as defined in the table data source properties.
Common properties for a file and folder
Here are some of the common properties for both the selection of a file and a folder:
Property | Control | Description |
---|---|---|
Type | drop-down list | Default is File System |
Data Source | drop-down list | Select the OneDrive external data source |
File Type | drop-down list | Select the Text (.csv , .tsv , .tab , .txt ) or Excel (.xlsx ) |
Has Header? | toggle | Select if the first row contains column header values |
Callback | toggle | Enables the Callback URL field |
Callback URL | text box | This property appears when the Callback toggle is enabled. Specify the URL. |
Common file properties
Here are some of the common properties specifically related to selecting a file of either type Text (.csv
, .tsv
, .tab
, .txt
) or Excel (.xlsx
):
Property | Control | Description |
---|---|---|
Incremental | toggle | Enable to support incremental loading. For a single file, you must specify both a File and Update file. |
File | button | Select a file opens the Add File from dialog. The dialog shows the files from your OneDrive data source. Select a single file and select Add. |
Update File | button | With Incremental enabled, Update File is available. Select a file opens the Add File from dialog. The dialog shows the files from your OneDrive data source. Select a single file and select Add. |
With Incremental enabled, if a Key column is not defined, new rows will be appended and no existing rows will be updated.
Properties for an Excel Workbook file
Here are the specific properties for an Excel Workbook (.xlsx
) file:
Property | Control | Description |
---|---|---|
Worksheet | drop-down list | Select a given worksheet for the Excel Workbook |
Properties for a Text file
Here are the properties specific to a Text (.csv
, .tsv
, .tab
, .txt
) file:
Property | Control | Description |
---|---|---|
Date Format | drop-down list | Select a specific format for date columns. Date formats are Java date format conventions. With Automatic , Incorta will determine the format by sampling the first few rows. |
Timestamp Format | drop-down list | Select a specific format for timestamp columns. Timestamp formats are Java data and time format conventions. With Automatic , Incorta will determine the format by sampling the first few rows. |
Character Set | drop-down list | Select a supported character set. |
Separator | drop-down list | Available when the selected File Type is Text . Specify a separator for columns in the row values. Comma and Tab are standard delimiters. Other requires that you specify a value such as : . |
Other | text box | Available when the Separator is Other . Enter one or more characters to specify the column separator or delimiter between values in a row. |
Enable Chunking | toggle | Enable for large file sizes |
Chunk Size (MB) | text box | Enter a value in megabytes (MB) to specify the chunk size |
Common folder properties
Folder properties are available when you enable Union Files. It is not possible to select a parent folder.
Here are the properties specifically related to selecting a folder:
Property | Control | Description |
---|---|---|
Incremental | toggle | Enable to support incremental loading |
Union Files | toggle | Enable to select all files within a given folder. When enabled, you will only be able to select a folder from your OneDrive data source. |
Directory | button | Select a folder from your OneDrive data source. It is not possible to select a parent folder. |
Include | text box | Enter a keyword with a wildcard * symbol to include specific named files within the folder |
Exclude | text box | Enter a keyword with a wildcard * symbol to exclude specific named files within the folder |
Include Sub-Directories Files | toggle | Enable to include files from sub-folders |
Add Filename as a column | toggle | Enable to add the filename of the file as a column. You will then need to specify a column name. |
Filename column | text box | Enter a column name for the filename such as source_file_name |
With Incremental enabled, if there is not a Key column defined, new rows will be appended and no existing rows will be updated.
Folder properties for Excel Workbook files
This release has limited support for Union Files for Excel Workbook (.xlsx
) files. The Loader Service only loads Worksheets with the same name as defined in the table data source properties. For this reason, each Excel Workbook file in the selected folder must have a common Worksheet tab name. You must select this common Worksheet name in the drop-down list.
Here are the properties specifically related to selecting a folder with a file type as Excel Workbook (.xlsx
) files:
Property | Control | Description |
---|---|---|
Worksheet | drop-down list | Select a tab for a worksheet |
View the schema diagram with the Schema Diagram Viewer
Here are the steps to view the schema diagram using the Schema Diagram Viewer:
- Sign in to the Incorta Direct Data Platform.
- In the Navigation bar, select Schema.
- In the list of schemas, select the OneDrive schema.
- In the Schema Designer, in the Action bar, select Diagram.
Load the schema
Here are the steps to perform a Full Load of the OneDrive schema using the Schema Designer:
- Sign in to the Incorta Direct Data Platform.
- In the Navigation bar, select Schema.
- In the list of schemas, select the OneDrive schema.
- In the Schema Designer, in the Action bar, select Load → Full Load.
- To review the load status, in Last Load Status, select the date.
Explore the schema
With the full load of the OneDrive schema complete, you can use the Analyzer to explore the schema, create your first insight, and save the insight to a new dashboard.
To open the Analyzer from the schema, follow these steps:
- In the Navigation bar, select Schema.
- In the Schema Manager, in the List view, select the OneDrive schema.
- In the Schema Designer, in the Action bar, select Explore Data.