CSV Splitter

The split can be right and the files still be wrong

Take a column of regions with North/South in it. A file name cannot contain a slash on any operating system, so every tool replaces it with something else, usually a hyphen. If another row says North-South, the two now have the same file name. One of them is written over the other, or the rows are appended together, depending on which tool you used. Either way you end up with one file where you expected two, and the row counts you can see still look perfectly correct.

And a slash is only the most obvious of five.

USA and usa are two values to a spreadsheet and one file to Windows and macOS, because neither tells file names apart by case. Café can be written with one character for the é or with an e followed by a separate accent mark, which look identical on screen and are different strings to a computer, until they reach a file system that joins them. A value ending in a space loses that space the moment it becomes a file name, because Windows strips it. And a value longer than a file name can be gets cut, so two long values that differ only at the end become one.

Every one of those is in the example on this page. A plain split turns fourteen rows into ten files and loses four groups into other groups. This one makes twelve files, and tells you about all four.

What happens instead. The first value keeps its name. The second gets a number added. Nothing is ever written over anything, and the page lists which values collided and which of the five reasons it was.

Every row counted out again

The rule this tool is built on is short enough to check: every row that goes in comes out in exactly one file, and the row counts of all the files add up to the number of rows you started with. Not most rows. Not the ones with values in them. Every row.

That is harder than it sounds, because the usual failures do not break the count. Two groups merging into one file keeps the total the same. Rows with an empty value being dropped is visible only if you were watching the total. So the count is done twice here, by two things that share no code: once by the engine from its own grouping, and once by reading the files that were actually written and counting the rows in them. Both numbers are on the page before you download anything.

When are two values the same thing?

This is the question underneath the whole job, and most tools answer it for you without saying so. Whether UK is the same as uk, whether "North " with a space at the end is the same as "North", whether U.K. is the same as UK: each of those is a real decision with a real consequence. A tool that quietly folds them together gives you one file where you wanted three. A tool that keeps them apart gives you three where you wanted one. Neither is wrong in general and both are wrong sometimes.

So it is a setting, and the default is the safest one: exactly, character for character. Three looser settings are there when you want them, from ignoring spaces at the ends up to ignoring case, spacing, punctuation and accents all at once. Whichever you choose, anything that gets joined together is listed on the page with the values that were joined, so you can see the effect of the setting rather than guess at it.

The files go out in the shape they came in

A file exported from a German or French system is usually semicolon separated, because the comma is the decimal point there. Open a comma separated file in that Excel and every row arrives as a single long cell. It is one of the most common ways a perfectly correct file looks broken to the person who receives it.

So the output here keeps what the input had: the same separator, the same encoding, and the byte order mark if there was one. A tab separated file comes back tab separated. A UTF-16 file comes back UTF-16. You can turn that off and get plain comma separated UTF-8 instead, but it is a choice you make rather than one made for you.

Seven ways to decide what belongs together

Splitting by the value in a column is the common one, and it is not always the useful one. A column of order dates has as many values as rows, so splitting by the value gives you one file per order. Splitting by the month in it gives you twelve.

So the value itself is one of seven. There is the year, the month and the day inside a date, read from whatever shape the date is written in. There is the first letter, for splitting a list of names into an A file and a B file. There is the first few characters, for codes with a meaningful prefix. There is a number range, for putting every total between zero and ninety nine in one file. And there are the two that have nothing to do with the values at all: every so many rows, and into a number of equal parts.

Two columns at once works as well, which gives one file per combination: one per region and status rather than one per region.

What has actually been tested

Every figure here is a test run that has to pass before anything reaches this page. If one fails, nothing ships. A test reads this page, pulls out every figure in it, and compares each one with the code behind it.

• 53Grouping, every rule, every matching setting, and the rule that every row comes out exactly once groups
• 47File names, and every way two different values quietly become the same file filenames
• 40Every file that ships, scanned for generated copy, inline styles, off-origin loads and broken asset paths ship
• 29Whole files written and read back, counted again from what was really produced files
• 28Every rule against every setting, and the sizes pushed until the machine is the limit limits
• 26Layout at seven screen sizes, with the browser text enlarged, and the contrast of every line visual
• 16The live page in a real browser, under the security policy it ships with e2e
• 14Every control clicked in a real browser, to prove none of them is decoration ui
• 12Every number printed on this page, checked against the code behind it figures
• 10Every icon and image is the real brand asset, and the link preview belongs to this tool brand
• 8The writing itself: sentence rhythm, repeated openers, and the templates that give generated prose away humanise

Properties you can hold it to

• It never writes one group over anotherWhere two values make the same file name, the second gets a number and both survive. The page says which values and which of the five reasons.
• It counts, and shows the countRows in and rows out are on the page before anything is downloaded, worked out twice by two separate things.
• It keeps the rows nobody asked aboutRows with nothing in the column go into a file of their own rather than being dropped or joined to whichever group came first.
• It asks before it mergesWhether two values are the same thing is a setting, not an assumption, and whatever it joins together is listed.
• It hands back what it was givenThe separator, the encoding and the byte order mark survive, so the files open the way the original did.
• It warns before you commitThe number of files appears before the download, and a split that would make hundreds of single-row files says so.
• It holds nothingNo account, no history, no storage. Close the tab and the file you dropped in is gone from it.
• It works offlineEverything happens in this tab. Pull the connection out after the page has loaded and it carries on, which a test does on every run.

Questions people ask first