excel

In data ingestion and data cleansing scenarios, business data is often delivered as Excel workbooks. Manually converting them to CSV files or processing data column by column is cumbersome and can easily cause data quality issues due to inconsistent headers, row ranges, null values, or cell formats. The server-side excel plugin provides the ability to read .xlsx files directly in DolphinDB Server. Its core capabilities include:

  • Reading workbook structure: obtains all Sheet names in a specified .xlsx file.
  • Reading by column mapping: supports mapping Excel columns to DolphinDB columns and specifying the names and data types of DolphinDB columns.
  • Controlling the read range: supports specifying the header row, data start row, and data end row.
  • Header validation: validates actual file headers against expected headers to promptly detect template changes.

DolphinDB currently provides two Excel-related plugins, which differ in where they run and in their usage scenarios:

  • DolphinDB Excel Add-in / Excel client add-in: runs in the Microsoft Excel client and is used to connect to DolphinDB, execute queries, and export data from Excel. It is suitable for users who want to interact with DolphinDB in the Excel interface.
  • DolphinDB server-side excel plugin: runs in DolphinDB Server and is used to read .xlsx files located on the server, converting them into DolphinDB tables. This article describes the server-side plugin.

Before downloading the plugin, confirm your usage scenario: if you need to operate DolphinDB in the Excel client, download the Excel Add-in; if you need to read Excel files through scripts in DolphinDB Server, download the server-side excel plugin. Note that the current server-side excel plugin supports only .xlsx files and does not support legacy .xls files. If the source file is .xls, save it as or convert it to .xlsx before reading it.

Installation (with installPlugin)

Required server version: DolphinDB Server version requirement: version 2.00.16, 3.00.4, or later.

Supported platforms: Linux-x86, Linux-ABI.

Installation Steps

  1. Use the listRemotePlugins function to check plugin information in the plugin repository.
    login("admin", "123456")
listRemotePlugins()
  2. UseinstallPlugin for plugin installation.
    installPlugin("excel")
  3. Use loadPlugin to load the plugin before using the plugin methods.
    loadPlugin("excel")

Method References

listSheets

Syntax

excel::listSheets(filePath)

Details

Obtains all Sheet names in the specified Excel workbook. The result is returned in the order of the Sheets in the workbook.

Parameters

filePath: A STRING scalar specifying the path of the .xlsx file on DolphinDB Server. The path must point to a specific file and cannot be a directory.

Returns

A STRING vector containing all Sheet names in the workbook.

Example

filePath = "/data/bond_trade.xlsx" sheets = excel::listSheets(filePath) sheets

Result:

["Spot Bond Trading", "Repo Trading"]

readSheet

Syntax

excel::readSheet(filePath, sheet, columnSpec, [options])

Details

Reads a single Sheet according to the specified column mapping, row range, and read options, and returns an in-memory DolphinDB table. Reading and merging multiple Sheets can be performed with DolphinDB scripts.

Parameters

filePath: A STRING scalar specifying the path of the .xlsx file on DolphinDB Server.

sheet: A STRING scalar specifying the name of the Sheet to read.

columnSpec: A column mapping table used to specify source columns in Excel, result table column names, and target DolphinDB data types. The table must contain, and can only contain, the following three columns:

Column Name Description Example
excelColumn Source column in Excel. You can use a column label or a 1-based integer column number. "A", "AA", 1
name Column name in the DolphinDB result table. "institution"
type Target DolphinDB data type, case-insensitive. "STRING", "double"

Supported target types include: BOOL, CHAR, SHORT, INT, LONG, FLOAT, DOUBLE, STRING, SYMBOL, DATE, MONTH, TIME, MINUTE, SECOND, DATETIME, TIMESTAMP, NANOTIME, NANOTIMESTAMP, and DATEHOUR.

options (optional): A dictionary of type dict(STRING, ANY). It is used to configure the header, data range, null values, merged cells, original location, and other read rules. The supported keys are as follows:

Key Description
headerRow The 1-based Excel header row number. When expectedHeader is specified, the plugin reads and validates this row. The default value is 1.
dataStartRow The 1-based data start row. The read result includes this row. The default is headerRow + 1.
dataEndRow The data end row. The read result includes this row. If not specified, data is read through the end of valid data.
includeExcelLocation

Whether to add the excelSheet (STRING) and excelRow (INT) columns to the front of the result table. The default value is false. If set to true, the following two columns are automatically appended to the front of the result table:

  • excelSheet: A STRING scalar indicating the name of the Sheet where the data is located.
  • excelRow: An INT scalar indicating the physical row number in Excel where the data is located.

Note: When this option is enabled, the name column in columnSpec cannot use excelSheet or excelRow as user-defined column names; otherwise, a column name conflict will occur.
expectedHeader A STRING vector with the same length as columnSpec, used to precisely validate headers according to the mapped columns. When specified, it is used together with headerRow.
mergedCellMode

Merged-cell handling mode. Valid values are:

  • "topLeft" (default): keeps only the value in the upper-left cell of the merged range and leaves the other positions empty.
  • "fill": fills the value from the upper-left cell into the entire merged range being read.
  • "error": throws an error directly when the merged range intersects with the header or data range to be read.

Returns

An in-memory DolphinDB table. The table schema is specified by columnSpec. When includeExcelLocation is enabled, the excelSheet and excelRow columns are added to the front of the result table.

Type Conversion Rules

Excel Type Target Type Conversion Method
Null value All types Outputs a null value of the corresponding type.
String CHAR Converted to a character only when the string length is 1 byte.
String STRING/SYMBOL Read as a string.
String Types other than STRING and SYMBOL Converted according to DolphinDB rules.
Boolean All types Read as BOOL first, and then converted to the corresponding type according to DolphinDB rules.
Number (numeric cell) All types Read as DOUBLE first, and then converted to the corresponding type according to DolphinDB rules.
Number (time cell) Numeric types Ignores the cell display format and processes the value as a number.
Number (time cell) Temporal types Parsed as TIMESTAMP first, and then converted to the corresponding temporal type according to DolphinDB rules.
Error All types Throws an error.
Formula All types Throws an error.
  • If strings such as - or N/A are used in the file to represent null values, it is recommended to first read the relevant columns as STRING in the DolphinDB script, and then handle them uniformly according to business rules. To help detect data exceptions promptly in production environments, the plugin throws an error directly when type conversion fails.
  • Each cell in Excel can have its own cell format. This setting affects whether the underlying DOUBLE value is displayed as a time or as a number in Excel. When reading temporal types, confirm that the source file cell format is consistent with the expected target type.

Examples

Example 1. The following example reads a single Sheet and converts Excel data into a DolphinDB table according to the column mapping.

loadPlugin("excel")
go

filePath = "/data/bond_trade.xlsx"

columnSpec = table(
    ["A", "B", "C", "D"] as excelColumn,
    ["institution", "maturity", "treasuryNew", "treasuryOld"] as name,
    ["STRING", "STRING", "DOUBLE", "DOUBLE"] as type
)

options = dict(STRING, ANY)
options["headerRow"] = 3
options["dataStartRow"] = 4
options["expectedHeader"] = ["Institution Name", "Maturity", "Treasury Bond - New Issue", "Treasury Bond - Old Issue"]
options["mergedCellMode"] = "topLeft"
options["includeExcelLocation"] = true

result = excel::readSheet(filePath, "Spot Bond Trading", columnSpec, options)
result

Result:

excelSheet excelRow institution maturity treasuryNew treasuryOld
Spot Bond Trading 4 Institution A 1Y 100.5 20.0
Spot Bond Trading 5 Institution B 3Y 80.0 10.5

Example 2. Reading and Merging Multiple Sheets

sheets = excel::listSheets(filePath)
tables = array(ANY, 0)

for (sheetName in sheets) {
    tables.append!(excel::readSheet(filePath, sheetName, columnSpec, options))
}

result = unionAll(tables, false)

The Sheets to be merged should use the same columnSpec to ensure a consistent result table schema.