Files

The OneDrive table Files in which you can view all the files in a drive.

Table Specific Information

Projection is done server-side. Also, queries that include the primary key Id and IN are executed server-side, e.g.:

SELECT Id, Name, audio_album, audio_title FROM Files WHERE Id IN ('B1E6BJ084A9133KB1!101', 'APF9D2K6WFA524Y')

Include ParentReference_DriveId and ParentReference_Id in the where clause to get the result for a particular folder, e.g.:

 SELECT * FROM Files WHERE  ParentReference_DriveId = 'b!3Yqza-5FJ0usfhyH-2QafkQpf0OC0jlNh8LAETWPK6dNROPDScevTKbV-drnCFHV' And ParentReference_id = '01QFG2V52Z62HOXS5F6RHIHVT36T3Q5GKH'

File listing is done recursively via the DirectoryRetrievalDepth property. This property's default value is 5, which means it will get data only for the first 6 levels of the 'Drive' you've chosen (because the first level is located in DirectoryRetrievalDepth=0).

You can specify the value -1 to get all the files in the 'Drive' no matter the depth they're located in.

While files may be listed in this view, the contents of the files may be worked with by using stored procedures such as:

CopyResource - Copy a file/folder from one directory in your drive to another.
DeleteResource - Delete a file/folder from your drive.
DownloadFile - Download an existing file from your drive.
MoveResource - Move a file/folder from one directory in your drive to another.
UploadFile - Upload a new file or update content to an existing file.

Columns

References Description String String String String String String String String String Datetime String String String Datetime String String String String

Name Type style="white-space:nowrap">id Etag createdBy_user_id createdBy_user_displayName lastModifiedBy_user_id lastModifiedBy_user_displayName ResourceType createdBy_application_displayName createdBy_application_id createdDateTime description lastModifiedBy_application_displayName lastModifiedBy_application_id lastModifiedDateTime name parentReference_driveId parentReference_driveType parentReference_id e-folders.htm">Folders.Id

parentReference_name String parentReference_path String parentReference_shareId String parentReference_sharepointIds_listId String parentReference_sharepointIds_listItemId String parentReference_sharepointIds_listItemUniqueId String parentReference_sharepointIds_siteId String parentReference_sharepointIds_siteUrl String parentReference_sharepointIds_tenantId String parentReference_sharepointIds_webId String parentReference_siteId String webUrl String LinkedcreatedByUser String LinkedlastModifiedByUser String audio_album String audio_albumArtist String audio_artist String audio_bitrate Long audio_composers String audio_copyright String audio_disc Int audio_discCount Int audio_duration Long audio_genre String audio_hasDrm Bool audio_isVariableBitrate Bool audio_title String audio_track Int audio_trackCount Int audio_year Int content String cTag String deleted_state String file_hashes_crc32Hash String file_hashes_quickXorHash String file_hashes_sha1Hash String file_hashes_sha256Hash String file_mimeType String file_processingMetadata Bool fileSystemInfo_createdDateTime Datetime fileSystemInfo_lastAccessedDateTime Datetime fileSystemInfo_lastModifiedDateTime Datetime folder_childCount Int folder_view_sortBy String folder_view_sortOrder String folder_view_viewType String image_height Int image_width Int location_altitude Double location_latitude Double location_longitude Double package_type String pendingOperations_pendingContentUpdate_queuedDateTime Datetime photo_cameraMake String photo_cameraModel String photo_exposureDenominator Double photo_exposureNumerator Double photo_fNumber Double photo_focalLength Double photo_iso Int photo_orientation Int photo_takenDateTime Datetime publication_level String publication_versionId String remoteItem_createdBy_application_displayName String remoteItem_createdBy_application_id String remoteItem_createdDateTime Datetime remoteItem_file_hashes_crc32Hash String remoteItem_file_hashes_quickXorHash String remoteItem_file_hashes_sha1Hash String remoteItem_file_hashes_sha256Hash String remoteItem_file_mimeType String remoteItem_file_processingMetadata Bool remoteItem_fileSystemInfo_createdDateTime Datetime remoteItem_fileSystemInfo_lastAccessedDateTime Datetime remoteItem_fileSystemInfo_lastModifiedDateTime Datetime remoteItem_folder_childCount Int remoteItem_folder_view_sortBy String remoteItem_folder_view_sortOrder String remoteItem_folder_view_viewType String remoteItem_id String remoteItem_image_height Int remoteItem_image_width Int remoteItem_lastModifiedDateTime Datetime remoteItem_name String remoteItem_package_type String remoteItem_parentReference_driveId String remoteItem_parentReference_driveType String remoteItem_parentReference_id String remoteItem_parentReference_name String remoteItem_parentReference_path String remoteItem_parentReference_shareId String remoteItem_parentReference_sharepointIds_listId String remoteItem_parentReference_sharepointIds_listItemId String remoteItem_parentReference_sharepointIds_listItemUniqueId String remoteItem_parentReference_sharepointIds_siteId String remoteItem_parentReference_sharepointIds_siteUrl String remoteItem_parentReference_sharepointIds_tenantId String remoteItem_parentReference_sharepointIds_webId String remoteItem_parentReference_siteId String remoteItem_shared_scope String remoteItem_shared_sharedDateTime Datetime remoteItem_size Long remoteItem_specialFolder_name String remoteItem_video_audioBitsPerSample Int remoteItem_video_audioChannels Int remoteItem_video_audioFormat String remoteItem_video_audioSamplesPerSecond Int remoteItem_video_bitrate Int remoteItem_video_duration Long remoteItem_video_fourCC String remoteItem_video_frameRate Double remoteItem_video_height Int remoteItem_video_width Int remoteItem_webDavUrl String remoteItem_webUrl String searchResult_onClickTelemetryUrl String shared_owner_application_displayName String shared_owner_application_id String shared_scope String shared_sharedDateTime Datetime sharepointIds_listId String sharepointIds_listItemId String sharepointIds_listItemUniqueId String sharepointIds_siteId String sharepointIds_siteUrl String sharepointIds_tenantId String sharepointIds_webId String size Long specialFolder_name String video_audioBitsPerSample Int video_audioChannels Int video_audioFormat String video_audioSamplesPerSecond Int video_bitrate Int video_duration Long video_fourCC String video_frameRate Double video_height Int video_width Int webDavUrl String Linkedanalytics String Linkedchildren String LinkedlistItem String Linkedpermissions String Linkedsubscriptions String Linkedthumbnails String Linkedversions String Linkedworkbook String

Power BI Connector for Microsoft OneDrive

Files

Table Specific Information

Columns