CSV Editor — Tips & Tricks

Back to the overview: CSV Editor · Open the tool live: www.jpkc.com/tools/csv/

The manual explains every function, the examples show the workflows. This page is about what both assume but rarely spell out: where CSV trips up in practice and how to get around it. The interface is in English, so the button and field names are the real labels.

Delimiter pitfalls

Auto-detection checks 30 lines, not just the first. It test-parses the first 30 lines with comma, semicolon, tab, and pipe and takes the character that yields the most consistent column count. A title line above the table or a comma inside a name therefore no longer throws it off. It isn't infallible, though — for instance with very short files in which comma and semicolon fit equally well. Rule of thumb: take one look after importing. If everything is stuck in one column, set Import delimiter manually in Settings; the file is read again right away.
Semicolon is the continental European default. Excel in German-speaking countries and much of Europe exports CSV with ;, because the comma is reserved as the decimal separator. If you edit such files regularly, set Import delimiter to Semicolon straight away — the setting persists in the browser.
Import and export have separate delimiters. Export delimiter defaults to Same as opened file: a semicolon file comes back out as a semicolon file, a tab file as .tsv. To convert — say, a semicolon file into a comma CSV for an import script — set Export delimiter to the target delimiter.
Re-reading only works while the table is unedited. The automatic re-read after changing the delimiter or encoding only kicks in as long as you haven't edited the table yet — otherwise your changes would be thrown away. If you've already edited, deliberately open the file again via Open.
Copy is always tab — whatever is set. The Copy button copies tab-separated (in header mode including the header row), so the data pastes straight into Excel or Google Sheets. Don't confuse this with the CSV export: if you need a comma- or semicolon-separated file, use CSV, not Copy.

Encoding and special characters

The tool usually catches garbled characters itself. Classic Excel "CSV" is stored in Windows-1252, not UTF-8. The automatic mode notices invalid UTF-8 bytes, reads the file as Windows-1252, and tells you so in the yellow notice box. If characters still look wrong — for example with a file in ISO-8859-15 or UTF-16 LE without a BOM — choose the right encoding under Import encoding; an unedited file is read again right away. UTF-16 BE is only detected by its BOM; without one, such a file can't be read correctly.
The notice is a hint, not proof. Windows-1252 is only the most likely alternative. If the file uses a different encoding, individual characters can still be wrong — spot-check accented characters, the euro sign, and quotation marks, and pick a different encoding if needed.
Tick the BOM for Excel. The CSV export always writes UTF-8. But Excel opens a double-clicked CSV file in the system encoding unless there's a BOM at the start — and accented characters turn into gibberish again. With CSV export with UTF-8 BOM, Excel displays the file correctly. If the file goes to a script or a database import, leave the option off unless the target system expects a BOM — which is why it's off by default.
Embedded delimiters, line breaks, and quotes are only safe if the source file wraps them correctly in "…" and doubles any contained quotes. A single " in the middle of a value (5" Display), on the other hand, is read tolerantly as an ordinary character. A file is truly broken when the notice box reports a quote that is never closed — the line number it gives shows you where to fix the source.
On export the editor does it right. Fields with a delimiter, a quote, a line break, or whitespace at the edges are quoted automatically and contained quotes are doubled — the file you get out is clean even when the input wasn't.

Excel and formula injection

Formulas are harmless inside the editor. Cells are plain text; a value like =SUM(A1:A9) or =HYPERLINK(…) is never calculated or executed in the editor.
Excel is a different story. When someone opens your CSV export in Excel or LibreOffice, values starting with =, +, -, or @ are interpreted as formulas there. If the data comes from other people (contact form, shop order, user import), enable Protect against formula injection before exporting: such values get a ' prepended and stay text in Excel. Real numbers such as -2 or +3.5 are left alone.
Not for machine exports. If the file goes to a script or a database rather than to people using Excel, leave the protection off — otherwise the apostrophe really ends up in your data.
Ctrl+S is not the CSV button. Save as… in the context menu and Ctrl+S in the table come from the spreadsheet library: they write a tab-separated Sheet1.csv with a BOM and ignore Export delimiter and Protect against formula injection. For files that go to people using Excel, always use the CSV button.

Using header mode correctly

"First row is header" works in both directions. On import the checkbox makes the first row column headers instead of a data row. On export it prepends the headers as the first row for CSV and Markdown and switches JSON from an array of arrays to an array of objects (header = key). So set or clear the checkbox deliberately, depending on the shape you need at the end.
The switch takes effect immediately. If you opened the file without header mode and realize the first row really is the headers, just enable the checkbox — the first row turns into column names on the spot. Turning it off moves the names back into the data as a row. No reload needed.
Toggling starts a fresh undo history. The header switch rebuilds the table (just like Transpose). Whatever you edited before can't be brought back with Undo afterwards — so toggle header mode early on if you can.

JSON quirks

All values are strings. The JSON export writes every cell value as a string — "30" instead of 30, "true" instead of true. That's honest (CSV has no types), but may need post-processing for an API. If you need numbers or booleans as real JSON types, run the result through the JSON Editor or a small script.
Without a header you get arrays. With header mode off, the JSON is an array of row arrays — good for purely positional data, bad if you want named fields. For named fields, turn header mode on.
Keys are always unique. Empty headers become Column 1, Column 2 …, duplicates get a suffix (email, email_2). If you need different keys, rename the columns before exporting via right-click on the column header → Rename this column.

Numbers, sorting, and statistics

German-style numbers are understood. Sorting and the selection statistics recognize 1,50, 1.234,50, and 1,234.50 alike as numbers. A date such as 2024-07-07, an item code like 12abc, or a value with a unit (5 kg), on the other hand, does not count as a number — the statistics show you in … of … cells numeric how many cells were actually included.
Sort with a double-click. Double-clicking a column header sorts by that column. Numbers land in the right numeric order (9 before 10), text in natural order (file 2 before file 10), and empty cells always at the end — even when sorting descending.
A quick total without Excel. Select a range, and the status bar shows SUM, AVG, MIN, and MAX. Handy for checking an order total or spotting an outlier without loading the file into a spreadsheet application.

Storage and privacy

Everything stays local. There is no upload and no server — the file is read in the browser, edited, and downloaded again. Especially for sensitive CSV files (customers, orders, internal exports), that's the real advantage: the data does not leave your machine.
The automatic save lives in browser storage. The tool saves your table to LocalStorage after each change — convenient for a reload, but tied to this browser on this machine. On a foreign or shared device, clean up with Reset when you're done.
The saved copy knows only one state. There are no versions and no restore button: every change overwrites the previous save. To pin an intermediate state, export it as a file.
Very large files hit limits. Files up to 50 MB can be opened, and even tens of thousands of rows stay responsive because the table only renders the visible part. Browser storage for the auto-save, however, only holds about 2 million characters. Beyond that, the status bar warns you (Too large to keep in this browser), and any older saved copy is deleted so that an outdated state doesn't turn up on your next visit. Editing continues — save your work by exporting it.

Combining with other JPKCom tools

CSV is usually a waypoint. The pattern: inspect and fix in the CSV Editor, then process further in the right tool.

Building the Markdown table into a document? Paste it into the Markdown Editor and see the rendered preview right away.
Working on after the JSON export? The JSON Editor formats and validates the result and helps turn the string values into real types.
Into a format other than CSV or JSON? With Convertor PRO you convert the JSON export onward to YAML, TOML, XML, and back.
Reviewing an exported file? The Source Viewer shows it with syntax highlighting before you hand it on.

More context: the overview for the big picture, the manual for every function, and the examples for the step-by-step workflows. You can try all of it directly in the tool.