📊 DataGrid — Excel Upload & Explorer

A modern full-stack application that lets you upload any Excel file, automatically creates a PostgreSQL table with dynamically inferred schema, detects duplicate uploads, and displays data with powerful sorting, pagination, and search — all without any hardcoded column definitions.

---

🌐 Live Demo

Frontend (Vercel)

DataGrid Frontend — Live

Backend API (Render)

DataGrid Backend API — Live

Replace the above links with your actual deployed URLs after completing deployment.

---

🚀 Features

📤 Excel Upload System

• Drag-and-drop or click-to-browse file upload
• Accepts .xlsx, .xls, and .csv formats
• Client-side Excel parsing using SheetJS (no server needed for parsing)
• Column detection and preview before uploading
• Upload progress indicator with animated progress bar
• Success and error state messages
• Form reset after successful upload

---

🧠 Dynamic Schema Inference

• No hardcoded table schema — columns are detected at runtime
• Automatic PostgreSQL type mapping:

Sample Values	Inferred Type
1, 42, 100	INTEGER
12.99, 3.50	NUMERIC
true / yes / false / no	BOOLEAN
2024-01-15	DATE
2024-01-15T10:00:00Z	TIMESTAMP
Anything else	TEXT

• Duplicate column names auto-deduplicated (name, name_2, name_3)
• Special characters in column headers sanitized to valid SQL identifiers

---

🔁 Duplicate File Detection & Smart Merge

• SHA-256 hash computed from file content on the frontend
• Backend checks hash against registry before inserting
• On duplicate detection, user is presented a choice:
  • Create New Table — always stores a fresh copy
  • Update Existing Table — smart row-level merge:
    • Identical rows → skipped
    • Modified rows (matched by ID column or row hash) → updated
    • Brand new rows → inserted
• Merge result shows: rows inserted / updated / skipped

---

📋 Data Explorer Table

• Fully dynamic column headers from any uploaded file
• Click column headers to sort (ASC / DESC toggle)
• Debounced search across all columns simultaneously
• Pagination with smart page number rendering
• Configurable page size (10 / 20 / 50 / 100 rows)
• Loading overlay while fetching
• Empty state with clear messaging

---

📁 Upload History

• Card grid of all previous uploads
• Shows: filename, row count, upload timestamp
• Click any card to instantly load and explore that dataset
• Active table highlighted

---

⚠️ Error Handling

• Invalid file type rejected on the frontend before any network call
• Empty Excel file detected and reported
• Inconsistent column counts handled gracefully (missing values → null)
• Backend validation with clear JSON error responses
• SQL injection prevention: all identifiers quoted; table names validated against registry

---

⚡ Performance

• Bulk insert with batched VALUES clauses (500 rows per batch)
• All inserts wrapped in a single database transaction
• Paginated queries with LIMIT / OFFSET
• Search uses ILIKE cast — no full-text index required
• 50,000 row upload limit with friendly error message
• 50MB payload limit

---

🛠️ Tech Stack

Frontend

Technology	Purpose
React 18	UI framework
TypeScript	Type safety
Vite	Build tool & dev server
Axios	HTTP client
SheetJS (xlsx)	Client-side Excel parsing
Custom CSS	Styling (no CSS framework dependency)

---

Backend

Technology	Purpose
Node.js	Runtime
Express.js	Web framework
TypeScript	Type safety
pg (node-postgres)	Database driver
crypto (built-in)	SHA-256 / MD5 hashing
dotenv	Environment variable loading

---

Database

Technology	Purpose
PostgreSQL	Primary database
Neon	Cloud-hosted PostgreSQL
Raw SQL	Dynamic CREATE TABLE, INSERT, UPDATE, SELECT

---

Deployment

Service	Purpose
Vercel	Frontend hosting
Render	Backend API hosting
Neon	Cloud PostgreSQL database

---

🏗️ System Architecture

Frontend (React + Vite + TypeScript)  [Vercel]
              │
              │  Axios — HTTPS API calls
              ▼
Backend API (Node.js + Express + TypeScript)  [Render]
              │
              │  node-postgres (pg)
              ▼
PostgreSQL Database  [Neon Cloud]

---

📂 Project Structure

excel-upload-app/
│
├── frontend/                          # React Vite application
│   ├── src/
│   │   ├── components/
│   │   │   ├── UploadZone.tsx         # Drag-drop upload + hash + duplicate prompt
│   │   │   ├── DataTable.tsx          # Dynamic table: sort, search, paginate
│   │   │   └── UploadHistory.tsx      # Past upload cards
│   │   ├── utils/
│   │   │   ├── api.ts                 # Axios API client
│   │   │   └── excelParser.ts         # SheetJS Excel → JSON conversion
│   │   ├── types/index.ts             # TypeScript interfaces
│   │   ├── App.tsx                    # Root component with tab navigation
│   │   ├── main.tsx                   # React entry point
│   │   └── styles.css                 # Full application CSS
│   ├── index.html
│   ├── vite.config.ts
│   ├── vercel.json                    # Vercel deployment config
│   ├── .env.example
│   └── package.json
│
├── backend/                           # Express API server
│   ├── src/
│   │   ├── controllers/
│   │   │   ├── uploadController.ts    # POST /upload, POST /check-hash, POST /merge
│   │   │   └── dataController.ts      # GET /data, GET /uploads
│   │   ├── services/
│   │   │   ├── uploadService.ts       # Dynamic table creation, bulk insert, merge logic
│   │   │   └── dataService.ts         # Paginated + sorted + searched queries
│   │   ├── routes/
│   │   │   └── api.ts                 # All route definitions
│   │   ├── middleware/
│   │   │   └── errorHandler.ts        # Global error handler
│   │   ├── utils/
│   │   │   ├── db.ts                  # PostgreSQL pool (supports Neon + local)
│   │   │   ├── typeInference.ts       # Column type detection logic
│   │   │   └── hashUtils.ts           # SHA-256 file hash, MD5 row hash
│   │   └── index.ts                   # Express app entry point
│   ├── tsconfig.json
│   ├── render.yaml                    # Render deployment config
│   ├── .env.example
│   └── package.json
│
├── .gitignore
└── README.md

---

⚙️ Local Installation & Setup

Prerequisites

• Node.js 18+
• PostgreSQL 13+ (or a free Neon account for cloud DB)
• npm

---

1️⃣ Clone the Repository

git clone https://github.com/YOUR_USERNAME/excel-upload-app.git
cd excel-upload-app

---

2️⃣ Database Setup

Option A — Local PostgreSQL

psql -U postgres
CREATE DATABASE excel_upload_db;
\q

Option B — Neon Cloud (Free)

1. Go to neon.tech → Create account → New project
2. Copy the Connection String — it looks like: postgresql://user:password@ep-xxx.us-east-2.aws.neon.tech/neondb?sslmode=require
3. Use this as DATABASE_URL in your .env

---

3️⃣ Backend Setup

cd backend
cp .env.example .env

Edit .env:

PORT=3001

# Option A — Neon or any PostgreSQL connection string
DATABASE_URL=postgresql://user:password@host/dbname?sslmode=require

# Option B — Individual variables (local PostgreSQL)
DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=yourpassword
DB_NAME=excel_upload_db

# Set this AFTER deploying the frontend
FRONTEND_URL=http://localhost:5173

npm install
npm run dev

Backend starts at: http://localhost:3001 Health check: http://localhost:3001/api/health

---

4️⃣ Frontend Setup

cd frontend
cp .env.example .env

Edit .env:

# During local dev, leave this unset — the Vite proxy handles /api → localhost:3001
# VITE_API_URL=http://localhost:3001/api

npm install
npm run dev

Frontend starts at: http://localhost:5173

---

☁️ Deployment Guide

Step 1 — Push to GitHub

git init
git add .
git commit -m "initial commit: DataGrid Excel Upload App"
git branch -M main
git remote add origin https://github.com/YOUR_USERNAME/excel-upload-app.git
git push -u origin main

---

Step 2 — Deploy Database on Neon

1. Go to neon.tech → Sign up / Log in
2. Click New Project → give it a name (e.g. datagrid-db)
3. Choose a region closest to your Render backend (e.g. US East)
4. Once created, go to Dashboard → Connection Details
5. Copy the Connection String (the one with ?sslmode=require at the end)
6. Keep this safe — you'll need it for Render

The app auto-creates all tables on first run. No migrations needed.

---

Step 3 — Deploy Backend on Render

1. Go to render.com → Sign up / Log in
2. Click New → Web Service
3. Connect your GitHub repository
4. Configure:

Setting	Value
Name	datagrid-backend
Root Directory	backend
Runtime	Node
Build Command	npm install && npm run build
Start Command	npm start

5. Under Environment Variables, add:

Key	Value
NODE_ENV	production
PORT	3001
DATABASE_URL	(paste your Neon connection string)
FRONTEND_URL	(leave blank for now — update after Step 4)

6. Click Create Web Service
7. Wait for the first deploy to finish (~2-3 minutes)
8. Copy your Render URL: https://datagrid-backend-xxxx.onrender.com

---

Step 4 — Deploy Frontend on Vercel

1. Go to vercel.com → Sign up / Log in
2. Click Add New → Project
3. Import your GitHub repository
4. Configure:

Setting	Value
Root Directory	frontend
Framework Preset	Vite
Build Command	npm run build
Output Directory	dist

5. Under Environment Variables, add:

Key	Value
VITE_API_URL	https://datagrid-backend-xxxx.onrender.com/api

6. Click Deploy
7. Copy your Vercel URL: https://datagrid-xxxx.vercel.app

---

Step 5 — Connect Frontend URL to Backend (CORS)

1. Go back to Render → your backend service → Environment
2. Update FRONTEND_URL to your Vercel URL: https://datagrid-xxxx.vercel.app
3. Click Save Changes — Render will auto-redeploy

---

Step 6 — Test Everything

Open your Vercel URL → Upload an Excel file → Verify data appears in the table.

Health check: https://datagrid-backend-xxxx.onrender.com/api/health

---

🔍 API Reference

Endpoints

Method	Path	Description
GET	/api/health	Health check
POST	/api/check-hash	Check if a file hash already exists
POST	/api/upload	Upload rows → create new table
POST	/api/merge	Merge rows into existing table
GET	/api/data	Fetch table data with filters
GET	/api/uploads	List all past uploads

---

POST /api/check-hash

Request body:

{ "fileHash": "abc123..." }

Response — duplicate found:

{ "exists": true, "tableName": "products_1720000000000" }

Response — no duplicate:

{ "exists": false }

---

POST /api/upload

Request body:

{
  "rows": [{ "Name": "Alice", "Age": 30 }, ...],
  "fileName": "employees.xlsx",
  "fileHash": "abc123..."
}

Response:

{
  "success": true,
  "message": "Successfully uploaded 150 rows.",
  "tableName": "employees_1720000000000",
  "rowsInserted": 150,
  "columns": [
    { "originalName": "Name", "sanitizedName": "name", "type": "TEXT" },
    { "originalName": "Age",  "sanitizedName": "age",  "type": "INTEGER" }
  ],
  "fileHash": "abc123..."
}

---

POST /api/merge

Request body:

{
  "tableName": "employees_1720000000000",
  "rows": [...],
  "fileHash": "def456..."
}

Response:

{
  "success": true,
  "message": "Merge complete: 10 inserted, 5 updated, 135 unchanged.",
  "tableName": "employees_1720000000000",
  "rowsInserted": 10,
  "rowsUpdated": 5,
  "rowsSkipped": 135
}

---

GET /api/data

Query Param	Type	Default	Description
tableName	string	required	Target table
page	number	1	Page number
pageSize	number	20	Rows per page (max 200)
sortColumn	string	_row_id	Column to sort by
sortDirection	ASC|DESC	ASC	Sort direction
search	string	—	Search across all columns

---

🧠 Core Logic Explained

Dynamic Table Creation

When you upload a file, the backend:

1. Receives a JSON array of rows from the frontend
2. Reads column names from the first row's keys
3. Analyzes all values in each column to infer the best PostgreSQL type
4. Sanitizes column names (lowercase, spaces → underscores, deduplicated)
5. Generates a unique table name: <filename>_<timestamp>
6. Executes CREATE TABLE with the inferred schema
7. Bulk-inserts all rows in batches of 500 inside a single transaction
8. Records the upload in _upload_registry

---

Duplicate Detection

1. Frontend computes a SHA-256 hash of the entire JSON row array
2. Sends a POST /api/check-hash request before uploading
3. Backend queries _upload_registry for a matching file_hash
4. If found → returns { exists: true, tableName: "..." }
5. Frontend shows the Create New Table / Update Existing Table prompt

---

Smart Row Merge

When the user chooses Update Existing Table:

1. Backend fetches all existing rows with their _row_hash and natural ID column
2. For each incoming row:
   • Hash matches exactly → skip (row is identical)
   • ID column matches but hash differs → UPDATE all fields (row was modified)
   • No match at all → INSERT as a new row
3. Returns a count of inserted / updated / skipped rows

---

SQL Injection Prevention

• All table names are validated against _upload_registry before any query
• All column and table identifiers are wrapped in double quotes: "column_name"
• User input (search terms, sort columns) is passed as parameterized $1 values
• Sort column is validated against the actual column list from information_schema

---

⚠️ Edge Case Handling

Edge Case	How It's Handled
Non-Excel file	Rejected on frontend before any API call
Empty file	Detected by SheetJS; error shown to user
Inconsistent column count	Missing values become null; warning logged
Duplicate column names	Sanitizer appends _2, _3 suffix
Special characters in headers	Replaced with underscores
Headers starting with a digit	Prefixed with _
Currency values ($12.99)	$ and , stripped; stored as NUMERIC
Large files (>50,000 rows)	Rejected with friendly message
Failed transaction	Full ROLLBACK; no partial data stored
Null / empty cells	Coerced to null safely

---

🔮 Future Improvements

• Docker Compose for one-command local setup
• GitHub Actions CI/CD pipeline
• Column type override UI before upload
• Export table back to Excel / CSV
• Multi-sheet Excel file support
• Chart / visualization tab for numeric columns
• User authentication (JWT)
• Per-user isolated datasets
• Table deletion from UI
• Row-level edit and delete
• Webhook notifications on upload
• Redis caching for repeated queries

---

🤝 Contributing

Contributions, suggestions, and bug reports are welcome.

# Fork → Clone → Branch → Commit → Push → Pull Request
git checkout -b feature/your-feature-name
git commit -m "feat: describe your change"
git push origin feature/your-feature-name

---

📄 License

This project is licensed under the MIT License.

---

👨‍💻 Developer

Developed by Yogeshwaran S

---

🔗 Repository

https://github.com/YOUR_USERNAME/excel-upload-app

---

⭐ Support

If you found this project useful:

• ⭐ Star the repository
• 🍴 Fork the project
• 🐛 Report bugs via Issues
• 💡 Suggest features via Discussions

---

📌 Project Status

✅ Active Development ✅ Production Deployed ✅ Dynamic Schema Inference ✅ Duplicate File Detection ✅ Smart Row-Level Merge ✅ Full Stack TypeScript ✅ Neon Cloud Database Connected ✅ Production Ready Architecture