Imports

Read, transform, and validate spreadsheet data with the import pipeline -- ToArray, ToCollection, heading rows, mapping, validation, batching, and more.

The import pipeline uses the same concern-based pattern as exports. Build an import class, implement one or more interfaces to describe how data should be read, transformed, and validated, then pass it to ExcelService.import().

Quick Example

import {
  ToCollection,
  WithHeadingRow,
  WithValidation,
  SkipsOnError,
} from "@nestbolt/excel";

class UsersImport
  implements ToCollection, WithHeadingRow, WithValidation, SkipsOnError
{
  readonly hasHeadingRow = true as const;
  readonly skipsOnError = true as const;

  handleCollection(rows: Record<string, any>[]) {
    for (const row of rows) {
      console.log(row.name, row.email);
    }
  }

  rules() {
    return {
      name: [{ validate: (v) => v?.length > 0, message: "Name is required" }],
      email: [
        { validate: (v) => /^.+@.+\..+$/.test(v), message: "Invalid email" },
      ],
    };
  }
}

// In your controller or service:
const result = await this.excelService.import(new UsersImport(), "users.xlsx");

Data Receivers

Every import needs a data receiver that defines how the parsed rows are delivered. Implement one of the following.

ToArray

Receive imported data as a two-dimensional array. Each inner array is one row of cell values.

import { ToArray } from "@nestbolt/excel";

class DataImport implements ToArray {
  handleArray(rows: any[][]) {
    // rows = [[1, "Alice", "alice@example.com"], [2, "Bob", "bob@example.com"]]
    for (const row of rows) {
      console.log(`ID: ${row[0]}, Name: ${row[1]}, Email: ${row[2]}`);
    }
  }
}

ToArray is the simplest receiver. It does not require headings or column mapping.

ToCollection

Receive imported data as an array of objects. Object keys are derived from the heading row (via WithHeadingRow) or from explicit column mapping (via WithColumnMapping).

import { ToCollection, WithHeadingRow } from "@nestbolt/excel";

class UsersImport implements ToCollection, WithHeadingRow {
  readonly hasHeadingRow = true as const;

  handleCollection(rows: Record<string, any>[]) {
    // rows = [{ ID: 1, Name: "Alice", Email: "alice@example.com" }, ...]
    for (const row of rows) {
      console.log(row.Name, row.Email);
    }
  }
}

If you use ToCollection without WithHeadingRow or WithColumnMapping, the library cannot derive object keys and the rows will be arrays instead.

Row Processing

WithHeadingRow

Use a row in the spreadsheet as column headings to derive object keys. Set the hasHeadingRow marker property to true.

import { WithHeadingRow } from "@nestbolt/excel";

class ImportWithHeadings implements WithHeadingRow {
  readonly hasHeadingRow = true as const;
}

By default, row 1 is used as the heading row. To use a different row, implement the optional headingRow() method:

class ImportWithCustomHeading implements WithHeadingRow {
  readonly hasHeadingRow = true as const;

  headingRow() {
    return 3; // row 3 contains the column headers
  }
}

When headingRow() returns 3, rows 1 and 2 are skipped, row 3 is read as headings, and data begins at row 4.

WithImportMapping

Transform each row after it has been read and (optionally) converted to an object. This is separate from the export WithMapping interface, so a single class can implement both import and export mapping without conflict.

import { ToCollection, WithHeadingRow, WithImportMapping } from "@nestbolt/excel";

class MappedImport implements ToCollection, WithHeadingRow, WithImportMapping {
  readonly hasHeadingRow = true as const;

  handleCollection(rows: Record<string, any>[]) {
    // rows contain the transformed shape
  }

  mapRow(row: Record<string, any>) {
    return {
      fullName: `${row.first_name} ${row.last_name}`,
      email: row.email?.toLowerCase(),
      age: parseInt(row.age, 10),
      isActive: row.status === "active",
    };
  }
}

The mapRow() method receives the raw row (as an object if using WithHeadingRow, or as an array otherwise) and returns the transformed row. The return value is what gets passed to handleCollection() or handleArray(), and what appears in the ImportResult.rows array.

WithColumnMapping

Map column letters or 1-based indices to named fields. This is useful for files that do not have a header row, or when you want to assign specific names to columns regardless of the header values.

import { ToCollection, WithColumnMapping } from "@nestbolt/excel";

class NoHeaderImport implements ToCollection, WithColumnMapping {
  handleCollection(rows: Record<string, any>[]) {
    // rows = [{ name: "Alice", email: "alice@example.com", age: 30 }, ...]
  }

  columnMapping() {
    return {
      name: "A",     // column A maps to "name"
      email: "C",    // column C maps to "email"
      age: 2,        // column index 2 (B) maps to "age"
    };
  }
}

Values can be column letters ("A", "B", "C") or 1-based column indices (1, 2, 3).

Validation

WithValidation

Validate imported rows using either custom rule functions or class-validator DTOs.

Custom Rules

Return an object where keys are field names and values are arrays of ValidationRule objects:

import { ToCollection, WithHeadingRow, WithValidation } from "@nestbolt/excel";

class ValidatedImport implements ToCollection, WithHeadingRow, WithValidation {
  readonly hasHeadingRow = true as const;

  handleCollection(rows: Record<string, any>[]) {
    // Only valid rows arrive here
  }

  rules() {
    return {
      name: [
        { validate: (v) => v != null && v.length > 0, message: "Name is required" },
        { validate: (v) => v?.length <= 100, message: "Name must be 100 characters or fewer" },
      ],
      email: [
        { validate: (v) => v != null && v.length > 0, message: "Email is required" },
        { validate: (v) => /^.+@.+\..+$/.test(v), message: "Email format is invalid" },
      ],
      age: [
        { validate: (v) => v != null, message: "Age is required" },
        {
          validate: (v) => Number.isInteger(v) && v >= 0 && v <= 150,
          message: "Age must be an integer between 0 and 150",
        },
      ],
    };
  }
}

Each ValidationRule has:

Property	Type	Description
`validate`	`(value: any, row: Record<string, any>) => boolean`	Returns `true` if the value is valid.
`message`	`string`	Error message when validation fails.

The validate function receives the cell value as the first argument and the entire row object as the second, allowing cross-field validation:

rules() {
  return {
    endDate: [
      {
        validate: (v, row) => new Date(v) > new Date(row.startDate),
        message: "End date must be after start date",
      },
    ],
  };
}

DTO Validation with class-validator

For class-validator integration, return { dto: YourDtoClass } from rules(). Each row is instantiated as the DTO class and validated using class-validator decorators.

import { IsString, IsEmail, IsNotEmpty, IsInt, Min, Max } from "class-validator";

class UserDto {
  @IsString()
  @IsNotEmpty()
  name!: string;

  @IsEmail()
  email!: string;

  @IsInt()
  @Min(0)
  @Max(150)
  age!: number;
}

import { ToCollection, WithHeadingRow, WithValidation } from "@nestbolt/excel";

class DtoImport implements ToCollection, WithHeadingRow, WithValidation {
  readonly hasHeadingRow = true as const;

  handleCollection(rows: Record<string, any>[]) {
    // Validated rows
  }

  rules() {
    return { dto: UserDto };
  }
}

DTO validation requires class-validator and class-transformer as peer dependencies:

npm install class-validator class-transformer

Error Handling

By default, when validation fails for any row, the import throws an error with all collected validation errors attached. The ImportResult will not be returned.

To change this behavior, use SkipsOnError.

Row Skipping and Limits

WithStartRow

Skip rows before a given 1-based row number. Data reading begins at the specified row.

import { ToArray, WithStartRow } from "@nestbolt/excel";

class SkippedImport implements ToArray, WithStartRow {
  handleArray(rows: any[][]) {
    // rows start from row 5
  }

  startRow() {
    return 5; // skip rows 1-4
  }
}

When combined with WithHeadingRow, startRow() specifies where data begins after the heading. The heading row is still read from its configured position.

WithLimit

Limit the number of data rows read. Only the first N data rows (after any heading or start-row offset) are processed.

import { ToCollection, WithHeadingRow, WithLimit } from "@nestbolt/excel";

class LimitedImport implements ToCollection, WithHeadingRow, WithLimit {
  readonly hasHeadingRow = true as const;

  handleCollection(rows: Record<string, any>[]) {
    // At most 500 rows
  }

  limit() {
    return 500;
  }
}

SkipsEmptyRows

Ignore blank rows during import. This is a marker concern.

import { ToArray, SkipsEmptyRows } from "@nestbolt/excel";

class CleanImport implements ToArray, SkipsEmptyRows {
  readonly skipsEmptyRows = true as const;

  handleArray(rows: any[][]) {
    // No empty rows in the result
  }
}

A row is considered empty when all of its cells are null, undefined, or empty strings.

SkipsOnError

Skip invalid rows instead of throwing an error. Without this concern, the first validation failure causes the import to throw. With it, invalid rows are excluded from result.rows and their errors are collected in result.errors.

import { ToCollection, WithHeadingRow, WithValidation, SkipsOnError } from "@nestbolt/excel";

class TolerantImport
  implements ToCollection, WithHeadingRow, WithValidation, SkipsOnError
{
  readonly hasHeadingRow = true as const;
  readonly skipsOnError = true as const;

  handleCollection(rows: Record<string, any>[]) {
    // Only valid rows
  }

  rules() {
    return {
      email: [
        { validate: (v) => /^.+@.+\..+$/.test(v), message: "Invalid email" },
      ],
    };
  }
}

const result = await this.excelService.import(new TolerantImport(), "data.xlsx");
console.log(`Valid: ${result.rows.length}`);
console.log(`Errors: ${result.errors.length}`);
console.log(`Skipped: ${result.skipped}`);

Batch Processing

WithBatchInserts

Process imported rows in configurable batch sizes. Instead of receiving all rows at once, handleBatch() is called multiple times with chunks of rows. This is useful for large files where you want to insert records into a database without loading everything into memory.

import { ToCollection, WithHeadingRow, WithBatchInserts } from "@nestbolt/excel";

class BatchImport implements ToCollection, WithHeadingRow, WithBatchInserts {
  readonly hasHeadingRow = true as const;

  constructor(private readonly userRepo: UserRepository) {}

  handleCollection(rows: Record<string, any>[]) {
    // Called after all batches are processed (optional)
  }

  batchSize() {
    return 100;
  }

  async handleBatch(batch: Record<string, any>[]) {
    await this.userRepo.save(batch);
  }
}

The batchSize() method returns the maximum number of rows per batch. The handleBatch() method is called once for each batch and can be async.

Import Result

All import methods return an ImportResult object:

interface ImportResult<T = any> {
  rows: T[];                        // valid, processed rows
  errors: ImportValidationError[];   // per-row validation errors
  skipped: number;                   // count of skipped rows
}

interface ImportValidationError {
  row: number;                      // 1-based row number in the spreadsheet
  errors: FieldError[];             // field-level errors for this row
}

interface FieldError {
  field: string;                    // field/column name
  messages: string[];               // validation error messages
}

Inspecting Errors

const result = await this.excelService.import(new UsersImport(), "users.xlsx");

if (result.errors.length > 0) {
  for (const rowError of result.errors) {
    console.log(`Row ${rowError.row}:`);
    for (const fieldError of rowError.errors) {
      console.log(`  ${fieldError.field}: ${fieldError.messages.join(", ")}`);
    }
  }
}

// Example output:
// Row 5:
//   email: Invalid email
// Row 12:
//   name: Name is required
//   age: Age must be an integer between 0 and 150

ExcelService Import Methods

import

Read and process a file from storage through the import class's concerns:

const result = await this.excelService.import(
  new UsersImport(),
  "uploads/users.xlsx",
);

// Read from a specific disk
const result = await this.excelService.import(
  new UsersImport(),
  "uploads/users.xlsx",
  undefined,  // auto-detect type from extension
  "s3",
);

importFromBuffer

Read and process a buffer directly. Useful when the file comes from a multipart upload or another in-memory source:

import { ExcelType } from "@nestbolt/excel";

@Post("import")
@UseInterceptors(FileInterceptor("file"))
async importUsers(@UploadedFile() file: Express.Multer.File) {
  const result = await this.excelService.importFromBuffer(
    new UsersImport(),
    file.buffer,
    ExcelType.XLSX,
  );

  return {
    imported: result.rows.length,
    errors: result.errors.length,
    skipped: result.skipped,
  };
}

toArray (Shorthand)

Read a file and return the raw two-dimensional array without an import class:

const rows = await this.excelService.toArray("data.xlsx");
// rows = [[1, "Alice", "alice@example.com"], [2, "Bob", "bob@example.com"]]

// Read from a specific disk
const rows = await this.excelService.toArray("data.xlsx", undefined, "s3");

toCollection (Shorthand)

Read a file and return an array of objects using row 1 as headings. No import class needed:

const users = await this.excelService.toCollection("users.xlsx");
// users = [{ ID: 1, Name: "Alice", Email: "alice@example.com" }, ...]

// Read from a specific disk
const users = await this.excelService.toCollection("users.xlsx", undefined, "gcs");

Method Signatures

Method	Returns	Description
`import(importable, filePath, type?, disk?)`	`ImportResult`	Read and process a file from storage
`importFromBuffer(importable, buffer, type?)`	`ImportResult`	Read and process a buffer
`toArray(filePath, type?, disk?)`	`any[][]`	Shorthand: returns raw 2D array
`toCollection(filePath, type?, disk?)`	`Record<string, any>[]`	Shorthand: returns objects using row 1 as headings

Complete Import Example

Here is a full import class combining multiple concerns:

import {
  ToCollection,
  WithHeadingRow,
  WithImportMapping,
  WithValidation,
  WithStartRow,
  WithLimit,
  SkipsEmptyRows,
  SkipsOnError,
  WithBatchInserts,
} from "@nestbolt/excel";

class EmployeeImport
  implements
    ToCollection,
    WithHeadingRow,
    WithImportMapping,
    WithValidation,
    WithStartRow,
    WithLimit,
    SkipsEmptyRows,
    SkipsOnError,
    WithBatchInserts
{
  readonly hasHeadingRow = true as const;
  readonly skipsEmptyRows = true as const;
  readonly skipsOnError = true as const;

  constructor(private readonly employeeRepo: EmployeeRepository) {}

  handleCollection(rows: Record<string, any>[]) {
    console.log(`Imported ${rows.length} employees`);
  }

  headingRow() {
    return 2; // headers are on row 2
  }

  startRow() {
    return 3; // data starts at row 3
  }

  limit() {
    return 10000; // process at most 10,000 rows
  }

  mapRow(row: Record<string, any>) {
    return {
      name: row.employee_name?.trim(),
      email: row.email?.toLowerCase().trim(),
      department: row.dept,
      salary: parseFloat(row.annual_salary),
      startDate: new Date(row.hire_date),
    };
  }

  rules() {
    return {
      name: [
        { validate: (v) => v?.length > 0, message: "Name is required" },
      ],
      email: [
        { validate: (v) => /^.+@.+\..+$/.test(v), message: "Invalid email" },
      ],
      salary: [
        { validate: (v) => !isNaN(v) && v > 0, message: "Salary must be a positive number" },
      ],
    };
  }

  batchSize() {
    return 250;
  }

  async handleBatch(batch: Record<string, any>[]) {
    await this.employeeRepo.save(batch);
  }
}

Usage in a controller:

@Post("employees/import")
@UseInterceptors(FileInterceptor("file"))
async importEmployees(@UploadedFile() file: Express.Multer.File) {
  const result = await this.excelService.importFromBuffer(
    new EmployeeImport(this.employeeRepo),
    file.buffer,
  );

  return {
    imported: result.rows.length,
    failed: result.errors.length,
    skipped: result.skipped,
    errors: result.errors.map((e) => ({
      row: e.row,
      issues: e.errors.map((f) => `${f.field}: ${f.messages.join(", ")}`),
    })),
  };
}

On this page