Import Data from CSV/Excel

Overview

Importing data from CSV or Excel is a staple of admin panels, data migration tools, and bulk-update flows. The hard part is rarely the parsing — it’s everything around it. Users upload files with the wrong encoding, renamed columns, blank rows, and dates stored as floating-point serial numbers. A 500MB spreadsheet will happily eat all your RAM if you load it naively.

This recipe covers reliable CSV/Excel import in Python, JavaScript, and Java: streaming parsers, per-row validation with error collection, encoding detection, and batch inserts. If you only need to read a file — not ingest it into a system — see Parse CSV Files or Parse Excel Files, which cover the lighter read-only path.

When to Use

Use this resource when:

• Users upload spreadsheets to bulk-create or bulk-update records. See File Upload Validation for securing the upload itself, not just the data.
• You’re migrating data from legacy systems or external vendors. See Export CSV Excel for the reverse direction.
• You’re building ETL pipelines that process scheduled file drops.
• An admin panel needs an “upload and import” capability. See Background Jobs for moving long imports off the request thread.

Solution

Python (pandas + csv)

import csv
import pandas as pd
from pydantic import BaseModel, ValidationError

# Streaming CSV import with per-row validation
class UserImport(BaseModel):
    name: str
    email: str
    age: int

def import_users_csv(file_path):
    valid, errors = [], []
    with open(file_path, newline="", encoding="utf-8") as f:
        reader = csv.DictReader(f)
        for row_num, row in enumerate(reader, start=2):
            try:
                user = UserImport(**row)
                valid.append(user.model_dump())  # Pydantic v2; use .dict() on v1
            except (ValidationError, ValueError) as e:
                errors.append({"row": row_num, "error": str(e)})
    return valid, errors

# Excel import with pandas
def import_users_excel(file_path):
    df = pd.read_excel(file_path, sheet_name="Users")
    df = df.dropna(how="all")  # drop fully empty rows, keep partially filled ones
    return df.to_dict("records")

JavaScript (csv-parser + xlsx)

const csv = require("csv-parser");
const fs = require("fs");
const XLSX = require("xlsx");

function importCsv(filePath) {
  return new Promise((resolve, reject) => {
    const valid = [], errors = [];
    let rowNum = 1;

    fs.createReadStream(filePath)
      .pipe(csv())
      .on("data", (row) => {
        rowNum++;
        if (!row.name || !row.email) {
          errors.push({ row: rowNum, error: "Missing required field" });
        } else {
          valid.push(row);
        }
      })
      .on("end", () => resolve({ valid, errors }))
      .on("error", reject);
  });
}

function importExcel(filePath) {
  const workbook = XLSX.readFile(filePath);
  const sheet = workbook.Sheets[workbook.SheetNames[0]];
  const rows = XLSX.utils.sheet_to_json(sheet, { header: 1 });
  const headers = rows[0];
  return rows.slice(1).map((row) => {
    const obj = {};
    headers.forEach((h, i) => (obj[h] = row[i]));
    return obj;
  });
}

Java (Apache Commons CSV + Apache POI)

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.nio.file.*;
import java.util.*;

public class Importer {

    public List<Map<String, String>> importCsv(Path path) throws IOException {
        List<Map<String, String>> rows = new ArrayList<>();
        try (Reader reader = Files.newBufferedReader(path);
             CSVParser parser = new CSVParser(reader, CSVFormat.DEFAULT.withFirstRecordAsHeader())) {
            for (CSVRecord record : parser) {
                Map<String, String> row = new HashMap<>();
                record.toMap().forEach(row::put);
                rows.add(row);
            }
        }
        return rows;
    }

    public List<Map<String, String>> importExcel(Path path) throws IOException {
        List<Map<String, String>> rows = new ArrayList<>();
        try (Workbook workbook = new XSSFWorkbook(Files.newInputStream(path))) {
            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(cell.getStringCellValue());
            }
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                Map<String, String> map = new HashMap<>();
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    map.put(headers.get(j), cell != null ? cell.toString() : "");
                }
                rows.add(map);
            }
        }
        return rows;
    }
}

Explanation

Importing is harder than exporting because you don’t control the input. Users will upload files with missing headers, wrong types, extra blank rows, and whatever encoding their Excel install felt like. The reliable pattern is a pipeline:

1. Parse the file row by row. Streaming matters — csv.DictReader, csv-parser, and CSVParser all iterate without loading the whole file into memory.
2. Validate each row against a schema. In Python, let Pydantic coerce and validate instead of casting int(row["age"]) yourself — you get clearer errors for free.
3. Collect errors rather than stopping at the first bad row. Users fixing CSVs want the full error list in one shot, with row numbers.
4. Insert in batches: for database imports, insert in chunks inside a transaction (see Importing into a Database below).

Two rules of thumb: never trust the file, and never make the user retry. Files that pass validation still carry surprises — a column named email (trailing space), a sheet where row 1 is a title banner instead of headers — so normalize headers (.strip().lower()) before matching them to your schema.

Variants

Format	Library	Streaming?	Best For
CSV	Python csv	Yes	Small to medium files with custom validation
CSV	csv-parser (JS)	Yes	Node.js streaming pipeline
CSV	Apache Commons CSV	Yes	Java enterprise parsing
Excel	pandas.read_excel	No	Quick imports, data exploration
Excel	xlsx (JS)	No	Client-side or small server imports
Excel	Apache POI	Yes (event model)	Very large Excel files

Apache POI’s event model (XSSFReader + SAX) is the only one here that streams Excel — worth reaching for when a client hands you a 200k-row workbook. pandas.read_excel loads the entire sheet into a DataFrame, which is fine up to a few hundred megabytes but a wall beyond that.

Handling Real-World Files

The solutions above cover the happy path. These cover the files that actually land in production.

Detecting the encoding

UTF-8 is the right default, but Windows Excel exports in cp1252 and old exports come in latin-1. Try UTF-8 first, then fall back:

def open_csv_with_encoding(file_path):
    """Open a CSV trying UTF-8 first, then common Windows encodings."""
    for encoding in ("utf-8", "cp1252", "latin-1"):
        try:
            f = open(file_path, newline="", encoding=encoding)
            f.read(4096)  # probe the first bytes before committing
            f.seek(0)
            return f
        except UnicodeDecodeError:
            f.close()
    raise UnicodeDecodeError("unknown", b"", 0, 1, "could not decode file")

latin-1 decodes anything (every byte is a valid character), so it always succeeds — which is exactly why it goes last. If the file turns out to be UTF-16 (Excel “Unicode Text” exports), chardet/charset-normalizer detects it reliably.

Different delimiters

European locales export CSV with ; because , is the decimal separator. Sniff it instead of hardcoding:

import csv

def detect_delimiter(file_path: str) -> str:
    """Detect the most likely delimiter in a CSV file."""
    with open(file_path, "r", newline="", encoding="utf-8") as f:
        sample = f.read(1024)
        dialect = csv.Sniffer().sniff(sample, delimiters=",;\t|")
        return dialect.delimiter

delim = detect_delimiter("data.csv")
with open("data.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f, delimiter=delim)
    for row in reader:
        print(row)

Sniffing on a small sample misfires occasionally — single-column files have nothing to sniff. Let the user override the detected delimiter in your import UI.

Multiple Excel sheets

import pandas as pd

def import_multi_sheet_excel(file_path: str) -> dict[str, list[dict]]:
    """Import all sheets from an Excel file."""
    xl = pd.ExcelFile(file_path)
    result = {}
    for sheet_name in xl.sheet_names:
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        df = df.dropna(how="all")  # drop fully empty rows
        result[sheet_name] = df.to_dict("records")
    return result

Excel dates

Excel stores dates as serial numbers (days since 1900-01-01, minus its off-by-one quirk). Parse explicitly or you get floats like 45789.0 where a birthday should be:

import pandas as pd

def import_excel_with_dates(file_path: str) -> list[dict]:
    """Import Excel with proper date parsing."""
    df = pd.read_excel(
        file_path,
        parse_dates=["birth_date", "hire_date", "last_login"],
        date_format="%Y-%m-%d",
    )

    # Handle mixed date formats within a column
    for col in ["birth_date", "hire_date"]:
        df[col] = pd.to_datetime(df[col], errors="coerce", format="mixed")

    # Drop rows where a required date couldn't be parsed
    df = df.dropna(subset=["birth_date", "hire_date"])

    return df.to_dict("records")

errors="coerce" turns unparseable values into NaT rather than raising — pair it with dropna (to reject) or your error collector (to report). format="mixed" requires pandas 2.0+.

Resuming an interrupted import

A 2-hour import that crashes at row 49,999 shouldn’t start over. Track progress with a checkpoint file:

import csv
import json
from pathlib import Path

def resumable_import(file_path: str, checkpoint_path: str, batch_size: int = 1000):
    """Import CSV with checkpointing for resume capability."""
    checkpoint = {"last_row": 0, "inserted": 0}

    if Path(checkpoint_path).exists():
        with open(checkpoint_path) as f:
            checkpoint = json.load(f)
        print(f"Resuming from row {checkpoint['last_row']}")

    with open(file_path, newline="", encoding="utf-8") as f:
        reader = csv.DictReader(f)
        batch = []

        for row_num, row in enumerate(reader, start=2):
            if row_num <= checkpoint["last_row"]:
                continue  # Skip already processed rows

            batch.append(row)

            if len(batch) >= batch_size:
                # Insert batch to database...
                checkpoint["last_row"] = row_num
                checkpoint["inserted"] += len(batch)

                with open(checkpoint_path, "w") as f:
                    json.dump(checkpoint, f)

                batch.clear()

        # Process remaining rows
        if batch:
            checkpoint["last_row"] = row_num
            checkpoint["inserted"] += len(batch)
            with open(checkpoint_path, "w") as f:
                json.dump(checkpoint, f)

    return checkpoint

The checkpoint only works because rows are processed in file order. For job queues where a worker crashes mid-batch, key the checkpoint on the last committed batch, not on rows seen — the same idea as a processed_offset in a streaming pipeline.

Importing into a Database

Row-by-row INSERT is where most imports die: 50,000 round-trips at ~2ms each is two minutes of nothing else. Batch it.

import sqlite3

BATCH_SIZE = 1000

def batch_insert_users(db_path: str, rows: list[dict]):
    """Insert validated rows in batches inside a transaction."""
    conn = sqlite3.connect(db_path)
    try:
        conn.execute("BEGIN")
        for i in range(0, len(rows), BATCH_SIZE):
            chunk = rows[i:i + BATCH_SIZE]
            conn.executemany(
                "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)",
                chunk,
            )
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

For anything non-trivial, stage first: load raw rows into a temporary users_staging table, validate there, then move clean data with a single INSERT INTO users SELECT ... FROM users_staging. You get a preview of what will land, rollback is trivial (DROP TABLE), and the production table never locks during validation. Duplicates resolve with INSERT ... ON CONFLICT (PostgreSQL), INSERT IGNORE (MySQL), or MERGE (SQL Server) — pick one business key like email and report the skip count back to the user.

What Works

• Validate before inserting. Never trust user-uploaded files; check every cell against expected types and ranges — the same discipline as input validation on an API boundary.
• Report all errors at once, not just the first. Users fixing a CSV want the full list in one shot, not trial-and-error one row at a time. Return a downloadable error report with row numbers and the offending values.
• Wrap database imports in a transaction. When a row fails validation, rolling back the batch beats leaving a half-imported table behind.
• Support more than one encoding. Try UTF-8 first, then fall back to latin-1 or cp1252 for legacy Windows files (see the detection snippet above).
• Give users a template file. A downloadable .xlsx/.csv with the exact headers and format you expect eliminates the most common import failure before it happens.

Common Mistakes

• Inserting row by row. Individual INSERT statements run about 100x slower than batch inserts; use executemany (Python), bulkCreate (Sequelize), or JDBC batch inserts.
• Ignoring encoding issues. A file with Spanish names saved on Windows corrupts or fails if you force UTF-8 — detect it or let the user pick.
• Not handling blank rows. Excel files often carry hundreds of empty rows at the end, so filter rows where all cells are empty (dropna(how="all"), not plain dropna() which kills partially filled rows).
• No rate limiting on uploads. A 500MB Excel file will crash most import processes; enforce max file sizes and push large imports to background jobs.
• Silent data loss. Truncating VARCHAR(255) columns without warning loses data — validate length constraints explicitly and report the truncation.
• df.dropna() without how. The default drops any row containing any NaN, so one blank cell kills a whole record. how="all" is almost always what you mean.

Further Reading

• pandas IO tools — read_csv / read_excel — the full option matrix, including usecols, dtype, and chunked reads.
• Apache Commons CSV — streaming CSV parsing for the JVM.
• SheetJS documentation — xlsx options for parsing and writing spreadsheets in JavaScript.
• RFC 4180 — CSV format — the official format definition; explains the quoting rules your parser must handle.
• Companion repository — the runnable importer scripts plus a sample file to test against.

See Also

• Parse CSV Files — the read-only path when you don’t need validation or persistence.
• Parse Excel Files — lighter Excel reading without the import machinery.
• Input Validation — the validation patterns applied at the row level here, applied to request payloads.