The Practical Guide to CSV Files

CSV stands for comma-separated values. It is a plain-text representation of a table. A line normally represents a row, and separators divide that row into fields. CSV carries values, but not spreadsheet formatting, formulas, charts, filters or multiple worksheets.

customer_id,name,town,active
000184,"Aisha Khan",Leeds,true
000185,"Tom O'Brien",Bristol,false

The first line is the header row. The next two lines are records. Each record has four fields in the same order as the headers. Although the values may look like numbers or booleans, CSV itself stores text and does not define a data type for each column.

Headers are conventions, not a guarantee

Most business exports put column names in the first row, but the format does not require them. An import process may treat the first customer as a header if you make the wrong choice. Use short, unique names, avoid blank headers, and keep them stable between recurring exports. If a receiving system expects email_address, changing it to Email can break the import even though the file remains valid CSV.

A CSV export from Excel contains the active sheet's displayed values, not a complete workbook. Formulas become their results, styling disappears and extra sheets are not included. Keep the XLSX original if those features matter.

JSON can represent structure that has no neat set of columns. A customer object can contain an address object and an array of orders; a CSV row cannot do so without flattening, repeating or serialising those values. For genuinely tabular data, use the CSV to JSON converter. For an array of flat objects, the JSON to CSV converter can produce a spreadsheet-friendly file.

Despite the name, CSV-like files may use commas, semicolons, tabs or pipes. Semicolons are common where a comma is used as the decimal mark. Tab-separated files are often labelled TSV. A file can be structurally sound but open in one column when the application assumes the wrong delimiter.

sku;description;price
P-104;"Mug, blue";12,50
P-105;"Plate; 20 cm";8,25

Quotes protect a field containing the delimiter. In the semicolon-separated example, the semicolon in "Plate; 20 cm" belongs to the description rather than ending the field. A comma-separated version must likewise quote "Mug, blue". A quote inside a quoted value is normally escaped by doubling it:

id,note
17,"Customer said ""deliver after 5 pm"""

New lines inside a field

A quoted field can legally contain a line break. This often appears in address, notes or product-description columns:

order_id,address,status
8421,"14 Market Street
York
YO1 8AA",dispatched

A parser must continue until the closing quote, so counting physical lines is not always the same as counting records. Splitting a file with a basic “every 1,000 lines” script may cut a record in half. Use a CSV-aware parser.

Encoding maps stored bytes to characters. UTF-8 is the safest general choice because it covers names, accents, non-Latin scripts and symbols in one standard. If a file is decoded using the wrong encoding, José might appear as José, curly quotes may become odd sequences, or characters may be replaced entirely.

“CSV” does not declare an encoding by itself. Some software uses a UTF-8 byte order mark (BOM) at the start to help Excel recognise UTF-8; other pipelines expect UTF-8 without a BOM. When an import specification names an encoding, follow it. Otherwise, export as UTF-8 and test names and symbols before processing the full dataset.

Spreadsheet software tries to infer data types. That is convenient for calculations, but risky for identifiers. A postcode fragment, SKU or customer ID such as 000184 may become 184. An identifier longer than the spreadsheet's numerical precision can be rounded, so its trailing digits change. Scientific notation may hide the change until you save the file.

Treat identifiers as text, even when they contain digits only. During import, choose the column type explicitly rather than double-clicking the CSV. Simply changing a column's display format after opening may be too late: lost zeros and rounded digits cannot be recovered from the altered value.

Dates are ambiguous

03/04/2026 can mean 3 April or 4 March. Values such as 1-2 may also be interpreted as dates when they are product codes. For data interchange, use ISO-style dates such as 2026-04-03. For a precise instant, include time and timezone, for example 2026-04-03T14:30:00Z. Agree whether a blank date means unknown, not applicable or not yet supplied.

Two adjacent delimiters represent an empty field. A trailing delimiter represents an empty final field:

customer_id,email,phone
1042,alex@example.com,
1043,,07700 900123

CSV does not have a universal native null. Depending on the source, missing data may be exported as an empty field, NULL, N/A or another marker. Those are not automatically equivalent: an empty string may mean “known to be blank”, whereas null may mean “unknown”. Confirm the receiving system's rules before replacing one with another.

Empty rows are different again. They can be harmless, or they can be read as records with every value missing. Check whether your importer skips them.

A malformed file does not follow one consistent structure. Common causes include an unclosed quote, an unescaped quote in a notes field, a different number of fields on one row, mixed delimiters, damaged encoding or a line break that was not quoted.

id,name,note
1,Ada,"Requested a callback"
2,Sam,"Said "not this week"
3,Mei,Follow up

Row 2 is ambiguous because its inner quotes are not doubled. A strict importer may reject the file; a forgiving one may silently shift values into the wrong columns. Silent success is therefore not enough.

A practical diagnosis

1. Keep an untouched copy and note the source application's export settings.
2. Check the delimiter and encoding expected by the receiving system.
3. Compare each row's parsed field count with the header count.
4. Inspect the first reported bad row plus the row before it; an unclosed quote can make the next row appear faulty.
5. Correct the source data or export process where possible, then repeat the import with a small sample.

After cleaning data, the duplicate remover can remove repeated records. Decide which columns define a duplicate before discarding anything: two people can share a surname, and one customer can legitimately have several orders.

A file may be valid but too large for a spreadsheet's row limit or your computer's available memory. Browser and desktop tools often need extra memory while parsing, sorting or generating a download, so a 500 MB source can require considerably more than 500 MB of working memory.

Avoid opening a large file merely to inspect its start. Use a suitable viewer or command-line sampling tool, and validate the header plus representative records. For imports with a row or file-size limit, use the CSV splitter to create smaller parts. Keep the header in every part when each file will be imported separately.

For monthly reports or exports with the same columns, the CSV merger can combine files. First verify that headers, column order and meaning match. A column called amount may use pounds in one supplier file and euros in another.

Reduce unnecessary work before sharing or importing. The column extractor can retain only selected fields, which can make an export smaller and support data minimisation.

Before a production import, check a small representative file for:

• Column mapping: names, order and required fields match the target.
• Record shape: every parsed row has the expected number of fields.
• Regional settings: delimiter, decimal mark and date order are agreed.
• Text preservation: identifiers retain leading zeros and all digits.
• Encoding: accented names and symbols survive a round trip.
• Missing values: blank and null conventions have the intended effect.
• Duplicates: retries will not create a second copy of each record.

Exporting “all fields” is rarely the safest default. CRM and ecommerce exports can include internal notes, tokens or personal details that the recipient does not need. Select only the required rows and columns, agree the schema, and record how totals or row counts will be reconciled after import.

A CSV can contain customer details, order history or confidential business data. Before using any service, establish whether the file leaves your device, where it is stored, who can access it and when it is deleted. The tools on CSVTools.pro process selected files locally in your browser rather than sending their contents to the website's server. See the Privacy Policy for current details about file processing and third-party services.

Local processing reduces unnecessary transfer, but it does not remove your own responsibilities. Use an approved device, restrict access to downloaded results, remove fields you do not need and delete working copies according to your organisation's retention rules.

Choose CSV when the data is a single, flat table; broad compatibility matters; and formatting or formulas do not. It is well suited to a product feed, a mailing-list import, monthly analytics rows or a database extract with a documented schema.

Choose XLSX when people need formulas, formatting, charts, comments or several related worksheets. Choose JSON when software needs nested objects, arrays, explicit booleans or a structure that is not naturally rectangular. For complex migrations, a database dump or purpose-built API may preserve types and relationships better than either CSV or a spreadsheet.

Whichever format you choose, document the delimiter, encoding, headers, date format, null convention and expected data types. A short data specification prevents more errors than relying on the file extension alone.