C3 AI Documentation Home

Load Excel Files with Data Integration

The C3 Agentic AI Platform loads .xlsx workbooks like any other file format. The platform reads each workbook as a stream so large workbooks do not have to fit in memory. Legacy .xls, .xlsm, and .xlsb files are not supported. A single file can hold many sheets. Every record keeps the name of the sheet it came from, so a transform can route or label data by sheet.

Understand how an Excel file loads

Loading an Excel file uses the same pipeline as any other file format. You need three pieces:

A source collection corresponds to the schema of an entire Excel file, not to an individual sheet. The platform reads all sheets in the file through the collection's single shared source Type. The shared source Type is the union of the columns you import from those sheets. Transforms then map that source Type to one or more downstream target Types, optionally one target per sheet.

Excel files follow these rules:

  • The platform selects the Excel deserializer from the file's content type. Legacy .xls, .xlsm, and .xlsb files are not supported.

  • When the file store reports no content type, the platform derives it from the extension. A .xlsx file then resolves to the Excel content type (application/vnd.c3.data+excel).

  • When the file store reports a content type for the file, the platform keeps it. A .xlsx file stored with another type does not load as Excel. To read every file in the collection as Excel, set contentTypeOverride on the collection:

    JavaScript
    // fsc is your FileSourceCollection.
fsc.config().setConfigValue("contentTypeOverride", "application/vnd.c3.data+excel");

  • A workbook can contain one or many sheets. By default, the platform processes every sheet in the file against the same source Type.

  • The platform reads each sheet independently, so sheets in the same workbook can have different columns. The first row of each sheet is that sheet's header row.

  • Each record carries the name of the sheet it came from. Transforms see this value as the sourceFileSheet field on the source object.

  • Excel support is read-only. The platform reads Excel files; it does not serialize objects to Excel.

Read a workbook directly

You do not need a source collection or a schema to read an Excel file. Any Content, including a File, exposes read methods you can call directly. Use these read methods to inspect a workbook or to script a one-off load.

Use readJsonLines to stream each row as a JSON object, with no C3 Type required. The first row of each sheet supplies the column names:

JavaScript
// file is a File; File mixes Content.
file.readJsonLines(FileObjsOperSpec.make());

Use readObjs instead when you want typed objects, passing the Type with withSerType:

JavaScript
file.readObjs(FileObjsOperSpec.make().withSerType(CanonicalUserImport));

For a typed read of a multi-sheet workbook, use withSerTypePerSheet to deserialize each sheet into its own Type. Any sheet you do not map falls back to withSerType, or to Obj if you set no default:

JavaScript
file.readObjs(FileObjsOperSpec.make()
  .withSerTypePerSheet({ "People": CanonicalUserImport, "Products": CanonicalProductImport }));

Restrict any of these calls to specific sheets with withSourceFileSheets. List the available sheet names first with getAllSheetNames:

JavaScript
file.getAllSheetNames();
file.readJsonLines(FileObjsOperSpec.make().withSourceFileSheets(["People", "Products"]));

When you read more than one sheet, rows stream in workbook sheet order. Any limit or offset on the spec applies to the combined stream across all sheets, not to each sheet individually.

Model the source schema

Define a single Source (or Canonical) Type whose fields are the union of the columns across all sheets that you read. A FileSourceCollection maps to exactly one source Type. The platform reads all sheets through one collection and deserializes them against this one schema.

Generate the schema from a file

Use FileSourceCollection schema inference to derive the Type from a sample workbook. Drop a representative file into the collection's inbox, then infer:

JavaScript
// fsc is your FileSourceCollection. Returns a TypeMeta you can inspect.
fsc.inferSourceType2(InferTypeSpec.make().withParseFields(true));

Pass withParseFields(true) to resolve field value types (int, double, boolean, and so on) from the data itself. inferSourceType returns the same information as JSON if you prefer to consume it that way.

For Excel, inference works as follows:

  • For a multi-sheet workbook, the inferred Type contains the union of the columns across all sheets.
  • If the inbox holds several .xlsx files, the inferred Type merges the columns found across those files.
  • Inference respects sourceFileSheets. If you restrict the collection to specific sheets, the inferred Type contains only the columns from those sheets.

Write the schema by hand

When you already know the columns, declare the Type yourself. This Type models four columns:

Type
type CanonicalUserImport mixes Canonical {

  id:        string
  firstName: string
  lastName:  string
  email:     string
}

When a sheet does not contain one of the Type's columns, the records read from that sheet have no value supplied for that field. Nullable fields stay null. Fields backed by a primitive take that primitive's default, so an absent boolean column reads as false, not null. Do not treat such a default as data present in the file.

For Data Integration you only ever need this one union Type. Per-sheet source Types apply only to direct reads. The serTypePerSheet option on FileObjsOperSpec deserializes each sheet into a different Type when you call readObjs yourself. Data Integration always deserializes every processed sheet into the collection's single source Type, so serTypePerSheet does not apply to a collection. To send different sheets to different destinations, keep the one union source Type. Then route each sheet to its own target downstream in a transform, as shown next.

Access the originating sheet in a transform

During import, the platform sets sourceFileSheet on each source record to the name of the sheet that holds the row. You use this field in a Transform to route records to different targets. The platform also records the sheet name automatically as record metadata. The source object itself does not keep the field. To keep the sheet name, the simplest option is to enable includeMeta, which stores it as meta.sourceFileSheet (see Query records by sheet). You need a dedicated target field only if you do not use metadata, or if the target Type is compact. A compact Type, declared with @db(compactType=true), stores no record metadata.

Route records by sheet

Use sourceFileSheet in a transform condition so each sheet flows to the appropriate target Type. These two transforms send Sheet1 records to one target and Sheet2 records to another:

JavaScript
data = {
  name: "CanonicalUserImport-UserSheet1",
  source: "CanonicalUserImport",
  target: "UserSheet1",
  condition: sourceFileSheet == "Sheet1",
  projection: {
    id: id,
    firstName: firstName,
    email: email
  }
};
JavaScript
data = {
  name: "CanonicalUserImport-UserSheet2",
  source: "CanonicalUserImport",
  target: "UserSheet2",
  condition: sourceFileSheet == "Sheet2",
  projection: {
    id: id,
    lastName: lastName,
    email: email
  }
};

Persist the sheet name as a target field (optional)

If you enable includeMeta, the platform already stores the sheet name as record metadata (meta.sourceFileSheet). A dedicated field is then unnecessary. Add a dedicated field when you want the sheet name as a field of its own on the target Type. For example, you can show the sheet name in the UI or reference the field from a calc field. A compact target Type (@db(compactType=true)) also needs a dedicated field. meta.sourceFileSheet is unavailable because compact Types do not store record metadata. To add the field, project sourceFileSheet onto a field you declare for it:

JavaScript
data = {
  name: "CanonicalUserImport-UserWithSheet",
  source: "CanonicalUserImport",
  target: "UserWithSheet",
  projection: {
    id: id,
    firstName: firstName,
    lastName: lastName,
    email: email,
    sheetName: sourceFileSheet
  }
};
Type
entity type UserWithSheet schema name 'USR_SHEET' {

  id:        string
  firstName: string
  lastName:  string
  email:     string
  sheetName: string
}

Restrict which sheets load

By default, the platform processes all sheets. To process only specific sheets, set the sourceFileSheets value on the SourceCollection.Config of the collection. sourceFileSheets is a list of sheet names. When the list is null or empty, the platform processes all sheets. This setting applies only to Excel content.

Set the sheet filter on the collection. The change takes effect immediately, with no separate save step:

JavaScript
// fsc is your FileSourceCollection.
fsc.config().setConfigValue("sourceFileSheets", ["Sheet1"]);

When you set sourceFileSheets, the platform behaves as follows:

  • The platform processes only the listed sheets and ignores the rest.
  • If some listed names match and others do not, the platform processes the matching sheets. The platform logs a warning that names the sheets it did not find.
  • If none of the listed sheets exist, or the workbook contains no sheets, the platform produces no records for that source. The platform records the reason in the import status.

To list the sheet names in a workbook before you configure the collection, call getAllSheetNames on the file's Content. The method returns the sheet names in workbook order. getAllSheetNames raises an error if the content is not an Excel file.

JavaScript
// content is the Content of an .xlsx file.
content.getAllSheetNames();

When the platform reads all sheets, it streams rows in workbook sheet order.

Read files that have no header row

By default, the platform treats the first row of each sheet as the header row and maps the remaining rows to fields by column name. For a workbook whose sheets contain data only, set both of the following on the SourceCollection.Config:

  • noCsvHeader: set to true so that the platform reads every row, including the first, as data.
  • csvHeaderOverride: a comma-separated list of column names, in column order, that maps columns to fields.
JavaScript
// fsc is your FileSourceCollection.
fsc.config().setConfigValue("noCsvHeader", true);
fsc.config().setConfigValue("csvHeaderOverride", "id,firstName,lastName,email");

For Excel content, you must set csvHeaderOverride whenever noCsvHeader is true. There is no header row to fall back on, so the read fails with an error if you set noCsvHeader without supplying column names. In a multi-sheet workbook, the column names you supply apply to every sheet that the platform processes.

Query records by sheet

If you enable includeMeta on the collection, the platform also writes the originating sheet to meta.sourceFileSheet on the persisted target objects. You can then query by sheet without adding a dedicated field to your target Type:

JavaScript
UserWithSheet.fetchCount({ filter: "meta.sourceFileSheet == 'Sheet1'" });

For more information about source-to-target metadata, see the section on tracing records to their source file in Configure a Source Collection.

Preview transforms before you persist

Run the transforms in memory before you process the collection, to confirm that your sheet routing produces the records you expect. DataIntegrator transformSources reads the collection's files and applies the transforms you name. transformSources returns the resulting target objects without writing to the database. Each returned object reflects the sourceFileSheet of its row. You can therefore check per-sheet conditions and projections against a real workbook before any data is written.

How cell values are converted

The platform converts each cell to the value type of the field it maps to.

  • Empty cells: read as null. The field stays unset.
  • Formula cells: the platform does not recalculate formula cells during import. The platform uses the value saved in the workbook. A formula with no saved value, or with an error result such as #DIV/0! or #N/A, reads as empty.
  • Whole numbers: Excel stores whole numbers as floating-point values. For a column mapped to an int field, the platform converts the value through a double and then truncates it toward zero. A cell holding 2.9 reads as 2. Map a column to a double or decimal field if you need to preserve the fractional part.
  • Booleans: read as true for true, 1, and yes, in any letter case. Any other non-empty value reads as false.

By default, a non-empty cell whose value cannot be converted to its target field type raises an error. The CSV, JSON, Parquet, and Avro deserializers behave the same way. Correct the data or the field mapping instead of suppressing the error. The ignoreBadContent option does not skip the offending row and continue; instead, it ends the read at the first failure. A bad value in the first data row therefore yields zero records and no error. That result is difficult to distinguish from an empty file.

Fix common problems

Each row starts from what you see:

SymptomCauseResolution
Import status reports No sheets found in XLSX file for source ...The workbook contains no sheets.Confirm the file is a valid workbook and is not empty.
Import status reports XLSX source ... does not contain any configured sourceFileSheets: ...None of the names in sourceFileSheets match a sheet in the workbook.List the sheet names with getAllSheetNames and correct sourceFileSheets. Sheet names are matched exactly.
Some sheets are skipped and a warning names sheets that were not foundSome names in sourceFileSheets match and others do not.Correct the unmatched names, or remove them if the omission is intentional.
noCsvHeader=true requires csvHeader to be specified for XLSX filesnoCsvHeader is set without a header list.Set csvHeaderOverride to the column names, in column order.
getAllSheetNames() can only be called on Excel files, got content type: ...The method was called on content that is not Excel.Confirm the file's content type resolves to Excel, normally through the .xlsx extension.
A .xlsx file does not loadThe file store reports a content type other than the Excel content type, and that type takes precedence over the extension.Set contentTypeOverride on the collection to application/vnd.c3.data+excel.
Failed to convert value ... for field ...A non-empty cell cannot be converted to its target field's value type.Correct the cell value, or map the column to a field whose type accepts it.
Zero records are produced and no error is reportedignoreBadContent is enabled and the first data row failed conversion, which ends the read.Disable ignoreBadContent to surface the underlying conversion error, then fix the data or the mapping.
XLSX parsing timed out waiting for data from producer or XLSX producer thread died unexpectedlyThe read stalled or the reader stopped before the file was fully processed.Retry the read. If the failure recurs on the same file, confirm the workbook is not corrupt and contact C3 AI support.

See also

Was this page helpful?