API reference

Validate and format CSV

Check CSV against RFC 4180, detect the delimiter and header, infer column types, list every problem with its position, and rewrite it consistently.

GET POST /api/csv/format MCP tool 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.

Browser version (runs locally): CSV Validate & Format.

Try it
Credentials
Remember these credentials

Not saved: you will paste them again next time.

No credentials yet? Get a client ID and secret. They are sent only to this site, in an Authorization: Basic header.

POST /api/csv/format with a JSON body

Parameters

Parameter Type Default Description
input required string (≤ 100000 chars) — The CSV text. Send it as a string field or as a raw text/plain POST body.
delimiter auto | comma | semicolon | tab | pipe auto 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 auto 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 false Remove spaces and tabs around unquoted values. Quoted values are always kept exactly.
quoting minimal | all | nonnumeric minimal 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 lf 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 same Delimiter for the formatted output; same keeps the input delimiter.

Also accepted on every endpoint: format (json | text). Parameters may come from the query string, a JSON object body, an urlencoded or multipart form, or (for input) a text/plain body.

Global parameters
Parameter Type Default Description
format json | text json json returns the documented object. text returns only the primary value as text/plain (see "Text output").

Response 200 application/json

Field Type Description
valid boolean True when there are no error diagnostics.
summary object
summary.valid boolean True when there are no error findings.
summary.errors integer Number of errors.
summary.warnings integer Number of warnings.
summary.infos integer Number of informational findings.
diagnostics array of object Findings in document order.
diagnostics[].rule string Rule ID, e.g. syntax, duplicate-keys, trailing-spaces.
diagnostics[].severity "error" | "warning" | "info" error makes the document invalid; warning and info are style.
diagnostics[].message string What is wrong and how to fix it.
diagnostics[].line integer 1-based line where the problem starts.
diagnostics[].column integer 1-based column (Unicode code points).
diagnostics[].offset integer 0-based UTF-16 offset into the input.
diagnostics[].endLine optional integer 1-based end line, when the problem spans a range.
diagnostics[].endColumn optional integer 1-based end column.
diagnostics[].endOffset optional integer 0-based end offset.
dialect object How the input was read.
dialect.delimiter "," | ";" | " " | "|" The delimiter used to read the input (a tab is "\t").
dialect.delimiterDetected boolean True when the delimiter was detected rather than given (false when detection fell back to comma).
dialect.lineEnding "lf" | "crlf" | "cr" | "mixed" | "none" Line breaks between rows (line breaks inside quoted values are not counted).
dialect.hasHeader boolean True when the first row is treated as the header.
dialect.headerDetected boolean True when that decision came from the heuristic (header=auto).
dialect.bom boolean True when the input starts with a byte-order mark.
header array of string | null The header row; null when there is none.
columnCount integer Expected fields per row: the header length, or the most common row length.
rowCount integer Data rows (the header is not counted).
columns array of object One entry per column.
columns[].name string Header name, or Column N without a header.
columns[].type "empty" | "integer" | "number" | "boolean" | "date" | "datetime" | "text" | "mixed" Inferred type of the non-empty values: integer, number (integers and decimals), boolean (true/false), date (YYYY-MM-DD), datetime (ISO 8601), text, mixed, or empty when every value is empty.
columns[].empty integer Empty (or missing) values.
columns[].distinct integer Distinct non-empty values.
columns[].maxLength integer Longest value, in Unicode code points.
preview array of array of string The first 20 data rows.
formatted string | null The normalised CSV; null when an unclosed quote makes the structure unknowable.

Text output (format=text, text/plain): The formatted CSV when valid; otherwise one line per error: line L, column C: [rule] message.

Errors

Errors are JSON: {"error": "<message>", "code": "<code>", "param": "<name>"?}.

Status Code When
400 invalid_parameter input is longer than 100,000 characters, or an option is not a listed value. Invalid CSV is NOT an error: it returns 200 with valid: false.
400 missing_parameter A required parameter is absent.
400 unknown_parameter A parameter name is not in the table above (typos are rejected, not ignored).
413 payload_too_large The request body exceeds 1 MiB.
405 method_not_allowed A method other than GET, POST or OPTIONS.

Behaviour

  • Idempotent: the same arguments always return the same result.
  • Requires a client ID and secret (authentication). Each call is recorded as one metadata row against your client ID; inputs and outputs are never stored or logged. Responses carry cache-control: no-store.
  • CORS: any origin may call this endpoint from a browser, using your credentials.

Examples

Responses below were produced by running each request through this code at build time. Long strings are shortened.

RFC 4180 quoting: embedded commas, quotes and line breaks

curl -X POST 'https://util.caruana.me/api/csv/format' \
  -H 'content-type: application/json' \
  -d '{"input":"id,product,notes,price\r\n1,\"Widget, large\",\"Says \"\"hello\"\"\",9.99\r\n2,Gadget,\"two\r\nlines\",12\r\n"}'

Response 200

{
  "valid": true,
  "summary": {
    "valid": true,
    "errors": 0,
    "warnings": 0,
    "infos": 0
  },
  "diagnostics": [],
  "dialect": {
    "delimiter": ",",
    "delimiterDetected": true,
    "lineEnding": "crlf",
    "hasHeader": true,
    "headerDetected": true,
    "bom": false
  },
  "header": [
    "id",
    "product",
    "notes",
    "price"
  ],
  "columnCount": 4,
  "rowCount": 2,
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "empty": 0,
      "distinct": 2,
      "maxLength": 1
    },
    {
      "name": "product",
      "type": "text",
      "empty": 0,
      "distinct": 2,
      "maxLength": 13
    },
    {
      "name": "notes",
      "type": "text",
      "empty": 0,
      "distinct": 2,
      "maxLength": 12
    },
    {
      "name": "price",
      "type": "number",
      "empty": 0,
      "distinct": 2,
      "maxLength": 4
    }
  ],
  "preview": [
    [
      "1",
      "Widget, large",
      "Says \"hello\"",
      "9.99"
    ],
    [
      "2",
      "Gadget",
      "two\r\nlines",
      "12"
    ]
  ],
  "formatted": "id,product,notes,price\n1,\"Widget, large\",\"Says \"\"hello\"\"\",9.99\n2,Gadget,\"two\r\nlines\",12\n"
}
Same call as an MCP tools/call
{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "csv_format",
    "arguments": {
      "input": "id,product,notes,price\r\n1,\"Widget, large\",\"Says \"\"hello\"\"\",9.99\r\n2,Gadget,\"two\r\nlines\",12\r\n"
    }
  }
}

Semicolons, padded delimiters and a short row

curl -X POST 'https://util.caruana.me/api/csv/format' \
  -H 'content-type: application/json' \
  -d '{"input":"name; city; joined\nAda; London; 2024-01-15\nGrace;New York\nLinus;Helsinki;2023-11-02\n","trim_fields":true}'

Response 200

{
  "valid": false,
  "summary": {
    "valid": false,
    "errors": 1,
    "warnings": 0,
    "infos": 0
  },
  "diagnostics": [
    {
      "rule": "field-count",
      "severity": "error",
      "message": "Line 3 has 2 fields; expected 3 (the header has 3).",
      "line": 3,
      "column": 1,
      "offset": 43,
      "endLine": 3,
      "endColumn": 15,
      "endOffset": 57
    }
  ],
  "dialect": {
    "delimiter": ";",
    "delimiterDetected": true,
    "lineEnding": "lf",
    "hasHeader": true,
    "headerDetected": true,
    "bom": false
  },
  "header": [
    "name",
    "city",
    "joined"
  ],
  "columnCount": 3,
  "rowCount": 3,
  "columns": [
    {
      "name": "name",
      "type": "text",
      "empty": 0,
      "distinct": 3,
      "maxLength": 5
    },
    {
      "name": "city",
      "type": "text",
      "empty": 0,
      "distinct": 3,
      "maxLength": 8
    },
    {
      "name": "joined",
      "type": "date",
      "empty": 1,
      "distinct": 2,
      "maxLength": 10
    }
  ],
  "preview": [
    [
      "Ada",
      "London",
      "2024-01-15"
    ],
    [
      "Grace",
      "New York"
    ],
    [
      "Linus",
      "Helsinki",
      "2023-11-02"
    ]
  ],
  "formatted": "name;city;joined\nAda;London;2024-01-15\nGrace;New York\nLinus;Helsinki;2023-11-02\n"
}
Same call as an MCP tools/call
{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "csv_format",
    "arguments": {
      "input": "name; city; joined\nAda; London; 2024-01-15\nGrace;New York\nLinus;Helsinki;2023-11-02\n",
      "trim_fields": true
    }
  }
}

Stray quote, formula and mixed types; reformatted as tab-separated

curl -X POST 'https://util.caruana.me/api/csv/format' \
  -H 'content-type: application/json' \
  -d '{"input":"sku,size,qty\nA1,5\" disk,3\nA2,=HYPERLINK(\"http://x\"),N/A\nA3,large,7\n","output_delimiter":"tab","quoting":"nonnumeric"}'

Response 200

{
  "valid": false,
  "summary": {
    "valid": false,
    "errors": 2,
    "warnings": 0,
    "infos": 2
  },
  "diagnostics": [
    {
      "rule": "stray-quote",
      "severity": "error",
      "message": "Quote inside an unquoted field. Put the whole field in quotes and double this quote: \"…\"\"…\".",
      "line": 2,
      "column": 5,
      "offset": 17,
      "endLine": 2,
      "endColumn": 6,
      "endOffset": 18
    },
    {
      "rule": "formula-injection",
      "severity": "info",
      "message": "Starts with \"=\": a spreadsheet may run this value as a formula. Escape it (e.g. prefix a ') before opening untrusted data in Excel or Sheets.",
      "line": 3,
      "column": 4,
      "offset": 29,
      "endLine": 3,
      "endColumn": 26,
      "endOffset": 51
    },
    {
      "rule": "stray-quote",
      "severity": "error",
      "message": "Quote inside an unquoted field. Put the whole field in quotes and double this quote: \"…\"\"…\".",
      "line": 3,
      "column": 15,
      "offset": 40,
      "endLine": 3,
      "endColumn": 16,
      "endOffset": 41
    },
    {
      "rule": "mixed-types",
      "severity": "info",
      "message": "Column \"qty\" is mostly number, but this value is text (\"N/A\").",
      "line": 3,
      "column": 27,
      "offset": 52,
      "endLine": 3,
      "endColumn": 30,
      "endOffset": 55
    }
  ],
  "dialect": {
    "delimiter": ",",
    "delimiterDetected": true,
    "lineEnding": "lf",
    "hasHeader": true,
    "headerDetected": true,
    "bom": false
  },
  "header": [
    "sku",
    "size",
    "qty"
  ],
  "columnCount": 3,
  "rowCount": 3,
  "columns": [
    {
      "name": "sku",
      "type": "text",
      "empty": 0,
      "distinct": 3,
      "maxLength": 2
    },
    {
      "name": "size",
      "type": "text",
      "empty": 0,
      "distinct": 3,
      "maxLength": 22
    },
    {
      "name": "qty",
      "type": "mixed",
      "empty": 0,
      "distinct": 3,
      "maxLength": 3
    }
  ],
  "preview": [
    [
      "A1",
      "5\" disk",
      "3"
    ],
    [
      "A2",
      "=HYPERLINK(\"http://x\")",
      "N/A"
    ],
    [
      "A3",
      "large",
      "7"
    ]
  ],
  "formatted": "\"sku\"\t\"size\"\t\"qty\"\n\"A1\"\t\"5\"\" disk\"\t3\n\"A2\"\t\"=HYPERLINK(\"\"http://x\"\")\"\t\"N/A\"\n\"A3\"\t\"large\"\t7\n"
}
Same call as an MCP tools/call
{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "csv_format",
    "arguments": {
      "input": "sku,size,qty\nA1,5\" disk,3\nA2,=HYPERLINK(\"http://x\"),N/A\nA3,large,7\n",
      "output_delimiter": "tab",
      "quoting": "nonnumeric"
    }
  }
}

An unclosed quote

curl -X POST 'https://util.caruana.me/api/csv/format' \
  -H 'content-type: application/json' \
  -d '{"input":"a,b\n1,\"open\n2,3\n"}'

Response 200

{
  "valid": false,
  "summary": {
    "valid": false,
    "errors": 1,
    "warnings": 0,
    "infos": 0
  },
  "diagnostics": [
    {
      "rule": "unclosed-quote",
      "severity": "error",
      "message": "This quote is never closed, so the rest of the text is read as one value. Add the closing quote, or double any quote that is part of the value (\"\").",
      "line": 2,
      "column": 3,
      "offset": 6,
      "endLine": 2,
      "endColumn": 4,
      "endOffset": 7
    }
  ],
  "dialect": {
    "delimiter": ",",
    "delimiterDetected": true,
    "lineEnding": "lf",
    "hasHeader": true,
    "headerDetected": true,
    "bom": false
  },
  "header": [
    "a",
    "b"
  ],
  "columnCount": 2,
  "rowCount": 1,
  "columns": [
    {
      "name": "a",
      "type": "integer",
      "empty": 0,
      "distinct": 1,
      "maxLength": 1
    },
    {
      "name": "b",
      "type": "text",
      "empty": 0,
      "distinct": 1,
      "maxLength": 9
    }
  ],
  "preview": [
    [
      "1",
      "open\n2,3\n"
    ]
  ],
  "formatted": null
}
Same call as an MCP tools/call
{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "csv_format",
    "arguments": {
      "input": "a,b\n1,\"open\n2,3\n"
    }
  }
}