How to Handle Data That Arrives in a Weird Format (October 2026)

Data that arrives in a weird format is data whose structure doesn’t match what your system expects: a character encoding mismatch, an unexpected delimiter, stray header rows, mixed date formats, a column that quietly shifted from number to text, or a missing value written as the literal string None. Nothing useful happens until you diagnose which of those you have.

Most searches for how to handle data that arrives in a weird format land on pages that jump straight to a tool recommendation. The diagnosis has to come first, and it takes about a minute. Preserve the source file untouched, look at the raw bytes, normalize a working copy, validate the result, and only then publish or analyze anything. This guide walks that path step by step, with the newsroom cases I keep running into: FOIA dumps, scraped HTML tables, CMS exports and agency spreadsheet drops that change shape between one week and the next.

What You Need

You don’t need much. You need a way to look at the file as bytes rather than as a preview, a tool that lets you declare the format explicitly instead of guessing it, and somewhere to write down what you changed.

• A raw byte viewer. Command line tools like file, head -c and hexdump -C, or a hex editor, tell you what the file actually is. A spreadsheet preview cannot.
• A tool that accepts explicit settings. Excel’s Power Query editor, pandas.read_csv, csvkit or OpenRefine all let you name the encoding and separator rather than inferring them.
• An immutable copy of the source. A folder that is append-only, plus a checksum of the original file, so you can prove later exactly what you were given.
• A validator. Row counts, duplicate checks, null rates, range tests. Spreadsheet formulas are enough for a single file; dbt tests and Great Expectations earn their keep when the load runs unattended.
• A change log. A plain text file, a spreadsheet tab or a commit message. Every assumption you make about the data belongs in it.

If you only do one thing before starting, make the copy. Every fix applied to the original destroys the evidence you need when someone asks why the numbers changed.

Tool	Reach for it when	Learning curve
Excel Power Query	You’re already working in Excel and the source changes weekly	Low
pandas read_csv	You need repeatable, scripted parsing with explicit encoding and dtype	Medium
csvkit	You want quick diagnostics on a CSV in the terminal, including the dialect	Low
OpenRefine	One-off cleanup of a messy spreadsheet, cluster-and-edit on inconsistent values	Low
dbt tests	The load runs on a schedule and needs pass/fail checks on schema and values	Medium
Great Expectations	Source data quality needs to be documented and checked against a standard	Higher

Step-by-Step

Step 1: Preserve the Source and Record Its Context

Make the source read-only and work only on a copy. Compute a checksum on the original (on Linux or macOS, sha256sum; on Windows, Get-FileHash) and write it down beside the file.

Then record the context, because a file without provenance is not evidence. Note who provided it, the date it arrived, the stated format, the encoding if you can determine it, any known limitations the provider mentioned, and what you intend to use it for. A FOIA production that covers one quarter will often contain a differently ordered set of columns than the previous quarter, and the cover email usually explains why.

How do you know it worked? The original file’s checksum is unchanged at the end of the project, and anyone on the team can trace the final dataset back to a specific file with a specific date.

Step 2: Identify the Actual File Structure

Before loading anything, run the checks in this order. Each one rules out a class of problem and tells you what to declare when you parse.

1. Identify the real file type. The extension lies more often than people expect. A file named data.csv can be an Excel workbook, a tab-separated dump or a PDF export with a text layer.
2. Check the first bytes for a BOM and inspect encoding. A UTF-8 byte order mark is the bytes EF BB BF at the start of the file. Accented names rendering as Café is the classic mojibake signature of UTF-8 bytes read as Latin-1.
3. Count delimiters per line. Take the first 20 lines and count commas, semicolons, tabs and pipes in each. The separator used consistently in nearly every line is your separator; one appearing in exactly one line is data, not structure.
4. Look at the first ten raw lines, not the spreadsheet preview. Preamble rows from export tools, merged title cells and a second header row mid-file all hide inside the preview.
5. Check that every row has the same number of fields. Rows that are short or long usually mean an unescaped delimiter inside a free-text field.
6. Find where the type breaks. Scan one column down and note the first row that looks different. Type drift is a local problem, not a column-wide one.

The table below maps what you see to what usually caused it, so you can skip straight to the relevant check.

Symptom	Likely cause	First check
Garbled accents or odd symbols	Encoding mismatch, often UTF-8 read as Latin-1	Inspect the first bytes for a BOM, then file -bi name.csv
Whole file lands in one column	Separator is a semicolon or tab, not a comma	Count delimiters across the first 20 lines
Column names appear as row one	Header row not consumed, or a preamble above the header	Read the first ten lines as raw text
One column refuses to sort or sum	Type drift: numbers stored or imported as text	Look for stray apostrophes, spaces or invisible characters
IDs lose leading zeros or become scientific notation	Coerced to a number by the importer	Compare ID length before and after import
Dates shifted by days or parsed as text	Ambiguous day/month order, Excel serial numbers, missing timezone	Print the raw string for any date that looks wrong
Row counts change between runs	Newlines inside quoted fields, or inconsistent line endings	Count records with a real parser, not a text editor
Loads cleanly but totals are wrong	Silent coercion, such as numbers imported as text and skipped by SUM	Compare SUM against a typed copy of the same column

How do you know this step worked? Every field lands in its own column, the column count matches the source’s header, and you can name the encoding and separator out loud. If you can’t, don’t move on.

Step 3: Make a Safe, Normalized Working Copy

Parse the copy with every setting stated explicitly. In pandas that looks like pd.read_csv("raw.csv", encoding="utf-8-sig", sep=";", dtype=str, skiprows=3). Reading everything as text first is deliberate: it stops the importer from guessing, and you cast types yourself once the shape is known.

In Excel, use Data then From Text rather than double-clicking the file. The Power Query import dialog lets you pick the file origin, set the delimiter, choose the header row and mark column types before anything loads. Save the result as a new sheet or a separate workbook, never as the source file.

Convert line endings to LF if the file mixes CRLF and LF, strip any stray preamble rows, and handle nested fields explicitly: embedded JSON in a CSV column gets parsed into separate fields or stored as a string you never index, never left half-parsed.

How do you know it worked? Reopen the normalized copy in a plain text editor and confirm the row count matches the source, then confirm the first data row sits directly under the header.

Step 4: Clean and Standardize the Fields

Work field by field, and keep the original value beside your corrected one for anything you alter.

Field names. Lowercase, replace spaces with underscores, strip punctuation, and disambiguate duplicates. Varyings like Value, VALUE and value (2) usually mean the header row was repeated mid-file, so remove the stray row rather than renaming columns around it.

Whitespace and invisible characters. Trim leading and trailing spaces, and normalize non-breaking spaces. Apply Unicode normalization so that a name typed with a combining accent matches the same name typed with a precomposed one.

Dates. Pick one target format and state it: ISO 8601, YYYY-MM-DD, is the safest for storage because it sorts correctly and has no ambiguity. Convert Excel serial numbers, where 1 is January 1900, by parsing explicitly instead of letting a spreadsheet guess. Decide whether a bare timestamp is local time or UTC and convert once, at the boundary.

Numbers. Strip currency symbols and thousands separators before parsing, and decide the decimal convention deliberately. A European export of 1.234,56 parses as 1234.56 with the wrong settings and as 1.234 with the right ones.

Missing values. Every spelling of “nothing here” means something different, and conflating them destroys information.

How it’s written	Likely meaning	Treat as
Empty cell	Not supplied, or suppressed upstream	Null, but flag the rate if it exceeds normal
Empty string “”	Known empty	Null, distinct from a literal “None”
Literal None / null / NULL	A string leaked out of an API or script	Null, and log the count as a source defect
N/A or –	Not applicable versus not available	Two separate codes, not one blank
9999 or 1900-01-01	Sentinel for missing	Convert to null only with a written rule
A space	Accidental	Trim, then re-check

Identifiers. Store them as strings from the start. An ID like 0012345 that gets coerced to a number becomes 12345 with no error and no warning, and a 16-digit ID can round-trip into scientific notation. Load them as text, keep them as text, and only cast when you have a reason to.

The raw and cleaned rows below are the same records, read the second way:

Raw line as delivered	After normalization
Bureau name;value;as_of	bureau_name, value, as_of
Northside Bureau;1.234,56;03/04/2026	northside_bureau, 1234.56, 2026-04-03
Eastside Bureau;987;03/04/2026	eastside_bureau, 987, 2026-04-03
Harbor Bureau;1120;None	harbor_bureau, 1120, null

The third row is the one that matters. Read as a number, 1.234,56 looks like a date in some locales and a truncated figure in others; the provider’s own documentation settles it. If nothing settles it, the honest move is to leave the value unparsed and ask.

Step 5: Validate the Data Before Analysis

Validation is what separates a dataset you can publish from one that merely loaded. Run these checks and write down the result of each, including the ones that passed.

• Row count against what the provider says they sent. A 40 percent drop is a parse failure until proven otherwise.
• Required columns present. Missing one is a format change, not an empty field.
• No duplicate keys where an ID should be unique.
• Null rate per column, compared with the previous run of the same feed.
• Range and type checks: counts are non-negative, percentages fall between 0 and 100, dates fall inside the requested period.
• Reconciliation. Recompute any headline figure by a different route, for example by summing the components as well as using the published total. A mismatch means a parse error, not a rounding note.

When a row fails, don’t let it stop everything. Write the bad rows to a quarantine table with the raw line, the reason it failed and the run timestamp, then load the rest. This is the pattern practitioners converge on in r/dataengineering: one thread there describes a job that had already imported thousands of rows when a tracking field came through as the literal string None and a .lower() call blew up on it. Quarantining that single row would have cost nothing. Raising on it cost the whole import. The same forum recommends open-source expectation packages, dbt tests and Great Expectations, to catch this class of problem automatically rather than by luck.

The trade-off is real, so decide it deliberately. Fail the whole load when a bad value would change a published number and you can’t bound the error. Quarantine when the failure is isolated, the reason is recorded, and someone reviews the quarantine before publication. Silently dropping rows is the only option that’s never right.

Step 6: Document the Transformation

A transformation nobody can reproduce is a claim, not a dataset. Keep a short audit trail that answers: what did the source look like, what did you assume, what did you change, what did you exclude, what is still unresolved, and what checks did you run.

Most pipelines need less than this: a raw-to-cleaned file map, a list of assumptions such as “dates are day-first per the provider’s data dictionary”, a count of quarantined rows with reasons, and the validation results. If you use Power Query, the query itself is the record, so store the .pbix rather than a screenshot of the steps.

How do you know it worked? A colleague who didn’t touch the project can rebuild the clean file from the raw file and the documentation alone.

Step 7: Publish or Load the Cleaned Data

Export to UTF-8, keep one delimiter throughout, write dates in ISO 8601 and leave the file with a header row. If the data loads into a newsroom database or a reporting tool, run a schema check on load so the next format change fails loudly at the boundary instead of quietly downstream.

Before anything is published, check for personal data that arrived unbidden. Court records, incident reports and agency spreadsheets often contain names, addresses or case numbers that were never meant to be a dataset, so review, redact or aggregate. If small counts would let someone be identified from a neighborhood or a ward, suppress or combine them.

Ship a plain data dictionary alongside the file: one line per field, its type, whether null is allowed, and its source. Readers of a published dataset, and the next person on your team, will need it within a year.

Common Mistakes When Data Arrives in a Weird Format

These are the failures that cost the most time, and most of them are cheap to avoid.

1. Trusting the extension. Check the bytes. A .csv that is really a semicolon export or an XLSX file wastes an afternoon.
2. Repairing errors silently. If a transform drops a row without recording it, the dataset is untrustworthy. Log every exclusion.
3. Guessing missing values. Filling a blank with zero or the column average invents a fact. Leave it null, or use a documented imputation, and say which.
4. Overwriting cleaned values. Keep source and corrected columns side by side. Overwriting is why nobody can explain a chart six months later.
5. Skipping validation because the import succeeded. Silent coercion is the dangerous class: text-typed numbers make SUM return partial totals with no error at all.
6. Hand-fixing headers. If a preamble row has to be removed every week, write the skip into the parse step rather than deleting it by hand each time.
7. Treating Excel as the pipeline. Spreadsheets are fine for exploration and bad for repeatability. Move to Power Query or a script once the file arrives more than once.
8. Never asking the source to change. If a feed has shipped the same defect for six months, another defensive cast is a temporary patch. Sometimes the right fix is a written request to the provider, with an example of what breaks, and a deadline. Write that request once the file stops being one-off.

One more habit pays off: compare each new file’s column signature, types and row count against the last one. When a column disappears or a type flips, you find out at delivery rather than at publication.

Frequently Asked Questions

Conclusion

The safest first moves take five minutes: keep the original file read-only with a checksum beside it, look at the raw bytes instead of the spreadsheet preview, and write down what the file actually is before you parse it. Then normalize a copy with explicit encoding, separator and column types, validate row counts, types and totals, and quarantine the rows that fail instead of letting them stop the load.

Knowing how to handle data that arrives in a weird format is mostly discipline about what you do before you click import. Diagnose first, guess never, and document every assumption. If the same defect keeps coming back, the fix that lasts is a request to whoever sends the file.