CSV Importer

Laravel 12 single-page utility for loading very large order CSV files, tracking the ingest in real time, and exploring the imported data with a server-side DataTable. The project targets MySQL installations that allow LOAD DATA LOCAL INFILE, but it automatically falls back to a chunked PHP importer when that command is unavailable.

Highlights

• Dual import strategies: blazing-fast LOAD DATA INFILE with a fully validated PHP fallback (app/Services/OrderLoadDataImporter.php, app/Services/OrderImporter.php).
• Progress-aware uploads with resumable status polling via order_imports records and duplicate/validation sample capture.
• Dashboard UI (resources/views/orders) built with Bootstrap 5 + DataTables that streams rows via GET /orders/data.
• Dedicated duplicate browser that exposes captured collisions when the fallback strategy runs.
• Drop-in sample CSV fixtures (5m Sales Records.csv, small.csv) for demos and profiling.

---

Requirements

• PHP >= 8.2 with mbstring, pdo_mysql, and fileinfo.
• Composer 2.5+, Node 20+/npm 10+ for the front-end toolchain.
• MySQL 8.0+ (or MariaDB 10.5+) with local_infile=ON. When it is OFF the fallback importer is used automatically.
• Writable default filesystem disk (FILESYSTEM_DISK=local in .env) because uploaded files are staged under storage/app/imports.
• Optional: a queue worker if you later offload imports, but the current implementation is synchronous.

---

Quick Start

1. Clone & install

   composer install
   npm install

2. Copy the environment file and configure it for MySQL

   cp .env.example .env

   Update at minimum APP_URL, DB_CONNECTION=mysql, DB_*, FILESYSTEM_DISK=local, and ensure your MySQL user can run LOAD DATA LOCAL INFILE.
3. Generate the app key and run the database migrations

   php artisan key:generate
   php artisan migrate --seed

   Seeding creates a development admin user (admin@example.com / password) via Database\Seeders\AdminUserSeeder.
4. Build assets / run Vite

   npm run build     # or npm run dev for watch mode

5. Serve the application

   php artisan serve

   Visit http://localhost:8000/ to reach the importer dashboard.

Tip: composer run setup performs steps 1–4 (install, .env copy, key generation, migrate, NPM install, Vite build). composer run dev starts PHP's dev server, a queue listener, Laravel Pail, and Vite in watch mode via npx concurrently.

---

CSV Format Expectations

Column	Required	Example	Notes
Region	Yes	Sub-Saharan Africa	Trimmed and stored as-is.
Country	Yes	Kenya	Indexed for faster filtering.
Item Type	Yes	Office Supplies	Matches Item Type header casing.
Sales Channel	Yes	Online	Free-text string.
Order Priority	Yes	H	Only the first character is used.
Order Date	Nullable	1/6/2011	Accepts multiple formats; stored as date.
Order ID	Yes	731057	Stored as unsignedBigInteger and must be unique.
Ship Date	Nullable	1/13/2011	Same parsing rules as order date.
Units Sold	Yes	995	Parsed as integer.
Unit Price / Unit Cost	Yes	651.21	Parsed as decimal(12,2).
Total Revenue / Total Cost / Total Profit	Yes	647,603.95	Parsed as decimal(18,2).

Use the bundled small.csv for smoke tests or the 5M-record file for load testing.

---

Import Workflow

1. The dashboard posts the file to POST /orders/import.
2. A record is created in order_imports capturing filenames, strategy, counters, and timestamps.
3. The app tries LOAD DATA LOCAL INFILE first (OrderLoadDataImporter). This path prioritizes throughput and lets MySQL handle casting.
4. If MySQL rejects the statement, the code flips to the PHP chunk importer (OrderImporter) which:
   • Streams rows with LazyCollection.
   • Validates each row, normalizes dates and decimals, and avoids inserting duplicates already in the table or earlier in the batch.
   • Records up to 100 duplicate and 100 failed row samples for debugging.
5. Browsers poll GET /orders/imports/{orderImport} for live metrics and can resume polling after refresh via GET /orders/imports/latest.
6. When the fallback importer runs, detailed duplicates can be inspected at /orders/imports/{orderImport}/duplicates.

---

Routes & Features

Method	Path	Name	Controller	Feature
GET	/	orders.index	OrderPageController@index	Renders the dashboard with uploader, progress card, and DataTable shell.
GET	/orders/data	orders.data	OrderPageController@data	Server-side DataTables endpoint (filters, keyword search, pagination).
POST	/orders/import	orders.import	OrderImportController@import	Upload a CSV and kick off either LOAD DATA INFILE or the PHP fallback importer.
GET	/orders/imports/latest	orders.import.latest	OrderImportController@latest	Returns the newest order_imports row so the UI can resume polling after refresh.
GET	/orders/imports/{orderImport}	orders.import.status	OrderImportController@show	JSON payload of counts, timestamps, strategy, and optional duplicate/failure samples.
GET	/orders/imports/{orderImport}/duplicates	orders.import.duplicates	OrderImportController@duplicates	Blade view for exploring captured duplicates (only populated in fallback mode).
GET	/orders/imports/{orderImport}/duplicates/data	orders.import.duplicates.data	OrderImportController@duplicatesData	DataTables JSON for duplicates with search/sort/pagination helpers.

routes/api.php is intentionally left empty so far. If you need programmatic CRUD access to orders, register Route::apiResource('orders', OrderController::class); and add authentication/rate-limiting before exposing it.

---

Database Changes

File	Description
2025_11_07_041438_create_orders_table.php	Creates the orders table with order metadata, numerics, and indexes on order_id (unique), order_date, and country.
2025_11_07_120000_create_order_imports_table.php	Tracks each import (file names, counts, status, chunk metadata, timestamps).
2024_05_29_000001_add_failed_at_to_order_imports_table.php	Adds a dedicated failed_at timestamp for better auditing.
2025_11_09_150000_add_report_columns_to_order_imports_table.php	Persists the strategy and JSON columns (duplicate_samples, failed_samples) used by the UI.
Default Laravel migrations	Users, cache, jobs, and sessions to support authentication, queueing, and server-side sessions.

Seeders live under database/seeders. AdminUserSeeder creates a development login and runs automatically from DatabaseSeeder.

---

Project Structure Notes

• app/Services contains the two import strategies and supporting helpers (OrderImporter, OrderLoadDataImporter).
• app/Support/CsvRowCounter.php counts rows efficiently for progress tracking.
• resources/views/orders holds the dashboard UI and duplicate inspector; both pages rely on CDN-hosted Bootstrap/DataTables plus a local public/js/jquery-3.7.1.min.js fallback.
• order_imports act as the single source of truth for progress. Each record exposes computed fields (percentage, is_terminal) via model accessors for consistent JSON responses.

---

Testing

Unit/feature coverage is minimal right now (tests/Feature/ExampleTest.php). Expand it as you harden the importer, then run:

php artisan test
# or
composer test

---

Troubleshooting

• LOAD DATA LOCAL INFILE disabled: run SHOW VARIABLES LIKE 'local_infile'; and set local_infile=1. If your host prevents this, the fallback importer will still work—expect slower throughput.
• Uploads hang or fail: ensure storage/app/imports exists and is writable by PHP. The importer creates it automatically, but shared hosts sometimes block mkdir.
• Duplicate viewer empty: only the PHP fallback captures row-level samples. Force the fallback by disabling local_infile temporarily if you need to inspect duplicates.
• Large CSVs timing out in the browser: increase upload_max_filesize, post_max_size, and max_execution_time, or split the CSV before uploading.

---

Next Steps

• Secure the routes by introducing authentication/middleware before deploying publicly.
• Wire up the pending API surface (OrderController) for automation use cases.
• Add queue/offline import support if uploads begin to block request threads.