Convert TSV to CSV

Plenty of systems export tab-separated files and almost none accept them. Analytics consoles, ad platforms, laboratory instruments and database clients all reach for the tab because their data is full of commas; the CRM, the mailing tool and the accounting package on the other end have a field labelled "Upload CSV" and mean it literally.

Renaming the file is worth thirty seconds because a minority of importers sniff the delimiter instead of trusting the extension, and Excel is one of them. The majority do not. They split each line on commas, find a single column where they expected twelve, and either reject the file with an unhelpful message or import every row into the first field, which is worse because it looks like it worked.

The conversion is not a search and replace of tabs with commas, and it must not be. A field reading "Berlin, Germany" already contains the character that is about to become the delimiter, so it is wrapped in double quotes on the way out, and a double quote inside such a field is doubled.

This is correct and universal, and it still alarms people opening the result in a text editor for the first time. Only the fields that need it are quoted; the rest are written bare, so a file where a handful of address or description columns are quoted and everything else is not is exactly what a well-formed CSV looks like. Any importer worth uploading to reads that convention. One that does not is a signal to look for a different import route rather than to strip the quotes.

The one weakness of a tab-separated file is that a tab occasionally ends up inside a value — pasted in, typed by accident, carried through from another system — and the format has no natural way to express it. Downstream tools that count tabs then see one extra column from that row onward.

Converting to CSV removes the problem entirely, because the tab is no longer a delimiter. It is written straight into the field with no quoting needed and no ambiguity created. If a tab-separated export has been producing misaligned rows in one tool, this conversion is a legitimate fix rather than a workaround, and it is worth knowing that the corruption was in the delimiter choice and not in the data.

Values that look numeric are read as numbers on the way through, which is right for quantities and prices and wrong for codes. A member number of 00123 arrives in the CSV as 123, a phone number without spaces becomes a large integer, and a reference written as 1e5 becomes 100000.

The test is whether adding two values in the column together would mean anything. Postcodes, part numbers, phone numbers and account references all fail it and all belong as text. If the export is yours to configure, adding a prefix or exporting the column quoted keeps it intact. If it is not, look at that column in the converted file before uploading, because an import screen will accept 123 where it should have received 00123 and match it to the wrong record without comment.

The output is UTF-8 with no byte order mark. That is the right choice for scripts, importers and version control, and it is the wrong one for double-clicking on Windows: with no mark at the start of the file, Excel falls back to the system code page and a name like Müller appears as Müller.

The fix is to import rather than open — Data, then Get Data, then From Text/CSV, choosing 65001: Unicode (UTF-8) as the origin. Excel then reads the file correctly and lets you set column types at the same time, which is also the moment to mark identifier columns as text. If the destination is really a spreadsheet rather than an import screen, converting the TSV to XLSX instead avoids the entire question, since a workbook carries its encoding and its types inside it.

A CSV produced here uses the comma, which is the international convention and what nearly every importer means by the name. Excel installed in a locale where the comma is the decimal separator — Germany, France, Spain, Brazil and much of the rest of Europe and South America — will not split it on a double-click, because it is looking for semicolons.

That is a display problem in one program rather than a fault in the file, and the same Get Data import route resolves it by letting you name the delimiter. If the file is destined for colleagues on that kind of Excel rather than for a system, converting the TSV to XLSX is again the shorter path: it sidesteps the delimiter, the encoding and the decimal separator in one step.

The column names come through exactly as the TSV wrote them, including any spaces, units or symbols. Import screens are typically much fussier than that: they match on exact names, or on a fixed order, or they ask you to map each column by hand on the next page.

Editing the header is easier before the conversion than after, when quoting may have appeared and the line is harder to read. If the importer maps columns interactively, a good header still saves time, because the mapping screen shows the names and a sample value and nothing else. Two columns both called `date` will look identical there.

Every row keeps the same number of fields, so an empty value stays as an empty field rather than disappearing, and the file stays rectangular. That is what an importer needs in order to line the columns up at all.

What a blank means is the importer’s decision, not the file’s, and this is where a contact upload goes wrong. Many systems treat an empty field in an update as "set this to nothing" and will clear a phone number that was never included in the export. If the file is updating existing records rather than creating new ones, check the tool’s rule for blanks before uploading, and remove the columns you do not intend to change.

Both formats are plain text, both the reader and the writer are ordinary JavaScript, and the conversion happens on this page. No request carries the file, so a contact list or a customer export is not copied to a third party on its way to the one you actually chose.

No row count is checked and no account is involved; the cap is 100 MB of file, a hundred files to a drop. A table of tens of megabytes converts without ceremony, and one that clears 100 MB is almost certainly too large for the import screen as well and needs splitting either way.

If the destination is a shell pipeline, an R script or anything that splits on a character rather than parsing quotes, the tab-separated original is the better file and the conversion is a step backwards. Quoting is exactly the complication those tools cannot handle.

Keep the TSV as well as the CSV when the export is a source of record. The conversion reads values as numbers where they look numeric, so the CSV is not a lossless copy of the original, and having the untouched export to go back to costs nothing and has saved a great many afternoons.