Load the two files you want to join
Everything runs inside this page. Your files are never uploaded, and the tool works with no internet connection. CSV, TSV and Excel files are accepted.
Left file: the records you want to keep
Right files: the records you want to pull data from
Or pick up where you left off
A progress file holds both input files, every candidate the tool worked out, and every decision you have made. Open it on any computer to continue. Nothing is stored in the browser, so this file is your only copy.
About RecordMatcher
Merges data from two files by matching on identifiers such as names or IDs, including where the two files write the same identifier differently. Every match the scoring is unsure about is held back for your review. Runs entirely in your browser, and your files are never uploaded.
Choose what to match on
Pick the columns to compare. One column works; two is usually better, because the second field confirms the first. If you also have a field that must agree exactly, such as county, district, state or year, set it as the grouping column instead. Records are then compared only against others in the same group, which rules out whole categories of false match.
Left file
Right files
Each file is mapped separately, so the column names do not have to agree between them. Match each of your left-file columns to its counterpart here, in the same order.
Columns to bring over from the right file
These are added to every matched row on export.
Decide how much you want to check
Every record gets a proposed match. You decide how many of them you review before they are accepted. Nothing here is destructive, so you can re-run the match as often as you like.
What should I check myself?
About your data
Clean up text before comparing
senior high = high school.Advanced settings, needed only if the results look wrong
How similarity is measured
Four measures, each strong where the others are weak. The score is their weighted average. Adjust these if a whole category of name is scoring badly.
Thresholds
Words that decide a match on their own
A pair whose words disagree in one of these ways is rejected, however similar the rest of the text is. Your choice of rule set determines these, and you can change any of them here. Which ones apply depends on your data: in a list of people, West and Senior are parts of a name rather than distinctions.
Export
Every row carries the score it was given, how it was decided, and whether a person confirmed it. Keep those columns, as they are what makes the result auditable later.
What didn’t resolve, broken down
Pick a column to see where the unresolved rows sit. Sorted worst first, so a gap concentrated in one county, one source file or one school type is immediately visible. Matched, Entered by you and Not matched are mutually exclusive and add up to Rows. Still to review overlaps them: a row waiting in the queue already has its highest-scoring candidate recorded as its match, so most are counted under Matched.
Right-file rows that were never matched
Expect this to be large when the right file covers more ground than the left. A statewide directory matched against one year of returns leaves most of itself unmatched, which is normal. Check it when one value is far worse than the rest, which points to a naming or coverage problem rather than genuine absence.
What to include
Clean up
Turn the export into the file you want to work from. Merge columns that hold the same value, split a column that holds several, rename columns, drop the ones you do not need and set the order of the rest. Every change is written out as an R script, so the cleanup is documented and repeatable: run the script on a future export and you get the same result.
Split a column into parts
Optional. Splits one column into several. A cell
holding 12 Main St, Frostburg, MD 21532 becomes street, city, state and ZIP in
four columns, each usable on its own below.
Build a column from others
Use this when one piece of information is spread across several columns: the same field arriving from each file under a different name, or an address held in a single cell in one file and split into street, city and ZIP in the other. List the places the value can come from, in priority order. Each row takes the first one that has a value.
| match_LocationCity | match_City | → city |
|---|---|---|
| Cumberland | blank | Cumberland |
| blank | Frostburg | Frostburg |
| blank | blank | blank |
Columns to keep, in order
Untick a column to drop it. Drag the handle, or use the arrows, to reorder. Type in the name box to rename a column.
Preview
The R to reproduce this
Reads the exported file, applies the settings above and writes the cleaned file. Keep it with the data so the cleanup is documented and repeatable without this tool.