Excel and CSV Import to Database: Encoding, Mapping, and Batching

2026-07-02

  • Excel
  • CSV
  • SQL
  • SQL Server
  • import
  • tutorial

End-to-end path: spreadsheet to database

Business teams hand you Excel or CSV; you need rows in SQL Server, MySQL, or similar. The usual path:

  1. Clean the file (headers, encoding, dedup)
  2. Generate SQL with Excel to SQL—INSERT, UPDATE, or DELETE
  3. Run on a test database; verify rows and columns
  4. Import to production in batches

This guide covers the pitfalls that break imports. Use it with the INSERT, UPDATE, and DELETE series.

Supported file formats

Format Supported Notes
.xlsx, .xlsm, .xltx, .xltm Yes Preferred; pick sheet when multiple
.csv Yes UTF-8, comma-separated; BOM auto-detected
.xls (Excel 97-2003) No Save as .xlsx in Excel first

Headers: Row 1 must be column names; data starts at row 2. Missing headers or merged cells break mapping.

CSV encoding and garbled text

The tool reads CSV as UTF-8 and detects BOM.

Symptom Cause Fix
Garbled Chinese File is GBK/GB2312 Save as “CSV UTF-8” or check via CSV to Excel
Misaligned columns Commas inside unquoted fields Fix in Excel, save as .xlsx, then upload
Odd first column UTF-8 BOM Usually parsed fine; re-save without BOM if needed

Tip: Standardize on UTF-8 for handoffs; mind export encoding from SQL tools.

Column names and mapping

  • Match DB column names when possible.
  • Spaces in Excel headers are trimmed; remap in the grid (e.g. User Name → UserName).
  • Table names like dbo.TableName work; #TempTable adds CREATE / preview INSERT / DROP—good for staging before INSERT INTO ... SELECT.

Include only columns that belong in the database; drop notes, row numbers, etc.

Data types in generated SQL

Cell content SQL output
Text Single-quoted; ' → ''
Dates Quoted; prefer yyyy-MM-dd or ISO in source
Integers / decimals Unquoted
Empty cells NULL
Yes/No booleans Treated as text or number—must match schema

Type errors (e.g. "N/A" into int) need cleanup in Excel or ETL via a staging table.

Which statement type?

Goal Type Guide
Load new rows INSERT INSERT guide; enable batch INSERT
Change existing rows UPDATE UPDATE guide
Remove by key DELETE DELETE guide
Check IDs exist Select Generates WHERE col IN (...) for verification

Large data strategies

  • 50,000 rows max per generation—split sheets or files beyond that.
  • For thousands of rows, download .sql instead of long in-browser preview.
  • Execute in batches of 500–1000 in SSMS or your client; watch locks and logs.
  • Consider disabling nonclustered indexes before bulk load, then rebuild.
  • Verify with @@ROWCOUNT or SELECT COUNT(*).

Batch INSERT merges ~500 rows per INSERT ... VALUES (...), (...) statement for fewer round trips.

Pre-import cleanup

  1. Dedup—avoid duplicate keys (emails, order IDs).
  2. Merge—combine files before one import.
  3. Format—consistent dates and headers.
  4. Web tables: HTML to Excel first, then SQL—fewer column breaks.

Multiple SQL dialects

Excel to SQL supports SQL Server, MySQL, PostgreSQL, SQLite, and Oracle. Identifier quoting and some syntax follow the selected dialect. Generate scripts for the target engine—do not reuse another dialect’s output blindly.

Privacy and file handling

Files are parsed server-side to produce SQL, then deleted—not stored long term. Still:

  • De-identify production data when possible
  • Prefer test environments
  • For large PII datasets, consider private deployment or offline tooling

Import checklist

[ ] CSV is UTF-8; headers are correct
[ ] Test run on staging (INSERT/UPDATE/DELETE)
[ ] Row counts match the spreadsheet
[ ] Strings, dates, NULL match table schema
[ ] Production backup taken
[ ] Script executed in batches
[ ] Business spot-check after import

Excel to SQL series

More resources

中文版