Reference

Table Files

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT list_files
INSERT
UPDATE
UPSERT
DELETE delete_item
LOOKUP get_item

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
SSIS API Source - Read from table or endpoint

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
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read all files (recursive)

<p>Gets all files recursively from the default drive. Use this when you need a flat list of every file under a drive. For a single folder (non-recursive) or to filter by path, use the <code>list_root</code> or <code>list_files</code> table with <code>DriveId</code> and <code>SearchPath</code>.</p> 

SELECT * FROM Files

Delete a file or folder

<p>Deletes a file or folder. Identify the item by ID or path in the <code>WITH</code> clause. This action cannot be undone.</p> 

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:

Read all files (recursive)

<p>Gets all files recursively from the default drive. Use this when you need a flat list of every file under a drive. For a single folder (non-recursive) or to filter by path, use the <code>list_root</code> or <code>list_files</code> table with <code>DriveId</code> and <code>SearchPath</code>.</p> 

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

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

Delete a file or folder

<p>Deletes a file or folder. Identify the item by ID or path in the <code>WITH</code> clause. This action cannot be undone.</p> 

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];