New Plugin | Reading Excel Files Directly into DolphinDB
Monthly reports, business records, transaction records, manually entered data, and files delivered by external institutions: Excel remains one of the most common formats for data exchange in enterprise workflows.
As data volumes grow and computation becomes more complex, though, Excel data often needs to be integrated with dedicated data processing systems. Data entered in Excel may still need to be cleaned, processed in batches, or joined with historical data. It may also need to feed BI tools, business applications, and AI systems.
The DolphinDB Excel Plugin provides a direct ingestion path: you can read .xlsx files directly on the DolphinDB server without manually converting them to another format. The data is loaded into a DolphinDB table based on the fields, data types, and read options you specify.
A More Direct Path from Excel to DolphinDB
Bringing Excel data into DolphinDB involves more than just reading a file. Real-world business templates and data structures vary widely, so the plugin offers flexible ingestion capabilities that let Excel data enter downstream workflows in the required structure.
1. Flexible Reading for Different Business Templates
A workbook may contain multiple sheets, and the header position, the starting row of the data, and the fields you need can all differ. The plugin supports reading by sheet, column mapping, and data range. It also provides several modes for handling merged cells, so it can handle the complex templates often found in real-world business workflows.
For example, you can define a column mapping like this:
columnSpec = table(
["A", "B", "C", "D"] as excelColumn,
["institution", "maturity", "treasuryNew", "treasuryOld"] as name,
["STRING", "STRING", "DOUBLE", "DOUBLE"] as type
)With this mapping, source columns in Excel can be renamed as needed and assigned explicit DolphinDB data types. Data that originally depended on cell positions and formatting in a spreadsheet can be loaded as a well-structured DolphinDB table. The entire process runs directly on the DolphinDB server, with no need to export intermediate formats such as CSV.
2. Validate Data Before It Enters Computation
In practice, Excel templates change as the business evolves. If the header, starting row, or cell types change, the data may still be readable but could lead to incorrect downstream results.
To address this, the plugin lets you configure an expected header and convert data to specified target types. For example, you can set row 3 as the header row and require it to match the expected column names:
options = dict(STRING, ANY)
options["headerRow"] = 3
options["dataStartRow"] = 4
options["expectedHeader"] = ["Institution", "Maturity", "Treasury-New", "Treasury-Old"]
excel::readSheet(filePath, "Cash Bond Trading", columnSpec, options)During reading, the plugin validates the header against options and converts values to the target types defined in columnSpec. If the file format or data types don't match expectations, the problem is caught before the data enters downstream computation.
3. Process Multiple Sheets Together
A single workbook often holds several sheets, for example one per business type or time period. If these sheets share the same structure, DolphinDB can read and merge them a single table:
sheets = excel::listSheets(filePath)
tables = array(ANY, 0)
for (sheetName in sheets) {
tables.append!(excel::readSheet(filePath, sheetName, columnSpec, options))
}
result = unionAll(tables, false)Data that was scattered across sheets becomes a single DolphinDB table, ready for cleansing, joins, aggregation, and computation.
Extending the Boundaries of Data Ingestion
For DolphinDB ecosystem partners, when business data is delivered as Excel files, the plugin offers a standardized way to load it directly into DolphinDB. This provides a unified entry point for downstream processing and computation while reducing the need to repeatedly implement file parsing, field mapping, and type conversion logic.
From databases and APIs to Excel, enterprise data now comes from, and is delivered through, an ever wider range of channels. Richer data ingestion capabilities let DolphinDB connect to more real-world business scenarios and make the path from data generation and delivery to processing and computation smoother.