Import System Guide

The B12 SIS includes a comprehensive data import system that supports Excel file uploads with field mapping and background processing.

Overview

The import workflow consists of:

Upload: Upload Excel file and get preview
Map: Map Excel columns to system fields
Validate: System validates data before import
Process: Background job processes the import
Review: Check results and handle errors

Import Workflow

┌─────────────┐     ┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   Upload    │────▶│    Map      │────▶│   Start     │────▶│  Process    │
│   Excel     │     │   Fields    │     │   Import    │     │  (Background)│
└─────────────┘     └─────────────┘     └─────────────┘     └─────────────┘
                                                                   │
                                                                   ▼
                                                            ┌─────────────┐
                                                            │   Review    │
                                                            │   Results   │
                                                            └─────────────┘

Supported Modules

All major modules support import:

Students
Teachers
Classes
Courses
Guardians
And more…

Step 1: Upload File

Upload an Excel file to create an import record.

Endpoint: POST /api/import_datas/upload

Request: Multipart form data

Field	Type	Description
`file`	File	Excel file (.xlsx, .xls)
`module`	String	Target module (e.g., “Student”)

cURL Example:

curl -X POST http://localhost:8080/api/import_datas/upload \
  -H "Authorization: Bearer $TOKEN" \
  -H "X-Team-ID: $TEAM_ID" \
  -F "file=@students.xlsx" \
  -F "module=Student"

Response:

{
  "success": true,
  "message": "File uploaded successfully",
  "data": {
    "id": "import-uuid",
    "module": "Student",
    "file_name": "students.xlsx",
    "file_url": "https://s3.../imports/students.xlsx",
    "status": "pending",
    "total_rows": 100,
    "preview": [
      {
        "row": 1,
        "data": {
          "A": "First Name",
          "B": "Last Name",
          "C": "Email"
        }
      },
      {
        "row": 2,
        "data": {
          "A": "John",
          "B": "Doe",
          "C": "john.doe@school.edu"
        }
      }
    ],
    "available_fields": [
      {
        "name": "first_name",
        "label": "First Name",
        "type": "text",
        "required": false
      },
      {
        "name": "last_name",
        "label": "Last Name",
        "type": "text",
        "required": false
      },
      {
        "name": "email",
        "label": "Email",
        "type": "email",
        "required": false
      },
      {
        "name": "code",
        "label": "Student Code",
        "type": "text",
        "required": false
      }
    ]
  }
}

Step 2: Get Importable Fields

Get the list of fields that can be imported for a module.

Endpoint: GET /api/admin/importable-fields/:module

Response:

{
  "success": true,
  "data": [
    {
      "name": "first_name",
      "label": "First Name",
      "label_vi": "Tên",
      "type": "text",
      "required": false
    },
    {
      "name": "last_name",
      "label": "Last Name",
      "label_vi": "Họ",
      "type": "text",
      "required": false
    },
    {
      "name": "status",
      "label": "Status",
      "type": "enum",
      "options": ["active", "applicant", "alumni"]
    },
    {
      "name": "current_class_id",
      "label": "Current Class",
      "type": "relate",
      "module": "Class",
      "lookup_field": "code"
    }
  ]
}

Step 3: Save Field Mapping

Map Excel columns to system fields.

Endpoint: PUT /api/import_datas/:id/mapping

Request:

{
  "mapping": {
    "A": "first_name",
    "B": "last_name",
    "C": "email",
    "D": "birthdate",
    "E": "gender",
    "F": "current_class_id"
  },
  "options": {
    "skip_header_row": true,
    "update_existing": true,
    "match_field": "email"
  }
}

Mapping Options

Option	Type	Description
`skip_header_row`	bool	Skip the first row (header)
`update_existing`	bool	Update records if they exist
`match_field`	string	Field to match existing records (e.g., “email”, “code”)
`create_missing_relations`	bool	Auto-create missing related records

Response:

{
  "success": true,
  "message": "Mapping saved",
  "data": {
    "id": "import-uuid",
    "mapping": {...},
    "validation_result": {
      "valid_rows": 95,
      "error_rows": 5,
      "warnings": [
        {
          "row": 10,
          "field": "email",
          "message": "Invalid email format"
        }
      ]
    }
  }
}

Step 4: Start Import

Begin the import process.

Endpoint: POST /api/import_datas/:id/start

Response:

{
  "success": true,
  "message": "Import started",
  "data": {
    "id": "import-uuid",
    "status": "processing"
  }
}

Step 5: Check Status

Monitor import progress.

Endpoint: GET /api/import_datas/:id/status

Response:

{
  "success": true,
  "data": {
    "id": "import-uuid",
    "status": "completed",
    "total_rows": 100,
    "processed_rows": 100,
    "success_rows": 95,
    "error_rows": 5,
    "progress_percent": 100,
    "started_at": "2024-01-15T10:00:00Z",
    "completed_at": "2024-01-15T10:02:30Z"
  }
}

Status Values

Status	Description
`pending`	File uploaded, waiting for mapping
`mapped`	Mapping saved, ready to start
`processing`	Import in progress
`completed`	Import finished
`failed`	Import failed
`cancelled`	Import cancelled by user

Step 6: Review Errors

Download error report for failed rows.

Endpoint: GET /api/import_datas/:id/errors/download

Returns an Excel file with error details:

Row	First Name	Last Name	Email	Error
10	John		john@test.com	Invalid email format
25		Doe	jane.doe@school.edu	First name is required

Cancel Import

Cancel a running import.

Endpoint: POST /api/import_datas/:id/cancel

Saved Mappings

Get Saved Mappings

Endpoint: GET /api/import_datas/mappings?module=Student

Response:

{
  "success": true,
  "data": [
    {
      "id": "mapping-uuid",
      "name": "Standard Student Import",
      "module": "Student",
      "mapping": {
        "A": "first_name",
        "B": "last_name",
        "C": "email"
      }
    }
  ]
}

Apply Saved Mapping

Include mapping_id when saving mapping:

{
  "mapping_id": "mapping-uuid",
  "options": {
    "skip_header_row": true
  }
}

Relation Field Import

Lookup by Code

For relate fields, the system looks up the related record:

Excel Data:

Class Code
10A
10B

Mapping:

{
  "A": "current_class_id"
}

The system finds the Class with code “10A” and uses its ID.

Lookup Field Configuration

Specify which field to use for lookup:

{
  "mapping": {
    "A": {
      "field": "current_class_id",
      "lookup_field": "code"
    }
  }
}

Multiple Lookup Fields

Try multiple fields in order:

{
  "A": {
    "field": "current_class_id",
    "lookup_fields": ["code", "name"]
  }
}

Excel Template

Download Template

Get a template with headers for a module:

Endpoint: GET /api/admin/importable-fields/:module?format=excel

Returns Excel file with:

Header row with field labels
Example data row
Field type hints in comments

Template Format

First Name	Last Name	Email	Birthdate	Gender	Current Class
John	Doe	john@school.edu	2010-05-15	Male	10A

Error Handling

Validation Errors

{
  "row": 10,
  "field": "email",
  "error_code": "INVALID_FORMAT",
  "message": "Invalid email format: 'notanemail'"
}

Relation Errors

{
  "row": 15,
  "field": "current_class_id",
  "error_code": "RELATION_NOT_FOUND",
  "message": "Class with code '99Z' not found"
}

Duplicate Errors

{
  "row": 20,
  "field": "email",
  "error_code": "DUPLICATE",
  "message": "Record with email 'john@school.edu' already exists"
}

Code Examples

JavaScript Import Flow

class ImportService {
  async uploadFile(file, module) {
    const formData = new FormData();
    formData.append('file', file);
    formData.append('module', module);

    const response = await fetch('/api/import_datas/upload', {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${token}`,
        'X-Team-ID': teamId
      },
      body: formData
    });

    return response.json();
  }

  async saveMapping(importId, mapping, options) {
    const response = await fetch(`/api/import_datas/${importId}/mapping`, {
      method: 'PUT',
      headers: {
        'Authorization': `Bearer ${token}`,
        'X-Team-ID': teamId,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({ mapping, options })
    });

    return response.json();
  }

  async startImport(importId) {
    const response = await fetch(`/api/import_datas/${importId}/start`, {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${token}`,
        'X-Team-ID': teamId
      }
    });

    return response.json();
  }

  async pollStatus(importId, onProgress) {
    const poll = async () => {
      const response = await fetch(`/api/import_datas/${importId}/status`, {
        headers: {
          'Authorization': `Bearer ${token}`,
          'X-Team-ID': teamId
        }
      });

      const data = await response.json();
      onProgress(data.data);

      if (data.data.status === 'processing') {
        setTimeout(poll, 2000);
      }
    };

    poll();
  }
}

Best Practices

Validate Before Import: Always preview and validate data before starting import
Use Match Fields: Enable update_existing with appropriate match_field to avoid duplicates
Small Batches: For large files, consider splitting into smaller batches
Error Review: Always review error reports and fix source data
Save Mappings: Save commonly used mappings for reuse
Date Formats: Use ISO 8601 (YYYY-MM-DD) for dates
Relation Codes: Use consistent codes for related records