Files

Represents files stored in Box, including their content, metadata, ownership, and lifecycle information.

Table Specific Information

Select

If you query Files without specifying any condition in the WHERE Clause, only files up to 5 levels deep from the root folder are returned by default. You can change the default depth value in the connection string (for example, DirectoryRetrievalDepth=10).

SELECT * FROM Files;

By default, the search starts from the root directory, identified as directory '0'. To customize the starting directory for your search, specify its Id using the SearchRootId column.

SELECT * FROM Files WHERE SearchRootId = '293533136411';

To search all the Files in your enterprise, query the Files table with the SearchTerms column.

SELECT * FROM Files WHERE SearchTerms = 'untitled';

To search all the Files within a specific folder, query the Files table with a filter on the relevant folder's Id in the ParentId column.

SELECT * FROM Files WHERE ParentId = '12';

Note: When using a ParentId filter, avoid specifying a SearchRootId simultaneously. If both are used, the search will recursively start from the specified SearchRootId instead of the ParentId, which may result in slower performance.

Retrieving File Representations

Box can generate alternate representations of a file, such as extracted text or a PDF. The Representations column returns the file's available representations as a JSON aggregate, including each representation's generation status (its state, and a code when the state is error). This status is a reliable way to identify files that cannot be processed without downloading them. For example, password-encrypted files return a state of 'error' with the code 'error_encrypted_file'.

The generation status is only returned when you request one or more representation types through the RepresentationTypes column, which is sent as the Box X-Rep-Hints request header. The surrounding brackets are optional, so 'extracted_text' and '[extracted_text]' are equivalent. You can request multiple types by chaining them, as in '[extracted_text][pdf]'.

SELECT Id, Name, Representations FROM Files WHERE Id = '123' AND RepresentationTypes = 'extracted_text';

The Representations column is also available when listing the files within a folder.

SELECT Id, Name, Representations FROM Files WHERE ParentId = '12' AND RepresentationTypes = '[extracted_text]';

Note: If RepresentationTypes is not specified, the Representations column returns the available representation types without their generation status.

Update

Any column where ReadOnly=False can be updated.

UPDATE Files SET Description = 'example description', OwnedbyId = '321', ParentId = '12', Name = 'updated file name' WHERE Id = '123';

Delete

Delete a file by specifying its Id. This file is then moved to TrashedItems.

DELETE FROM Files WHERE Id = '100';

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique identifier assigned to the file.
Name	String	False		The display name of the file.
Sha1	String	False		The SHA-1 checksum of the file, used for content verification and deduplication.
Etag	String	False		Version identifier for the file, used to track changes and manage concurrency.
SequenceId	String	False		An incremental identifier representing the version sequence of the file.
Description	String	False		A user-provided description that explains the file's purpose or contents.
Size	Long	True		The file size in bytes.
CreatedAt	Datetime	True		The date and time when the file was originally created in Box.
ContentCreatedAt	Datetime	True		The date and time when the file content was first created, which may differ from the Box creation date.
CreatedById	String	True	Users.Id	Identifier of the user who created the file.
CreatedByName	String	True		The full name of the user who created the file.
CreatedByLogin	String	True		The login email of the user who created the file.
SharedLink	String	False		A shareable URL that provides access to the file.
ModifiedAt	Datetime	True		The date and time when the file was last updated in Box.
ContentModifiedAt	Datetime	True		The date and time when the file content itself was last modified.
ModifiedById	String	True	Users.Id	Identifier of the user who most recently modified the file.
ModifiedByName	String	True		The full name of the user who most recently modified the file.
ModifiedByLogin	String	True		The login email of the user who most recently modified the file.
OwnedById	String	False	Users.Id	Identifier of the user who owns the file.
OwnedByName	String	False		The full name of the user who owns the file.
OwnedByLogin	String	False		The login email of the user who owns the file.
ParentId	String	False	Folders.Id	Identifier of the folder that contains the file.
ItemStatus	String	False		The current state of the file, such as active or trashed.
TrashedAt	Datetime	True		The date and time when the file was moved to the trash.
PurgedAt	Datetime	True		The date and time when the file was permanently deleted from the trash.
ExpiresAt	Datetime	True		The date and time when the file is scheduled to be automatically deleted.
Path	String	True		The full folder path leading to the file within the Box hierarchy.
LockType	String	True		The type of the lock held on the file, which is always lock.
LockId	String	True		The unique identifier of the lock held on the file.
LockCreatedById	String	True		Identifier of the user who created the lock on the file.
LockCreatedByName	String	True		The full name of the user who created the lock on the file.
LockCreatedByLogin	String	True		The login email of the user who created the lock on the file.
LockCreatedAt	Datetime	True		The date and time when the lock was created on the file.
LockExpiresAt	Datetime	False		The date and time when the lock on the file expires.
LockIsDownloadPrevented	Bool	False		Indicates whether downloading the file is prevented while the lock is held.
Scope	String	True		The scope of the file search, such as limiting results to a user or enterprise.
FileExtension	String	True		The file's extension, such as PDF or DOCX.
ContentTypes	String	True		Specifies which file fields to search against, separated by commas. Possible values include name, file_content, description, comments, and tags.
OwnerUserIDs	String	True		A list of user identifiers separated by a comma, used to restrict searches to files owned by specific users.
AncestorfolderIDs	String	True		A list of folder identifiers separated by a comma, used to restrict searches to files contained within specific folders.
AsUserId	String	False		Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.
SearchRootId	String	True		Identifier of the folder to use as the starting point for recursive searches, with '0' representing the root folder.
Representations	String	True		A JSON aggregate of the file's available representations, including each representation's type and generation status (state, and code when present). Requires RepresentationTypes to be set for the status to be populated.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.


Name	Type	Description
SearchTerms	String	Keywords used to search for files stored in Box.
RepresentationTypes	String	The representation(s) to evaluate, sent as the Box X-Rep-Hints request header. The surrounding brackets are optional, so 'extracted_text' and '[extracted_text]' are equivalent; already-bracketed or chained values such as '[extracted_text][pdf]' are used as provided. Set this to populate the generation status in the Representations column.

Excel Add-In for Box