Elysia Tools
Navigation
Workflow Playbook
Diagnose why a CSV import keeps failing, map messy headers onto the target schema, remove duplicates under a stated policy, and emit typed INSERT statements or a bulk-load TSV.
Hubs
A failed import rarely explains itself. The loader reports a syntax error near a row number, or worse, loads half the file and stops, and the operator is left guessing among encodings, delimiters, and quoting styles. The first move is therefore not repair but diagnosis: run the structural validation and get a defect list with row numbers — malformed rows, unbalanced quotes, columns that do not match the header count. A file that validates cleanly but still will not load has a header problem or a duplicate problem, which is exactly what the next two steps address. A file that does not validate cleanly gets its ragged rows fixed or consciously skipped first, because every later tool trusts the row structure it is handed.
The target table is the contract; the file's first row is the negotiation. External exports arrive with Customer ID in January, userid in February, and CUSTID whenever a different team runs the report. Header resolution maps each of those onto the real column names of the target schema, combining layered automatic matching with an explicit alias dictionary for the names you already know are unreliable. The deliverable of this step is a mapping where every schema column is fed by exactly one source header — nothing unmapped, nothing claimed twice. An unmapped column means silent NULLs after loading; a double-mapped one means one field quietly overwriting another. Both are cheaper to catch on the mapping screen than in a query three weeks later.
Duplicate rows in an import file are usually not accidents — they are re-exports, double form submissions, or two people typing the same customer. That is why deduplication belongs to a stated policy rather than a reflex. Exact matching is the honest default: it removes rows that appear verbatim twice and never touches rows that merely look similar. Fuzzy matching earns its place when the file was hand-entered and spacing or case separates true twins — and it must be written down, because "we removed 340 rows" is only defensible when the criterion is on record. The row arithmetic closes at this step: source rows, minus duplicates, minus skipped malformed rows, equals what the next step will emit.
The final artifact follows the load path. Batched INSERT statements suit the reviewed, moderate-sized load: they carry type inference, target MySQL, PostgreSQL, SQLite, or SQL Server, and state a conflict strategy — ON CONFLICT or IGNORE — so reruns do not double-insert. They can be read in code review, kept in version control, and replayed. The TSV exit suits the native bulk loader: COPY and LOAD DATA paths eat a clean tab-separated file far faster than any statement stream, and the dialect conversion handles quoted fields and embedded delimiters so the TSV stays parse-safe. One warning applies — the bulk loader will not resolve conflicts for you, so a file that may contain re-runs belongs on the INSERT path where the conflict strategy lives.
This page repairs one external CSV that a database refuses and walks it to a first successful load. When the job grows beyond that, hand it to the neighbors. Sources arriving as workbooks, or a handoff that must run unattended every week, belong to the ETL ingestion workflow. Suspicious values, outliers, and quality evidence for an argument belong to the data quality investigation. Filtering, grouping, and reshaping a table that already loads fine belong to the CSV utility workflow, and designing or migrating the schema itself belongs to the database schema migration pages. Repair first — then graduate the routine to a pipeline.
Workflow playbook
Run the structural validation to get a named defect list — malformed rows, unbalanced quotes, ragged column counts — instead of the database's silent refusal.
Resolve inconsistent column names such as Customer ID, user_id, and CUST_ID onto the real table columns, layering automatic matching with an explicit alias dictionary for the names you already distrust.
Choose exact matching for re-exported rows that appear verbatim twice and fuzzy matching for hand-entered near-duplicates, and write the choice down — the policy is part of the delivery.
Generate batched, type-inferred INSERT statements with a conflict strategy for MySQL, PostgreSQL, SQLite, or SQL Server, or hand a quote-safe TSV to a native COPY or LOAD DATA bulk loader.
Exact matching removes true re-exports where the same row appears verbatim twice; fuzzy matching also catches hand-entered near-duplicates that differ by spacing or case, at the risk of merging two genuinely distinct rows.
Batched INSERTs are reviewable, portable across MySQL, PostgreSQL, SQLite, and SQL Server, and carry ON CONFLICT or IGNORE handling; a TSV handed to a native COPY or LOAD DATA path is far faster at millions of rows but delegates conflict handling to the loader.
An explicit alias dictionary makes weekly imports deterministic and auditable; the layered automatic resolution suits one-off files whose headers you are seeing for the first time.