Import CSV and Excel files with column mapping

[AliiiBenn]

Description

Allow users to import stock data from CSV and Excel files with a column mapping interface.

Setup Flow (First Run)

When the local storage is empty, show a setup page instead of the main warehouse view.

• Show setup page only when IndexedDB is empty
• Once data is imported, redirect to main warehouse view
• Setup page should only appear again if user clears all data

User Experience

Step 1: File Selection

Drop files here or click to browse. Accepts: .csv, .xlsx • Max: 10MB

Step 2: Preview

Show first 10 rows with column headers. Display row count. Allow user to go back.

Step 3: Column Mapping

Two-column layout: File columns on left, system fields dropdowns on right. Auto-detect matching columns. Reset mapping button.

Step 4: Validation

Show inline errors (missing required fields, invalid quantities) and warnings (duplicates, negative values). Allow import with warnings option.

Step 5: Import Complete

Success message with imported count. Options to view products or import another file.

System Fields

type ImportField = {
  key: 'sku' | 'name' | 'quantity' | 'sector' | 'zone' | 'floor' | 'description' | 'category' | 'unit'
  label: string
  required: boolean
  defaultValue?: string
}

Data Storage (Dexie.js)

Database Schema

import Dexie, { Table } from 'dexie'

type Warehouse = {
  id?: number
  name: string
  floors: number
  createdAt: Date
  updatedAt: Date
}

type Sector = {
  id?: number
  warehouseId: number
  name: string
  createdAt: Date
  updatedAt: Date
}

type Zone = {
  id?: number
  sectorId: number
  name: string
  floor: number
  positionX: number
  positionY: number
  width: number
  height: number
  color?: string
  createdAt: Date
  updatedAt: Date
}

type Product = {
  id?: number
  sku: string
  name: string
  quantity: number
  sectorId?: number
  zoneId?: number
  floor: number
  positionX?: number
  positionY?: number
  description?: string
  category?: string
  unit: string
  createdAt: Date
  updatedAt: Date
}

class WarehouseDB extends Dexie {
  warehouses!: Table<Warehouse>
  sectors!: Table<Sector>
  zones!: Table<Zone>
  products!: Table<Product>

  constructor() {
    super('wareflow')
    this.version(1).stores({
      warehouses: '++id, name',
      sectors: '++id, warehouseId, name',
      zones: '++id, sectorId, name, floor',
      products: '++id, sku, sectorId, zoneId, floor, category'
    })
  }
}

export const db = new WarehouseDB()

Check if Setup Required

const isSetupRequired = async (): Promise<boolean> => {
  const productCount = await db.products.count()
  return productCount === 0
}

Import Products

const importProducts = async (
  data: ParsedData[],
  mapping: ColumnMapping
): Promise<ImportResult> => {
  // Get or create default warehouse
  let warehouse = await db.warehouses.toCollection().first()
  if (!warehouse) {
    const warehouseId = await db.warehouses.add({
      name: 'Main Warehouse',
      floors: 6,
      createdAt: new Date(),
      updatedAt: new Date()
    })
    warehouse = { id: warehouseId, name: 'Main Warehouse', floors: 6, createdAt: new Date(), updatedAt: new Date() }
  }

  // Get or create sector
  const sectorName = data[0]?.[mapping.sector] || 'Default'
  let sector = await db.sectors.where('name').equals(sectorName).first()
  if (!sector) {
    const sectorId = await db.sectors.add({
      warehouseId: warehouse.id!,
      name: sectorName,
      createdAt: new Date(),
      updatedAt: new Date()
    })
    sector = { id: sectorId, warehouseId: warehouse.id!, name: sectorName, createdAt: new Date(), updatedAt: new Date() }
  }

  const products: Omit<Product, 'id'>[] = data.map(row => ({
    sku: row[mapping.sku] || '',
    name: row[mapping.name] || '',
    quantity: parseNumber(row[mapping.quantity]) || 0,
    sectorId: sector.id,
    zoneId: undefined,
    floor: parseNumber(row[mapping.floor]) || 0,
    positionX: undefined,
    positionY: undefined,
    description: row[mapping.description] || undefined,
    category: row[mapping.category] || undefined,
    unit: row[mapping.unit] || 'pcs',
    createdAt: new Date(),
    updatedAt: new Date()
  }))

  await db.products.bulkAdd(products)
  return { imported: products.length }
}

Clear All Data (Reset)

const resetApp = async (): Promise<void> => {
  await db.products.clear()
  await db.zones.clear()
  await db.sectors.clear()
  await db.warehouses.clear()
}

Validation Rules

type ValidationResult = {
  isValid: boolean
  errors: ValidationError[]
  warnings: ValidationWarning[]
}

Edge Cases

Case	Handling
Empty file	Show error: "File is empty"
No headers	Prompt: "First row appears to be data. Use as headers?"
Large file (>10MB)	Show error with file size
Invalid encoding	Try UTF-8, then ISO-8859-1, show warning

Priority

High - Core functionality