Import Specification¶

Detailed specification for importing Excel costing templates into CostEngine (MfgIQ).

Overview¶

The import module processes Excel costing templates and extracts structured data for cost calculation. This document defines:

• Supported file formats
• Required data fields
• Validation rules
• Error handling
• Mapping configurations

Supported File Formats¶

Excel Files¶

Maximum File Size: 10 MB
Maximum Sheets: 20 sheets per file
Maximum Rows: 10,000 rows per sheet

Standard Template Structure¶

Required Sheets¶

1. Part Info - Basic part and RFQ details
2. Material - Material specifications and BOM
3. Routing - Operations and process steps
4. Tooling - Tooling and fixture requirements

Optional Sheets¶

• Outside Process - Subcontracted operations
• Assumptions - Custom notes and overrides
• History - Previous quote references

Data Fields Specification¶

Part Info Sheet¶

Material Sheet¶

Routing Sheet¶

Routing data is in tabular format starting from row 5:

Multi-row Operations: Continue until empty row encountered

Validation Rules¶

Pre-Import Validation¶

1. File Structure
2. File must be valid Excel format
3. Must contain at least "Part Info", "Material", and "Routing" sheets

4. Sheet names are case-insensitive

5. Data Types

6. Numeric fields must contain valid numbers
7. Dates must be in Excel date format

8. Text fields must not exceed max length

9. Business Rules

10. Net Weight ≤ Gross Weight
11. At least one routing operation required
12. Material grade must exist in master data

Post-Import Validation¶

1. Calculated Fields
2. Yield % = Net Weight / Gross Weight
3. Yield must be between 50% and 100%

4. Total setup time should not exceed 24 hours

5. Cross-Sheet Validation

6. Operation machine types must exist in Machine Master
7. Material grade must exist in Material Master

8. Customer must exist in Customer Master (if strict mode enabled)

9. Warning Conditions (Non-blocking)

10. Cycle time > 24 hours
11. Scrap % > 20%
12. Missing RFQ date
13. No estimator name

Field Mapping Configuration¶

Auto-Detection¶

System attempts to auto-detect field locations by:

1. Searching for known field labels (case-insensitive)
2. Looking in common cell locations
3. Analyzing data patterns

Detection Confidence Levels: - High (>90%): Auto-import without confirmation - Medium (70-90%): Show preview, request confirmation - Low (<70%): Require manual mapping

Manual Mapping¶

For non-standard templates:

1. Upload Excel file
2. System shows preview
3. First 10 rows of each sheet
4. Detected field mappings highlighted
5. User maps fields
6. Drag-and-drop or dropdown selection
7. Required fields must be mapped
8. Save mapping template
9. Reusable for future uploads
10. Shareable across team

Mapping Template Schema:

{
  "template_name": "Legacy Format v2",
  "file_identifier": "*.xls",
  "mappings": {
    "part_number": {"sheet": "Header", "cell": "C5"},
    "material_grade": {"sheet": "Materials", "cell": "B10"},
    "routing_table": {
      "sheet": "Processes",
      "start_row": 8,
      "columns": {
        "operation_name": "A",
        "cycle_time": "D"
      }
    }
  }
}

Error Handling¶

Error Categories¶

Error Messages¶

Template:

[SEVERITY] [Category]: [Description]

Location: [Sheet Name] Cell [Cell Reference]
Current Value: [Value]
Expected: [Expected Format/Range]

Suggested Action:
1. [Action 1]
2. [Action 2]

Example:

ERROR Missing Required Field: Material Grade is required

Location: Material Sheet, Cell B3
Current Value: (empty)
Expected: Valid material grade (e.g., SAE4140, EN8, SS304)

Suggested Action:
1. Enter material grade in cell B3, OR
2. Select from Material Master dropdown

Import Process Flow¶

graph TD
    A[User Uploads Excel] --> B{File Valid?}
    B -->|No| C[Show Error & Reject]
    B -->|Yes| D{Known Template?}
    D -->|Yes| E[Auto-Map Fields]
    D -->|No| F[Manual Mapping UI]
    F --> G[User Maps Fields]
    G --> H[Save Mapping Template]
    E --> I{Validation Pass?}
    H --> I
    I -->|Critical Errors| J[Show Errors & Reject]
    I -->|Warnings Only| K[Show Warnings]
    I -->|Success| L[Import Data]
    K --> M{User Confirms?}
    M -->|Yes| L
    M -->|No| N[Cancel Import]
    L --> O[Create Quote Draft]
    O --> P[Redirect to Quote Builder]

Data Extraction Logic¶

Material Cost Data¶

1. Extract material grade from specified cell
2. Lookup current RM rate from Material Master
3. Apply override if present in Excel
4. Extract gross/net weights
5. Calculate yield percentage
6. Extract scrap percentage (default 0%)
7. Apply material scrap factor

Routing Data¶

1. Identify routing table range (start row to first empty row)
2. For each row:
3. Extract operation details
4. Validate operation sequence
5. Lookup machine rate from Machine Master
6. Apply override if present
7. Calculate setup cost per piece
8. Calculate cycle cost
9. Apply operation scrap factor

Cascading Calculations¶

1. Start with final quantity from Part Info
2. Work backward through operations
3. Apply scrap % at each stage
4. Calculate required input quantity for material
5. Store scrap factors for explainability

Import API¶

REST Endpoint¶

POST /api/v1/imports/excel

Request:

{
  "file": "base64_encoded_excel_content",
  "filename": "costing_template.xlsx",
  "mapping_template_id": "optional_template_id",
  "options": {
    "strict_validation": true,
    "auto_create_quote": true
  }
}

Response (Success):

{
  "status": "success",
  "import_id": "imp_20260124_001",
  "quote_id": "Q-2026-0042",
  "warnings": [
    {
      "field": "rfq_date",
      "message": "RFQ date not provided, using current date"
    }
  ],
  "summary": {
    "part_number": "RH-2024-05",
    "material": "SAE4140",
    "operations_count": 5,
    "estimated_cost": 529.00
  }
}

Response (Error):

{
  "status": "error",
  "errors": [
    {
      "field": "material_grade",
      "location": "Material!B3",
      "message": "Material grade 'XYZ123' not found in Material Master",
      "severity": "critical"
    }
  ],
  "import_id": "imp_20260124_001"
}

Performance Targets¶

Security Considerations¶

1. File Upload
2. Virus scan all uploads
3. Validate file extensions

4. Limit file size to prevent DoS

5. Data Privacy

6. Uploaded files stored encrypted
7. Auto-delete after 30 days if not imported

8. Access logged and auditable

9. Injection Prevention

10. Sanitize all text inputs
11. No formula execution from Excel
12. Escape special characters

Related Documentation¶

• Costing Formats Index → - Overview of all costing formats
• Export Specification → - Quote output format specification
• Excel Import Use Cases → - Detailed import scenarios
• Data Pipeline → - Technical architecture for data processing