List Files

Parameters

Output Columns

Input Columns

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 Destination

This Endpoint belongs to Files table, therefore you cannot work with it directly. Use this table and table-operation pair instead:

OneDrive

Files

Select

ODBC application

Use these SQL queries in your ODBC application data source:

List files in a folder path (Recursive)

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

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 All Files (Recursive)

Lists all files (recursive)

SELECT * FROM Files

List All Files and Folders (Recursive)

Lists all files and folders recursively

SELECT * FROM FilesFolders

List All Folders (Recursive)

Lists all folders recursively (exclude files)

SELECT * FROM Folders

List All Files and Folders Shared with Me

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

SQL Server

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

List files in a folder path (Recursive)

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.

DECLARE @MyQuery NVARCHAR(MAX) = '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''.';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

List files from Group / other User's drive

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).

DECLARE @MyQuery NVARCHAR(MAX) = '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
)';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

List All Files (Recursive)

Lists all files (recursive)

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

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

List All Files and Folders (Recursive)

Lists all files and folders recursively

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

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

List All Folders (Recursive)

Lists all folders recursively (exclude files)

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

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

List All Files and Folders Shared with Me

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.

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

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];