# CSV Validate & Format > Validate CSV with exact error positions, detect the delimiter and header, infer column types, and reformat or convert to and from JSON. Category: Text & Data · Page: https://util.caruana.me/tools/csv/ ## API, MCP and A2A ### Validate and format CSV (`csv_format`) `GET|POST https://util.caruana.me/api/csv/format` · MCP tool `csv_format` · A2A skill `csv_format` · [reference](https://util.caruana.me/reference/csv_format/) Parses `input` as RFC 4180 CSV, also accepting semicolon, tab and pipe delimiters and LF or CR line breaks. Reports every problem as a diagnostic with a stable rule ID, severity, line, column and offset: unclosed quotes, stray quotes, text after a closing quote, rows with the wrong number of fields (errors); empty and duplicate header names, padded delimiters, blank lines, mixed line endings (warnings); and mixed column types, inconsistent quoting, trailing delimiters and values a spreadsheet could run as formulas (info). Invalid CSV is a successful response with `valid: false`, so check `valid`. `formatted` is the data rewritten with one delimiter, one quoting style and one line ending, blank lines and the byte-order mark removed, ending with a line break; it is null only when an unclosed quote makes the structure unknowable. Rows themselves are not returned: `preview` holds the first 20 data rows. At most 100 diagnostics are listed per rule, then one summarising the rest. | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `input` | string (≤ 100000 chars) | yes | The CSV text. Send it as a string field or as a raw `text/plain` POST body. | | `delimiter` | auto \| comma \| semicolon \| tab \| pipe | no | Field delimiter of the input: `comma`, `semicolon`, `tab` or `pipe`. `auto` picks the one that gives the most consistent field count over the first 50 records, ignoring quoted text, and falls back to comma. | | `header` | auto \| yes \| no | no | Whether the first row is a header. `auto` says yes when its values are all text (no numbers, booleans or dates), all different, and none repeats in its column below. | | `trim_fields` | boolean | no | Remove spaces and tabs around unquoted values. Quoted values are always kept exactly. | | `quoting` | minimal \| all \| nonnumeric | no | `minimal` quotes only values that need it (a delimiter, quote or line break, or leading/trailing whitespace); `all` quotes every value; `nonnumeric` quotes everything except numbers. | | `line_ending` | lf \| crlf | no | Line break written after every row. `crlf` is what RFC 4180 specifies; `lf` is what most tools write, and every reader accepts both. | | `output_delimiter` | same \| comma \| semicolon \| tab \| pipe | no | Delimiter for the formatted output; `same` keeps the input delimiter. | Privacy: The server receives the CSV text, processes it in memory inside the request, and returns the result. Your input is not stored or logged; only the per-call metadata row (operation, time, status, sizes) is recorded. The server makes no network requests. For confidential data use the page, which does the same work in your browser and sends nothing. ### Convert CSV to JSON (`csv_to_json`) `GET|POST https://util.caruana.me/api/convert/csv-to-json` · MCP tool `csv_to_json` · A2A skill `csv_to_json` · [reference](https://util.caruana.me/reference/csv_to_json/) Reads `input` exactly as `/api/csv/format` does (same delimiter and header detection) and converts it to JSON. `shape=objects` gives one object per row, keyed by the header names (an empty name becomes `column_N`; a repeated name gets `_2`, `_3`, …). `shape=arrays` gives one array per row, the header row first. `auto` is objects when there is a header. Without `types` every value is a string, exactly as written. With `types`, a column whose values are all integers or numbers becomes JSON numbers (every digit kept, even beyond 2^53), a true/false column becomes booleans, and empty values become `null`; text, date and mixed columns stay strings. CSV with errors (see the format operation) is rejected rather than guessed at. | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `input` | string (≤ 50000 chars) | yes | The CSV text. Send it as a string field or as a raw `text/plain` POST body. | | `delimiter` | auto \| comma \| semicolon \| tab \| pipe | no | Field delimiter of the input: `comma`, `semicolon`, `tab` or `pipe`. `auto` picks the one that gives the most consistent field count over the first 50 records, ignoring quoted text, and falls back to comma. | | `header` | auto \| yes \| no | no | Whether the first row is a header. `auto` says yes when its values are all text (no numbers, booleans or dates), all different, and none repeats in its column below. | | `trim_fields` | boolean | no | Remove spaces and tabs around unquoted values. Quoted values are always kept exactly. | | `shape` | auto \| objects \| arrays | no | `objects` (needs a header), `arrays`, or `auto`: objects when there is a header, arrays otherwise. | | `types` | boolean | no | Convert number and boolean columns to JSON numbers and booleans, and empty values to `null`. | Privacy: The server receives the CSV text, processes it in memory inside the request, and returns the result. Your input is not stored or logged; only the per-call metadata row (operation, time, status, sizes) is recorded. The server makes no network requests. For confidential data use the page, which does the same work in your browser and sends nothing. ### Convert JSON to CSV (`json_to_csv`) `GET|POST https://util.caruana.me/api/convert/json-to-csv` · MCP tool `json_to_csv` · A2A skill `json_to_csv` · [reference](https://util.caruana.me/reference/json_to_csv/) Parses `input` strictly as JSON. An array of objects becomes one row per object, with a header made of every key in first-seen order (a missing key gives an empty cell); a single object becomes one row. An array of arrays becomes one row per array, with no header. Strings are written as they are, numbers exactly as written in the JSON (`1.0` and 20-digit integers survive), booleans as `true`/`false`, `null` as an empty cell, and nested objects or arrays as compact JSON text (listed in `notes`). Values are quoted per `quoting`. | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `input` | string (≤ 200000 chars) | yes | The JSON text: an array of objects, an array of arrays, or one object. Send it as a string field or as a raw `text/plain` POST body. | | `delimiter` | comma \| semicolon \| tab \| pipe | no | Delimiter for the output: `comma`, `semicolon`, `tab` or `pipe`. | | `header` | boolean | no | Write the header row (arrays of objects only). | | `quoting` | minimal \| all \| nonnumeric | no | `minimal` quotes only values that need it (a delimiter, quote or line break, or leading/trailing whitespace); `all` quotes every value; `nonnumeric` quotes everything except numbers. | | `line_ending` | lf \| crlf | no | Line break written after every row. `crlf` is what RFC 4180 specifies; `lf` is what most tools write, and every reader accepts both. | Privacy: The server receives the JSON text, processes it in memory inside the request, and returns the result. Your input is not stored or logged; only the per-call metadata row (operation, time, status, sizes) is recorded. The server makes no network requests. For confidential data use the page, which does the same work in your browser and sends nothing. ## User guide The studio works like a desktop window: a menu bar (**File**, **Edit**, **View**, **Settings**, **Help**) with the **full-screen** icon at the right of the same row. **Settings → Options…** opens the options in an overlay (or **View → Inline config** shows them in the card), and **Help → Keyboard shortcuts…** lists the keys. 1. **Paste CSV** into the **Input** view, or choose **File → Load sample** to see one with a few problems. It is checked as you type; line numbers make positions easy to find. 2. Read the verdict and the **Problems** view (the button at the right of the status line shows the count). Each problem has a severity, a line and column, and a message saying how to fix it. Click one to jump to it: in the input, the text is selected; in the **Table** view, the bad row is highlighted. 3. Look at the **Table** view: a grid of the data with each column's inferred type (integer, number, boolean, date, datetime, text, mixed or empty). Click a column heading to sort by it. Sorting only changes the view, not your data. 4. **Edit → Format** (or **Ctrl+Enter**) writes the **Formatted** view: the same data with one delimiter, one quoting style and one line ending, without blank lines or a byte-order mark, and with exactly one line break at the end. 5. **Copy** or **Download** the result, or **File → Clear input** to start again. The **full-screen** icon at the right of the menu bar makes the studio fill the window; **Esc** returns. **Options.** - **Delimiter**: _Auto_ (detected), comma, semicolon, tab or pipe. Detection picks the delimiter that splits the first 50 rows into the same number of fields without breaking any quotes; set it yourself if it guesses wrong. - **Header**: _Auto_, _Yes_ or _No_. Auto treats the first row as a header when its values are all text (no numbers, true/false or dates), all different, and none of them appears again in its column below. - **Quoting** (for the formatted output): _Minimal_ quotes only values that need it; _All_ quotes every value; _Non-numeric_ quotes everything except numbers, which some importers use to tell text from numbers. - **Output delimiter**: _Same as input_, or another delimiter to convert, e.g. semicolons to commas. - **Line ending**: _LF_ (the default; what most tools write) or _CRLF_ (what RFC 4180 specifies and some older Windows software expects). Readers accept either. - **Trim**: remove spaces and tabs around unquoted values. Quoted values are never trimmed: quoting is how a file says the spaces matter. **What the checks catch.** The errors are the problems that change how a file is read: an unclosed quote swallows every row after it into one value; a quote in the middle of an unquoted value, or text after a closing quote, is read differently by different programs; a row with a missing or extra field shifts every value after it into the wrong column. The warnings are things that are probably mistakes, such as `a, b` (the space is part of the value, so the column is `" b"`, not `"b"`), duplicate or empty header names, and blank lines. The info notes are worth knowing but often deliberate: a column that mixes numbers with `N/A`, quoting that varies from row to row, a delimiter at the end of every row, and values that a spreadsheet would run as a formula. **Formatting never changes your values.** Only the delimiter, quotes, line breaks between rows, blank lines and padding outside quotes change. A value with leading or trailing spaces is quoted in the output, so tools that trim unquoted values keep the spaces. The exception is **Trim**, which you ask for. Formatting a file twice gives the same result as formatting it once. **Spreadsheet formulas.** When a CSV is opened in Excel, LibreOffice or Google Sheets, a value starting with `=`, `+`, `-` or `@` can be run as a formula; `=HYPERLINK(...)` or worse in data from a web form is a known attack ("CSV injection"). The page flags such values, unless they are plain numbers like `-5`. It does not change them: escaping is for the program that exports data to spreadsheets, usually by putting a `'` in front. ## Specification **Grammar.** RFC 4180, read leniently and reported strictly. - A file is a sequence of records separated by line breaks; a record is a sequence of fields separated by the delimiter. The last record may or may not end with a line break. - A field is either unquoted (any characters except the delimiter, `"` and line breaks) or quoted: `"` … `"`, where the content may include the delimiter and line breaks, and a quote is written `""`. - Spaces are part of a field. `a , b` is the two values `"a "` and `" b"`. **Beyond RFC 4180.** These are accepted: - Delimiters `;`, tab and `|` as well as `,`. - LF and lone CR line breaks as well as CRLF, and a mix of them (reported by `line-endings`). - Spaces or tabs between a delimiter and a quoted field (`a, "b"`): they are ignored and reported by `whitespace`. A tab is never padding in a tab-separated file. - A byte-order mark: removed before parsing, so it never becomes part of the first header name. - Rows with different numbers of fields are kept as they are (and reported). - After an error that leaves the structure readable, parsing continues so every problem is listed: a stray quote stays in the value, and text after a closing quote is appended to it. Only an unclosed quote stops parsing, because everything after it is part of one value; then there is no formatted output. **Blank lines.** A blank line between rows is skipped, with a `blank-line` warning; blank lines at the end are reported by `final-newline`. In a file with a single column an empty line is an empty value instead (there is no other way to write one), and the formatter writes it as `""` so it cannot be mistaken for a blank line. **Delimiter detection** (when _Auto_). Each candidate (`,` `;` tab `|`) is used to parse the first 50 records (and at most the first 32,768 characters), quotes respected. Candidates that produce quote errors lose to those that do not; among the rest, the one whose most common field count covers the most records wins, and a tie goes to more fields, then to the order listed. A candidate must give at least two fields. If none does, the file is read as one column with `,` (and `delimiterDetected` is false). Limits: a file where several delimiters split every row consistently (`a;b,c` on every line) is read with the earliest-listed one; a file whose first 50 rows are not typical can mislead it. **Header detection** (when _Auto_). The first row is a header when its non-empty values are all text (see the types below), are all different, at least one is non-empty, and none of them appears again in the same column of the next 49 rows. Limits: a file of text with no header (`Ada,London`) is read as having one, because the two cannot be told apart; a header containing a year or other number is read as data. Set **Header** when it matters. **Column count.** The header's length, or without a header the most common row length (ties go to the first seen). Each row with a different length gets a `field-count` error. **Rules.** Every finding has a rule ID, a severity, a message, and the 1-based line and column (in Unicode code points) and 0-based offset (in UTF-16 units) where it starts, with an end position when it covers a range. A line break inside a quoted value starts a new line for positions, as it does in an editor. At most 100 findings are listed per rule, followed by one saying how many more there are. | Rule | Severity | Fixed by Format | Reported when | | ---------------------- | --------------- | --------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `unclosed-quote` | error | no | A quoted field never closes. At the opening quote. There is no formatted output. | | `stray-quote` | error | yes | A `"` appears inside an unquoted field (`5" disk`). At the first such quote; Format quotes the field and doubles it. | | `text-after-quote` | error | yes | Text follows a closing quote before the delimiter (`"abc"def`). From the closing quote to the end of the field; Format writes the joined value. | | `field-count` | error | no | A row has more or fewer fields than expected. Covers the row; the message gives its line and both counts. | | `empty-header` | warning | no | A header name is empty or only whitespace (except the extra column from `trailing-delimiter`). | | `duplicate-header` | warning | no | A header name repeats an earlier one exactly. At the repeat; the message names the first column. | | `whitespace` | warning or info | partly | Spaces or tabs around a value. Warning when they touch a delimiter (`a, b`), make up the whole value, or sit outside quotes (Format removes the last kind); info at the start or end of a line. Not reported inside quotes, or for unquoted values when **Trim** is on. | | `blank-line` | warning | yes | An empty line between rows (except in a one-column file). | | `trailing-delimiter` | info | no | Every row ends with an empty, unquoted field: probably a delimiter after the last value. Format keeps the column, since it is data. | | `line-endings` | warning | yes | Rows end with more than one of LF, CRLF and CR. At the first line break of the least used style. Line breaks inside quotes are values and are not counted. | | `byte-order-mark` | info | yes | The text starts with U+FEFF. Info, not a warning: Excel needs it to open UTF-8 CSV correctly. Format removes it; add it back if your users open the file in Excel. | | `final-newline` | info | yes | The last row has no line break (RFC 4180 allows this), or blank lines follow it. | | `mixed-types` | info | no | A column's non-empty values have more than one type (integers and decimals count as numbers; dates and datetimes as datetimes). At the first value that differs from the most common type. | | `inconsistent-quoting` | info | yes | In one column (header excluded), some values are quoted although they need not be, while others are unquoted. At the first value of the less common style. | | `formula-injection` | info | no | A value (header included) starts with `=`, `+`, `-`, `@`, a tab or a CR, is at least two characters long, and is not a number. | The shared text rules for the other formats are adapted: trailing spaces are part of the last value in CSV, so they are reported by `whitespace` instead of `trailing-spaces`; line length is not checked, because long rows are normal; mixed line endings and the final newline only count line breaks between rows. **Types.** Values are typed after removing surrounding spaces; empty values are ignored, and a column's type comes from all of its non-empty values. | Type | Matches | | ---------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `integer` | An optional sign and digits, without leading zeros: `0`, `-12`, `+7`. `007` and `00501` are text, because leading zeros mean an identifier (a ZIP code, an account number). | | `number` | An integer or decimal with an optional exponent: `1.5`, `.5`, `-2e10`. A column of integers and decimals is `number`. Not numbers: `1.`, `1,000`, `1_000`, `0x1F`, `NaN`, `Infinity`, `$5`, `5%`. | | `boolean` | `true` or `false`, in any case. `yes`/`no` and `1`/`0` are not booleans. | | `date` | `YYYY-MM-DD`, a real calendar date (`2024-02-29` yes, `2023-02-29` no). | | `datetime` | A date, `T` or a space, `HH:MM`, optional `:SS` and fraction, optional `Z` or `±HH:MM`. A column of dates and datetimes is `datetime`. | | `text` | Anything else. | | `mixed` | Non-empty values of more than one of the above. | | `empty` | Every value is empty. | Column statistics count empty values (missing fields in short rows included), distinct non-empty values, and the longest value in code points. **Formatting.** The header (if any) and the data rows are written with the output delimiter and line ending, one line break after every row. _Minimal_ quoting quotes a value that contains the delimiter, a quote, CR or LF, or starts or ends with a space or tab. _All_ quotes every value, empty ones too. _Non-numeric_ quotes every value that is not an integer or number (empty values included). Quotes inside values are doubled. A row consisting of one empty value is written as `""`. **Conversions.** - **CSV → JSON**: reads the CSV with the same rules and refuses one with errors. _Objects_ (needs a header) gives one object per row, keyed by header name; an empty name becomes `column_N` and a repeated one gets `_2`, `_3`. _Arrays_ gives one array per row, the header row first. With **types**, `integer` and `number` columns become JSON numbers (every digit kept, even beyond 2^53; `+1` and `.5` become `1` and `0.5`), `boolean` columns become `true`/`false`, and empty values become `null`; text, date and mixed columns stay strings. - **JSON → CSV**: an array of objects gives a header of every key in first-seen order and one row per object (a missing key gives an empty value); a single object gives one row; an array of arrays gives rows without a header. Strings are written as they are, numbers exactly as written in the JSON, `true`/`false` as words, `null` as an empty value, and nested objects and arrays as compact JSON text, with a note saying which columns contain them. **API.** - `POST /api/csv/format` with `input` and the options as `delimiter` (`auto`, `comma`, `semicolon`, `tab`, `pipe`), `header`, `quoting`, `output_delimiter`, `line_ending` and `trim_fields`. It returns `valid`, a `summary` of counts, every `diagnostic`, the `dialect` that was used, the header, the column count and statistics, a `preview` of the first 20 rows, and `formatted`. Invalid CSV is a 200 response with `valid: false`, not an HTTP error. - `POST /api/convert/csv-to-json` (`shape`, `types`) and `POST /api/convert/json-to-csv` (`delimiter`, `header`, `quoting`, `line_ending`). They return 422 for CSV with errors or JSON of the wrong shape. - `input` may be at most 100,000 characters for `csv_format`, 50,000 for `csv_to_json` (it also builds and serializes JSON) and 200,000 for `json_to_csv`, which keeps each call within the server's CPU budget. The page has no limit.