npm package: @manaty/smart-csv-import
Description: Smart CSV import library with schema validation, auto-mapping, and optional AI-assisted column mapping
Latest version: 1.0.6

# Smart CSV Import

A framework-agnostic CSV import library with schema validation, auto-mapping, and optional AI-assisted column mapping.

**[Live Demo & Schema Builder](https://manaty.net/smart-csv-import/)** - Try the component, build schemas visually, and test CSV imports.

## Features

- **Schema-based validation**: Define your target CSV structure with column types, validation rules, and defaults
- **Auto column mapping**: Automatically maps source columns to target schema using case-insensitive matching
- **AI-assisted mapping**: Optional AI integration for intelligent column mapping when auto-mapping fails
- **Inline editing**: Edit data directly in the preview table before export
- **Error handling**: Validates each row and provides detailed error information
- **Framework support**: Core logic is framework-agnostic, with Svelte components included

## Installation

```bash
npm install @manaty/smart-csv-import papaparse
```

## Quick Start

```svelte
<script>
  import { CSVImporter } from '@manaty/smart-csv-import/svelte';
  import { toCSV } from '@manaty/smart-csv-import/core';
  import type { TransformResult } from '@manaty/smart-csv-import/core';

  const schema = {
    $schema: 'csv-import-schema/v1',
    name: 'products',
    version: '1.0',
    columns: [
      { name: 'sku', format: 'text', mandatory: true },
      { name: 'name', format: 'text', mandatory: true },
      { name: 'price', format: 'currency', mandatory: true },
    ],
  };

  let transformedFile: File | undefined;

  function handleTransform(result: TransformResult) {
    if (result.validRows.length > 0) {
      // Create a File from valid rows for upload
      const csv = toCSV(result.validRows, schema.columns.map(c => c.name));
      transformedFile = new File([csv], 'import.csv', { type: 'text/csv' });
    }
  }
</script>

<CSVImporter {schema} onTransform={handleTransform} />

<button on:click={() => uploadFile(transformedFile)}>Upload</button>
```

## AI-Assisted Column Mapping

When source CSV columns don't match your schema exactly (e.g., "First Name" vs "first_name"), AI can intelligently suggest mappings. There are several ways to configure this:

### Option 1: No AI (Auto-mapping only)

Without `aiConfig`, the component uses simple name matching (case-insensitive, ignores separators like `_` and `-`).

```svelte
<CSVImporter {schema} onTransform={handleTransform} />
```

### Option 2: Direct AI Call (User provides their API key)

The component calls the AI API directly from the browser. Use this when users provide their own API key (e.g., through a settings panel or input field).

```svelte
<script>
  // User enters their own API key
  let userApiKey = '';  // From user input

  // OpenAI
  $: aiConfig = userApiKey ? {
    provider: 'openai',
    apiKey: userApiKey,
    model: 'gpt-4o-mini',       // Optional, defaults to gpt-4o-mini
  } : undefined;

  // Or Anthropic
  $: aiConfig = userApiKey ? {
    provider: 'anthropic',
    apiKey: userApiKey,
    model: 'claude-3-haiku-20240307',
  } : undefined;
</script>

<input type="password" bind:value={userApiKey} placeholder="Enter your OpenAI API key" />
<CSVImporter {schema} {aiConfig} onTransform={handleTransform} />
```

### Option 3: Backend Proxy (Recommended for Production)

The component sends mapping data to your backend, which calls the AI API. **Your AI API key stays secure on the server.**

```svelte
<script>
  const aiConfig = {
    provider: 'structured',
    apiKey: userAuthToken,      // Your app's auth token (NOT the AI key)
    endpoint: '/api/csv/map-columns',
  };
</script>

<CSVImporter {schema} {aiConfig} onTransform={handleTransform} />
```

**Backend request format:**
```json
POST /api/csv/map-columns
Authorization: Bearer <userAuthToken>

{
  "sourceColumns": ["First Name", "Last Name", "Email Address"],
  "sampleData": [
    {"First Name": "John", "Last Name": "Doe", "Email Address": "john@example.com"}
  ],
  "schema": [
    {"name": "first_name", "format": "text", "mandatory": true},
    {"name": "last_name", "format": "text", "mandatory": true},
    {"name": "email", "format": "email", "mandatory": true}
  ],
  "hints": "optional user-provided hints",
  "model": "gpt-4o-mini"
}
```

**Backend response format:**
```json
{
  "mapping": {
    "First Name": "first_name",
    "Last Name": "last_name",
    "Email Address": "email"
  }
}
```

**Backend implementation:** Use the exported `buildMappingPrompt` and `parseAIResponse` functions to keep prompt logic centralized:

```typescript
import { buildMappingPrompt, parseAIResponse } from '@manaty/smart-csv-import';
import OpenAI from 'openai';

const openai = new OpenAI();

async function mapColumns(req) {
  const { sourceColumns, sampleData, schema, hints, model } = req.body;

  // Build prompt using the library's function
  const prompt = buildMappingPrompt(sourceColumns, sampleData, schema, hints);

  const response = await openai.chat.completions.create({
    model: model || 'gpt-4o-mini',
    messages: [{ role: 'user', content: prompt }],
    temperature: 0.1,
  });

  // Parse response using the library's function
  const mapping = parseAIResponse(response.choices[0].message.content);

  return { mapping };
}
```

> See the [Backend Integration](#backend-integration) section below for more details and a complete NestJS example.

## Working with Transformed Data

The `onTransform` callback provides the transformed data:

```typescript
interface TransformResult {
  data: Record<string, unknown>[];        // All transformed rows
  validRows: Record<string, unknown>[];   // Rows without validation errors
  errorRows: Record<string, unknown>[];   // Rows with validation errors
  errors: Map<number, ValidationError[]>; // Row index -> errors
  stats: { total: number; valid: number; invalid: number };
}
```

**Creating a File for upload:**
```typescript
import { toCSV } from '@manaty/smart-csv-import/core';

function handleTransform(result: TransformResult) {
  const csvContent = toCSV(result.validRows, schema.columns.map(c => c.name));
  const file = new File([csvContent], 'import.csv', { type: 'text/csv' });

  // Upload to your server
  const formData = new FormData();
  formData.append('file', file);
  fetch('/api/import', { method: 'POST', body: formData });
}
```

## Schema Definition

Use the [Schema Builder](https://manaty.net/smart-csv-import/) to create schemas visually.

```typescript
interface Schema {
  $schema: string;           // e.g., 'csv-import-schema/v1'
  name: string;              // Schema name
  version: string;           // Schema version
  description?: string;      // Optional description
  columns: SchemaColumn[];   // Column definitions
}

interface SchemaColumn {
  name: string;              // Column name (key in output)
  format: ColumnFormat;      // Validation type
  mandatory: boolean;        // Required field
  defaultValue?: string;     // Default if empty
  validation?: string;       // Custom regex pattern
  description?: string;      // Help text
}

type ColumnFormat =
  | 'text'      // Any text
  | 'number'    // Numeric (allows currency symbols)
  | 'integer'   // Whole numbers only
  | 'decimal'   // Decimal numbers
  | 'date'      // Date (parsed by Date.parse)
  | 'datetime'  // DateTime
  | 'email'     // Email address
  | 'phone'     // Phone (min 7 digits)
  | 'boolean'   // true/false/yes/no/1/0
  | 'url'       // Valid URL
  | 'currency'; // Numeric with currency symbols
```

## Backend Integration

If you're using the **Backend Proxy** approach (Option 3), your backend needs to implement an endpoint that handles AI column mapping. This package exports helper functions to keep the prompt logic centralized.

### Installation (Backend)

```bash
npm install @manaty/smart-csv-import openai
```

### Backend Functions

Use the `/backend` entry point to avoid pulling in frontend dependencies:

```typescript
import { buildMappingPrompt, parseAIResponse } from '@manaty/smart-csv-import/backend';
```

> **Note**: The `/backend` entry point only exports the AI mapping utilities without any dependencies on `papaparse` or `svelte`.

| Function | Description |
|----------|-------------|
| `buildMappingPrompt(sourceColumns, sampleData, schema, hints?)` | Builds the AI prompt for column mapping |
| `parseAIResponse(content)` | Parses the AI response to extract the mapping object |

### Why Use These Functions?

- **Centralized logic**: The prompt format is maintained in one place
- **Consistency**: Frontend and backend use the same mapping logic
- **Easy updates**: Prompt improvements automatically apply everywhere

### Quick Example (Express)

```typescript
import express from 'express';
import { buildMappingPrompt, parseAIResponse } from '@manaty/smart-csv-import';
import OpenAI from 'openai';

const app = express();
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });

app.post('/api/csv/map-columns', express.json(), async (req, res) => {
  const { sourceColumns, sampleData, schema, hints, model } = req.body;

  // Build prompt using centralized function
  const prompt = buildMappingPrompt(sourceColumns, sampleData, schema, hints);

  const response = await openai.chat.completions.create({
    model: model || 'gpt-4o-mini',
    messages: [{ role: 'user', content: prompt }],
    temperature: 0.1,
  });

  // Parse response using centralized function
  const mapping = parseAIResponse(response.choices[0].message.content);
  res.json({ mapping });
});
```

See [`examples/nestjs-integration.ts`](./examples/nestjs-integration.ts) for a complete NestJS implementation with DTOs, service, and controller.

## API Reference

### Core Functions

```typescript
import {
  parseCSV,           // Parse CSV file
  autoMapColumns,     // Auto-map columns to schema
  applyMapping,       // Apply mapping and validate
  validateRow,        // Validate single row
  toCSV,              // Convert data to CSV string
  downloadCSV,        // Trigger CSV download
  aiMapColumns,       // AI-assisted mapping (direct call)
  buildMappingPrompt, // Build AI prompt (for backends)
  parseAIResponse,    // Parse AI response (for backends)
} from '@manaty/smart-csv-import/core';
```

> **Note**: For backends using older module resolution (e.g., NestJS), import directly from the package root:
> ```typescript
> import { buildMappingPrompt, parseAIResponse } from '@manaty/smart-csv-import';
> ```

### Svelte Components

#### `<CSVImporter>`

| Prop | Type | Default | Description |
|------|------|---------|-------------|
| `schema` | `Schema` | required | Target schema |
| `aiConfig` | `AIProviderConfig` | - | AI configuration |
| `maxFileSizeMb` | `number` | `20` | Max file size |
| `showSchemaPreview` | `boolean` | `true` | Show schema info |
| `showMappingDetails` | `boolean` | `true` | Show mapping info |
| `enableInlineEditing` | `boolean` | `true` | Allow cell editing |
| `pageSize` | `number` | `10` | Rows per page |
| `onParse` | `function` | - | CSV parsed callback |
| `onMap` | `function` | - | Mapping done callback |
| `onTransform` | `function` | - | Transform done callback |
| `onError` | `function` | - | Error callback |

#### `<SchemaBuilder>`

| Prop | Type | Default | Description |
|------|------|---------|-------------|
| `initialSchema` | `Schema` | - | Initial schema to edit |
| `onChange` | `function` | - | Schema changed callback |

## Demo

- **Online**: [https://manaty.net/smart-csv-import/](https://manaty.net/smart-csv-import/)
- **Local**: Open `demo/index.html` in a browser

## License

MIT