Check column types before the table becomes a spreadsheet
Use Capture Score and type mismatch warnings in Chrome so dates, currency, and emails do not silently break the first pivot you build.
Check column types before the table becomes a spreadsheet
I have a folder of CSVs named things like prices_final2.csv. Each one looked fine in the first five rows. Each one had a landmine further down: a date written as TBC, a price written as from £8, an email with a trailing comma. Excel did not complain. The pivot did.
The cheap lesson is “always profile your data”. The useful lesson is to profile it before the file exists. Web tables already know, in a fuzzy way, what a column is trying to be. A capture tool that detects types and shouts about mismatches saves the later archaeology.
What goes wrong in the first twenty minutes
Currency columns pick up a code or a symbol in some cells and not others. Excel then stores the column as text. SUM returns 0. You format the column. Nothing changes because the values are still text.
Date columns mix 30/08/2026, 2026-08-30, and Aug 30. Sort “works” in the sense that it does something. It does not order time.
Email columns include a mailto link in one row and plain text in the next. A mail merge skips half the list.
Number columns contain 1,234 from a UK page and 1.234 from a page that used a different locale. You do not notice until a total is off by a factor of a thousand.
None of this is exotic. It is Tuesday.
Capture Score as an early warning
I want a score that is boring and structural. Did the extractor find a header? Do columns have consistent lengths? Do declared types have rebels?
I do not want a score that congratulates me for capturing a table. I can see that I captured a table.
Table Capture Chrome exposes Capture Score and type detection inside Table Studio. Revenue marked as currency. Customer email marked as email. Ship date marked as date, with a count of values to check. That last bit is the product.
I jump to the mismatched cells, fix or drop them, then export. The spreadsheet starts cleaner because the argument already happened in the browser.
A mismatch table I keep seeing
| Column the page implied | What arrived | What I do |
|---|---|---|
| Monthly fee | £29 and Contact us | Split into a numeric column plus a notes column, or drop the contact row |
| Start date | 2026-08-30 and ASAP | Move ASAP to notes. Do not leave it in a date field |
| Units | 12 and 12 / pallet | Parse the number, keep the unit in a second column |
ada@example.com, | Trim punctuation | |
| Status | blank and Active | Decide if blank means inactive or unknown |
I used to do this in Excel with filters. Doing it while I can still see the original page is faster. The page often explains the weird cell in a tooltip I would have forgotten.
How I walk a capture
I open the detected table and ignore the score number for a second. I look at the type row.
If a column I think is money is typed as text, I search for the first letter. That letter is usually £, from, or Call.
If a date column has one value to check, I go there first. One bad date is enough to break a timeline chart.
If an email column is clean, I still scan for n/a@ or role addresses I do not want in a mailout.
Then I delete rows that are not records. “1 value to check” is sometimes a footer that says Updated daily.
Locale is a type problem in disguise
I work in British English and I still get caught. A page from a US vendor uses 08/30/2026. A UK government table uses 30/08/2026. A JSON-ish widget uses ISO. After a paste, Excel guesses based on the workbook locale, not the page.
If I am mixing sources, I convert to ISO in Table Studio (2026-08-30) before export. It is ugly in a slide. It is correct in a file.
Currency has the same split. I either keep the symbol in a separate currency column or I standardise to a number and put the code in the filename. Mixing £12 and 12 in one column is how totals go quiet.
Exports that preserve the cleanup
XLSX will happily re-guess types when someone else opens the file. I still prefer it for humans, but I glance at the first numeric column after they send it back.
CSV is honest to the point of cruelty. Everything is text until the next program decides. I keep headers stable and I do not put commas inside unquoted fields. A decent exporter quotes them. I still peek at the file in a text editor once.
JSON makes types more explicit, which is why I use it when the next consumer is code.
When the warning is the data
Sometimes the mismatch is the point. A column of prices with three POA cells is a finding, not a defect. I keep those rows and add a price_status column: numeric or on_request.
The mistake is letting POA sit in the same field you will average.
A five-minute rule
If I cannot get types stable in five minutes, the page is the wrong source. I look for a CSV download, an API, or a different view that is already a real <table>.
Type checks are not a personality. They are a way to stop naming files final. The browser is a good place to have that argument, because the source is still on screen and the bad cell still has a context.
Empty is a type
Blank cells look innocent. In a quantity column, blank might mean zero, or unknown, or “see notes”. Those three meanings produce three different sums.
I do not fill blanks with 0 unless I can defend it. I add a qty_known column with yes or no when the page is sloppy. That looks pedantic in a 15-row file. It looks wise in a 400-row file.
Boolean columns have the same fog: Y, Yes, true, 1, and a tick character. I normalise them in the editor to true and false if code will consume the file. I leave Yes/No if a person will read it. I do not mix.
Units hiding inside numbers
12kg, 12 kg, and 12 in one column will fail a numeric type check, or they will pass if the extractor stripped the letters on some rows only. I look at the raw cell, not only the type badge.
If I need maths, I split. If I need a label for a slide, I leave the unit in the cell and I do not average it.
A type pass I now do on every CSV I inherit
Even when I did not capture the file, I open it and ask the same questions. Which columns should be dates? Which should stay text because of leading zeros? Which contain a word that poisons a sum?
The capture tool taught me the questions. I now ask them everywhere. That is the useful leftover, more than any single score number.