Table Files

Description

No description available

Supported Operations

Examples

SSIS

Use OneDrive Connector in API Source component to read data or in API Destination component to read/write data:

Read from Files table using API Source

API Source - OneDrive

Read and write Microsoft OneDrive data effortlessly. Integrate, manage, and automate files and folders — almost no coding required.

OneDrive

Files

Required Parameters
Record Filter (Client Side)	Fill-in the parameter...
Drive Id / Shared Folder	Fill-in the parameter...
Optional Parameters
Default Group or User Id (additional Scopes needed to list - If fails enter manually)
Search Type (Default=Recursive)	search(q='')
Search Path (Default=Root Folder) - Max 200 listed	/root
Order By Field(s) - Only for Recursive SearchType

Read/write to Files table using API Destination

API Destination - OneDrive

Read and write Microsoft OneDrive data effortlessly. Integrate, manage, and automate files and folders — almost no coding required.

OneDrive

Files

Select

Required Parameters
Record Filter (Client Side)	Fill-in the parameter...
Drive Id / Shared Folder	Fill-in the parameter...
Optional Parameters
Default Group or User Id (additional Scopes needed to list - If fails enter manually)
Search Type (Default=Recursive)	search(q='')
Search Path (Default=Root Folder) - Max 200 listed	/root
Order By Field(s) - Only for Recursive SearchType

ODBC application

Use these SQL queries in your ODBC application data source:

List All Files (Recursive)

Lists all files (recursive)

SELECT * FROM Files

Delete a file/folder

Deletes a file or folder in OneDrive

DELETE FROM Files
WITH (
     DriveId='me'
   --OR--
   --DriveId='b!GtLN726LE0eY5F2BBNi14'
   
   --Enter file path or ID 
   
   --,ItemId='root:/dump.pdf:'	--Path must end with colon
   --,ItemId='root:/myfolder/dump.pdf:'
   --,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.

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

List All Files (Recursive)

Lists all files (recursive)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Files';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

Delete a file/folder

Deletes a file or folder in OneDrive

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Files
WITH (
     DriveId=''me''
   --OR--
   --DriveId=''b!GtLN726LE0eY5F2BBNi14''
   
   --Enter file path or ID 
   
   --,ItemId=''root:/dump.pdf:''	--Path must end with colon
   --,ItemId=''root:/myfolder/dump.pdf:''
   --,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.';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];