Microsoft OneDrive
Connect your Varicent ELT and Microsoft OneDrive accounts to import your data.
Connect your Varicent ELT and Microsoft OneDrive accounts to import your data.
Before you begin
You must have a pre-existing Microsoft account, either business or personal.
When you create a new Microsoft OneDrive connection, you are redirected to sign in to your Microsoft account. If you have multiple Microsoft accounts, enter the credentials for the account containing the Microsoft OneDrive you want to import files from. You are then prompted to give permission to Varicent ELT to read the files in your Microsoft OneDrive. This permission is required so Varicent ELT can list available files, and import the files you select.
Supported data sources
This connector can import any CSV and Excel files stored within, or shared with, your Microsoft OneDrive account.
Note
Varicent ELT cannot import any files larger than 5GB.
Import
With the correct permissions in place, you can now import your data into Varicent ELT.
In Varicent ELT, go to the Data tab.
Click Import data.
In the Third party sources section, click Microsoft OneDrive.
Click New connection.
On the Connect to your OneDrive page, enter the Connector name.
Click Next.
You will be redirected to the Microsoft permissions page.
On the Pick an account modal, choose the account that you want to import your data from.
On the permissions page, click Accept.
You will be redirected back to Varicent ELT.
On the Select data page, enter the following information to import your data:
Field
Description
Directory
Select the directory for the file that you want to import.
File type
Select the file type that you want to import.
Delimiter
Select the delimiter that you want to use from the following delimiters in the drop-down field:
Comma
(,)Pipe
(|)Semi-colon
(;)Space
( )Tab
( )Tilde
(~)
Quote
Select the type of quotes that you want to use, such as double quotes (").
Exclude excess data
Enter the number of rows that you want to ignore in the beginning or end of your file.
Header
Check to designate the first unignored row as the column header. Leave unchecked to have column headers automatically generated.
Click Next.
On the Import data page, select which data sets to include or obfuscate (if any). Click Next.
On the Filter data page, optionally click Filter data to refine your data, or click Import data to continue.
Filter the rows in your data by selecting the column to filter, the filter category and the value or text.
Click Import data to continue.
Note
For more information about how to configure data filters, see: Filter data.
On the Review page, review the data sets that you are importing. Click Import data.
You are redirected to the My data page where you can review the progress of the import. After the data is imported, you can click + Create pipe.
In Varicent ELT, go to the Data tab.
Click Import data.
In the Third party sources section, click Microsoft OneDrive.
On the Select data page, choose the data set to import from the Directory drop-down.
Note
You can import Excel, CSV or text files.
Click Next.
For text or CSV files, enter the following information:
Note
You don't have to enter this information for Excel files. This step is for text and CSV files only.
Field
Description
Directory
The file that you want to import.
Delimiter
The delimiter that you want to use:
Comma
(,)Pipe
(|)Semicolon
(;)Space ( )
Tab ( )
Tilde
(~)
Quote
The type of quotes that you want to use, such as double quotes
(").Exclude excess data
Enter the number of rows that you want to ignore in the beginning or end of your file.
Header
Check to designate the first unignored row as the column header. Leave unchecked to have column headers automatically generated.
On the Import data page, select which data sets to include or obfuscate. Click Next.
On the Review page, review the data sets that you are importing. Click Import data.
You are redirected to the My data page where you can review the progress of the import. After the data is imported, you can click + Create pipe.
Usage examples
After creating the connection, you can view a list of all available files that you can import from Microsoft OneDrive, such as your files, and files shared with you.
For shared files, there is a limit on the number of items, either files or folders that are retrieved for display during the Directories step of import. There is a limit of 200 items that are directly shared with the user. This limit includes all types of files and folders, not just CSV and Excel files. If a user has more than 200 items shared directly to them, all items might not appear in the drop-down for selection. This limit does not apply to files belonging to the user, or files contained within shared folders.
If you select an Excel file, you can continue with the import process without any further configuration.
If you select a CSV file, you have some extra configuration options available.
Delimiter (defaults to comma ,): The character in your CSV file that separates columns in your data.
Quote (default to double quotes "): The character used to wrap data that might contain special characters. If a column in your data contained text, which itself could contain commas, your file would likely have those columns wrapped in a quote character, such as ". Specifying the correct quote character, if applicable, ensures that your data is imported correctly.
For example, in a column called
Location, you might have values such asToronto, ONandMontreal, Quebec. To ensure the commas inside the text don’t get interpreted as column separators, they would be wrapped in quotes, such as"Toronto, ON"and"Montreal, Quebec".Exclude excess data: Your CSV files might contain extra information, such as comments, above or below the actual table of data. If the CSV file you want to import contains this extra information, but you don’t want it imported, you can exclude it by specifying how many rows should be skipped at the beginning or end of the file. Leave this section unchanged if your file simply contains a table of data.
If your data doesn’t contain a header row (i.e. a row immediately preceding your data that gives a name to each column), you can uncheck the Header box, and column names will be automatically generated for you.
Security and privacy
We use standard authentication and authorization practices to connect with Microsoft and securely store encrypted access tokens. We do not have access to any personal information associated with your Microsoft account. We only request access to your Microsoft OneDrive files, which are required for us to import from Microsoft.
Error handling
The following errors may appear during the import process:
Error | Description | Solution |
|---|---|---|
Data refresh fails | The data refresh can fail if the original imported file has been renamed or deleted. | To fix this error, import the new file, re-selecting the file as a new import. One unexpected way this error can happen is if you import a CSV file, and then sometime later, open that file in Microsoft OneDrive, which automatically converts the file to an Excel file. With this conversion, you will not be able to refresh that particular file directly, as it has changed from a CSV to Excel file. |
| If you create a Microsoft OneDrive connection, and then do not use it for an extended period of time, it’s possible that the access tokens we store internally to connect to Microsoft will expire. In that case, you may see an error like Credentials failed. | To fix this error, edit your connection. This prompts you to authenticate with Microsoft again. Authenticating allows you to refresh existing data sources that were originally imported using the connection. You can create a new Microsoft OneDrive connection, and you can select multiple options. |
| Import failed with the Microsoft OneDrive error. | To fix this error, review the Microsoft OneDrive documentation: Code property. |