Is This Data Record Rot or Not? Swipe Left? Swipe Right?
A CSV file can look pretty fugly without being hopeless. A naughty record might have less values than its respectable header row. A Bad Man might have stuck something in it you don’t want. A supplier might have added an extra comma, or maybe did something really weird to a colon. Rejecting every imperfect file can interrupt useful work, but accepting everything can be worse: it can silently alter, fester, blister or damage your data. If your first ‘Data Fix’ thought is “I’ll just raw-dog into this cheap Tijuana Spreadsheet”. Yeah. Wow… Spreadsheets. We need to get your mind out of the gutter.
Only the fortunate few live in a world where they get to ask: “Does this file perfectly follow every CSV convention and flawlessly conform to our Data Specification?”. Yeah right. When you get data (especially from external sources), its often in pretty bad shape. Even when they send it ‘really clean’ – its often not the structure you precisely need. There will be “Data Wrangling” – we know this. Here’s the ‘Alpha and the Omega’ of Data Wrangling: “Can we BANG on this INPUT DATA over a LOGIC ANVIL to anneal it to Canonical Form without clobbering, destroying, or fictionalizing it?”
Anything worth keeping must be kept. Anything salvageable must be saved. Do NOT just throw stuff away – somehow find a way to STOP The Machine before it decides To Hell With The Rest. Data Wrangling is the work of surgeons, not butchers.
First, remember what the parser must know
A header-based CSV file uses its first row to assign meaning to later field values:
CustomerID,City,Status
C104,Denver,Active
C105,Boston,Pending
The second field in each record means City because of its position beneath the header. When the number of values and headers disagree, the parser must decide whether any safe association still exists.
Case 1: A short record may be recoverable
Consider:
CustomerID,City,Status
C104,Denver,Active
C105,Boston
The second data row has no third value. If the input contract permits missing trailing fields, there is an obvious interpretation: CustomerID=C105, City=Boston, Status is empty. No existing value needs to move or disappear.
That is an example of a potentially safe normalization. The empty value should remain distinguishable from a fabricated Active, Unknown or other default. Whether an empty status is acceptable for the business operation is a separate validation question.
A strict import tool might still reject the file. A transformation workflow designed for imperfect input can reasonably accept it when the interpretation is deterministic and preserves the provided data.
Case 2: An extra non-empty field creates a harder problem
Now consider:
CustomerID,City
C104,Denver,West
What is West? A region, an accidental extra value, or something belonging in a new column? The file contains meaningful content without a corresponding header identity.
Silently ignoring West produces a cleaner-looking file at the cost of losing source information. Inventing a Region column might appear helpful, but it amounts to making a business decision the source file did not authorize. Unless a documented source-specific contract resolves the mismatch, refusing this record—or the file—is safer.
A single extra empty trailing field is a different case. It may be harmless under a carefully defined parser policy, but that behavior should be tested rather than assumed to be safe in every tool.
Case 3: Duplicate headers threaten data identity
CustomerID,Amount,Amount
C104,35.00,42.00
Yeah – don’t ever do this. There are two physical Amount columns, each with a different value. If a parser stores records in a dictionary keyed only by header name, one value may overwrite the other. The output could look valid while information has already been lost. Now, if you’re not familiar with some of this jargon (for example – KEY uniqueness in a dictionary), then disregard the techie-talk – and just know “Don’t repeat identical column names”. And that goes for the WHOLE Headers Row. Not just ‘side by side’. No Dupes, period.
Even visually different headers may become duplicates after intentional normalization: AMOUNT and Amount, for example. If a workflow does CASE Normalization (ie: makes ALL column names lowercase) then you can end up with amount and amount – You got DUPES again. Make sure you are aware of any NORMALIZATION your Header Row might process through – and confirm uniqueness after column header normalization.
This is a good reason to distinguish permissiveness from wishful thinking. A robust import process should refuse ambiguous column identity rather than pretend the duplicate was harmless. And this is where spreadsheets can be DANGEROUS. Spreadsheets tend to be more permissive than CSV and Databases. It may be the case your data-feed originated from Spreadsheets. But once you realize that CSV is the BEST structure for data-cleaning and transformation, you need to adapt your thinking ‘out of spreadsheets’ and into the downstream processing destinations like CSV Transformers and databases.
Case 4: Empty lines and empty values are not the same
A physically blank line between records need not be a meaningful data record. Many workflows can skip it harmlessly.
But a row containing delimiters is different:
CustomerID,City,Status
,,
You see what that is? Tricky that ,, (two commas) IS NOT AN EMPTY LINE. You gotta know what to do with this. That second line explicitly describes a record with empty field values. It may fail business validation, but silently dropping it is not the same as skipping a genuinely blank physical line. Your policy needs to distinguish the two.
Likewise, a space inside a quoted value or leading whitespace in an unquoted field can be intentional information. Trimming every value during input parsing may destroy evidence needed by a later, explicitly chosen cleanup rule. In case you are starting to wonder “This is NUTS! How many RULES do I gotta remember!?” Well, I won’t say I can paint a simple pretty picture, but you NEED to author a Data Dictionary that defines EVERY allowed value and rule for each field in the file. Hard truth is: You WILL have to think about EVERY SINGLE tiny detail about it – but at least with a Data Dictionary, you don’t have to memorize it.
Case 5: Quoting errors require special care
A value containing a comma usually needs quoting:
CustomerID,Address
C104,"17 Main Street, Unit 3"
Without valid quoting, the parser may observe three values where two are expected. If the file has broken quotation marks or embedded newlines, there may be multiple plausible interpretations of where a record ends. This is not a good place for silent guesswork.
Let’s define some things so we can talk about this “commas inside data values” – and how to escape those with quotes. This deserves special attention because THIS IS THE WORST GENUS of problem.
- Major Delimiter : A CSV File uses two basic “Structural Components” – Linefeeds and Commas. The Linefeeds are how the RECORDS are defined. Each new LINE in a CSV File following the HEADER ROW is “linefeed delimited”. Here, the linefeed is the MAJOR DELIMITER. You can also think about it as an ‘outer’ delimiter.
- Minor Delimiter : As introduced above, the COMMA is the other structural thing and that is the MINOR DELIMITER. Within a RECORD, the Commas are used to delimit the distinct FIELDS in the RECORD.
- Delimiter Escaping : If you think for a moment about CSV’s use of LINEFEEDS and COMMAS as MAJOR and MINOR Delimiters, respectively – you become quickly aware that “Wait a minute, this makes it problematic for me to have either of these DELIMITERS within any single FIELD VALUE in a record”. Yup, that’s right. We are going to only talk about the COMMA (the Minor Delimiter) since thats what this whole Case 5 is about. If you have a FIELD VALUE that itself contains a COMMA – then how do we prevent that NESTED COMMA from ‘acting like’ a MINOR DELIMITER? You gotta wrap the whole field in QUOTES. That is ‘Delimiter Escaping’ in this usage. By doing this quoting, the CSV Parser is explicitly informed that the NESTED Comma in the field is just ‘field data’ and NOT to be treated like a MINOR DELIMITER.
Test a malformed-file policy against real examples—including quotes, line endings, Unicode, blank records and extra data—not just a single demonstration row.
A practical decision checklist
Before repairing a malformed CSV, ask:
- Can every non-empty source value still be accounted for?
- Does every physical column have one unique, known identity?
- Are missing values represented honestly rather than replaced with guessed facts?
- Can the same rule be applied consistently to every matching record?
- Is there any alternative interpretation that would materially change the meaning?
- Can the result be checked against counts, identifiers and retained exception evidence?
If the answer to the first two is no, or the fifth is yes, the case probably requires controlled refusal or human clarification.
Where CSVTrax fits
CSVTRAX approaches imperfect CSV/TSV input with a useful design principle: FIRST be as ‘Permissive as reasonably possible with the INPUT’ – and THEN seek a deterministic interpretation without silently discarding meaningful data or inventing structural identity. When evaluating any tool, ask which malformed patterns it actually accepts, which it rejects, and how it communicates the difference.
There are some CSV tools “out there” that have the self-defeating characteristic they are NOT PERMISSIVE ENOUGH to load ‘trashy but salvageable’ CSV. Watch out for those others – or you could confront the frustrating scenario where the TOOL that is supposed to fix your less-than-perfect CSV will NOT ACCEPT your CSV for repairing it… BECAUSE its ‘Less Than Perfect’. WTF!? Yeah – don’t bother with those.
The practical takeaway
“Malformed” is a description, not a verdict. Some irregularities can be normalized transparently. Others destroy the relationship between columns and values. The best CSV recovery policy is as accepting as deterministic, non-destructive interpretation permits—and no more accepting than that.
The discussion of this article is intentionally INTRODUCTORY. The gamut of considerations and techniques for CSV Transformation and “Data Wrangling”, generally – is vast. We barely skipped a small stone across its surface here. Other articles throughout the CSVTRAX Technical BLOG Series will explore some topics in depth.
