--- title: "Drive.Excel" slug: "driveexcel" tags: ["luminesce ", "provider", "drive", "excel"] updated: 2026-06-29T12:52:24Z published: 2026-06-29T12:52:24Z canonical: "support.lusid.com/driveexcel" stale: true --- > ## Documentation Index > Fetch the complete documentation index at: https://support.lusid.com/llms.txt > Use this file to discover all available pages before exploring further. # Drive.Excel | Type | Read/write | Author | Availability | | --- | --- | --- | --- | | [Direct provider](/v1/docs/what-are-a-data-provider-and-a-direct-provider#direct-provider) | Write | FINBOURNE | Provided with LUSID | The `Drive.Excel` provider enables you to write a [Luminesce query](/v1/docs/understanding-the-luminesce-sql-query-syntax) that extracts data from one or more Excel (`.xlsx` or `.xlsm`) files stored in [Drive](/v1/docs/drive). The query returns a table of data assembled from the contents of the file or files in the order they are read. **See also:** [Drive.Csv](/v1/docs/drivecsv), [Drive.Sqlite](/v1/docs/drivesqlite), [Drive.Xml](/v1/docs/drivexml), [Drive.RawText](/v1/docs/driverawtext), [Drive.File](/v1/docs/drivefile) ## Basic usage ```sql @x = use Drive.Excel enduse; select * from @x ``` ## Options `Drive.Excel` has options that enable you to filter or refine a query. > [!NOTE] > **Note:** The `--file` option is mandatory. An option takes the form `--<option>=<value>`, for example `--file=pnl.xlsx`. Note no spaces are allowed either side of the `=` operator. If an option: - Takes a boolean value, then specifying that option (for example `--noHeader`) sets it to True; omitting the option specifies False. - Takes multiple string values, then specify a comma-separated list, for example `--names=My,Column,Names`. Current options at article update time are listed in the table below. For the very latest information, run the following query using a [suitable tool](/v1/docs/what-tools-are-available-to-write-luminesce-queries) and examine the online help: ```sql @x = use Drive.Excel enduse; select * from @x ``` | Current options | Explanation | | --- | --- | | `file` (`-f`) | **Mandatory**. The file to read. Should be an Excel file (.xlsx or .xlsm). It may also be a folder, in which case --folderFilter is also required to specify which files in the folder to process. [String] | | `folderFilter` | Denotes this is searching an entire folder structure and provides a Regular Expression of path/file names within it that should be processed. All matches should be of the same format. [String] | | `zipFilter` (`-z`) | Denotes this is a Zip file and provides a Regular Expression of path/file names within it that should be processed. All matches should be of the same format. [String] | | `allowMissing` | Should a file/folder simply not exist, don't throw an error but return an empty table with column names and types created as best possible given other options. [Boolean] | | `addFileName` | Adds a column (the first column) to the result set which contains the file the row came from. [Boolean] | | `names` | Column Names either overrides the header row or steps in when there is no header row (comma-delimited list). [String] | | `types` | Column types (comma delimited list of: 'Boolean, Date, DateTime, Decimal, Double, Int, BigInt, Text, Table', some columns may be left blank while others are specified) [String] | | `inferTypeRowCount` | If non-zero and 'types' is not specified (or not specified for some columns) this will look through N rows to attempt to work out the column types for columns not pre-specified. [Int32] | | `trim` | Trim both leading and trailing whitespace when reading values. [Boolean] | | `noHeader` | Set this if there is no header row. [Boolean] | | `calculate` | Whether to attempt a calculation of the imported cell range prior to import. [Boolean] | | `password` | If specified will be used as the password used for password protected workbooks. [String] | | `worksheet` (`-w`) | The worksheet containing the cell range to import (name or index, will default to first). [String] | | `range` (`-r`) | The cell range to import as either a specified range or a table name. Range formats can be any of: - 'A1:B3' (standard Excel range format) - '1,3,7,9' (rows 1-7, columns 3-9) - ',3,,9' (all populated rows, but only columns 3-9) - ',3,,' (all populated rows, but only columns 3 to the last populated one) Defaults to the used dimensions of the worksheet (equivalent to ',,,') [String] | | `ignoreInvalid` | If specified cells which can not be successfully converted to the target type will be ignored. [Boolean] | | `ignoreBlankRows` | If the entire rows has only blank cells it will be ignored will be ignored. [Boolean] | | `valuesToMakeNull` | Regex of values to map to 'null' in the returned data; You must Specify --convertValuesToNulls for this to be used. [String, Default: (^[nN][uU][lL]{1,2}$)\|((\r\n\|\n\|\r)$)\|(^(\r\n\|\n\|\r))\|(^\s*$)] | | `convertValuesToNulls` | Uses the Regex in 'valuesToMakeNull' to decide if some values should be converted to nulls. [Boolean] | | `addWorksheetName` | Adds a column (the first or second column) to the result set which contains the worksheet name. [Boolean] | ## Examples In the following examples, the `select * from @x` syntax at the end prints the table of data assembled by the query. > [!NOTE] > **Note:** For more examples, try the [Luminesce Github repo](https://github.com/finbourne/luminesce-examples/tree/master/examples/drive). ### Example 1: Extract data from particular cells in a particular Excel worksheet The `--range` option enables you to select particular cells, for example: - `A1:B3` selects every cell between A1 and B3. - `5,3,20,9` selects every cell from row 5 to row 20 and in columns 3 to 9. - `,3,,9` selects every populated cell in columns 3 to 9 from the first row to the last populated row. - `,3,,` selects every populated cell from column 3 to the last populated column. Note if you omit `--range` all the populated cells in a worksheet are selected. ```sql @x = use Drive.Excel --file=/calculation/pnl.xlsx --range=A1:B3 --worksheet=Portfolio1 enduse; select * from @x ``` ### Example 2: Extract data from a matching Excel file In this example, `@@today` is a [scalar variable](/v1/docs/understanding-the-luminesce-sql-query-syntax#variables) that must resolve to exactly one column and one row of data. ```sql @@today = select strftime('%Y%m%d', 'now'); @x = use Drive.Excel with @@today --file=/calculation/pnl-{@@today}.xlsx --range=A1:C12 enduse; select * from @x ``` ### Example 3: Extract data from multiple matching Excel files in a particular folder If `--file` is a folder in Drive, you must specify the `--folderFilter` option with a regular expression to nominate one or more files to read. In this example, data is extracted from the same cells in three files `pnl-1.xlsx`, `pnl-2.xlsx` and `pnl-3.xlsx`, but not from `pnl-4.xlsx`. The `--addFileName` option adds an extra column to the table of results showing the source of each record. ```sql @x = use Drive.Excel --file=/calculation --folderFilter=pnl-[1-3].xlsx --range=A1:C12 --addFileName enduse; select * from @x ``` ### Example 4: Extract data from Excel files stored in a ZIP archive In this example, `daily.zip` is stored in the root Drive folder; data is extracted from any Excel file within it that just has alphabetic characters in its file name. ```sql @x = use Drive.Excel --file=daily.zip --zipFilter=[a-zA-Z].xlsx --range=A1:C12 --addFileName enduse; select * from @x ``` ### Example 5: Extract data from an Excel file with no header row, instead supplying column names Note the number of column names specified must match the inbound number of columns. ```sql @x = use Drive.Excel --file=/calculation/pnl.xlsx --range=A1:C12 --noHeader --names=Equity,Units,Cost enduse; select * from @x ``` ### Example 6: Infer data types of columns from the first 10 rows In this example, the `--ignoreInvalid` option is specified to ignore cells that cannot be successfully converted. ```sql @x = use Drive.Excel --file=/calculation/pnl.xlsx --range=A1:Z12 --inferTypeRowCount=10 --ignoreInvalid enduse; select * from @x ```