Free graph maker & data converters

How to Clean a Messy CSV File

By Plotiq Editorial Team · Published 14 November 2026 · Updated 14 November 2026 · 5 min read

CSV looks like the simplest format there is, which is exactly why files from instruments, European spreadsheets and web exports break so often. This guide lists the problems you will meet most, how to recognise each one, and how to fix them before you plot or convert the data.

Why CSV files break

CSV stands for comma-separated values, but there is no strict standard that every program follows. Each program that writes a CSV makes its own choices about the separator, the quoting, how decimals are written, which character encoding is used and what counts as an empty value. A file that opens perfectly in one program can appear as a single jumbled column in another. Cleaning a file is mostly a matter of recognising which choice was made and translating it.

The best first step is always to look at the raw text. Opening the file in a CSV viewer or a plain text editor, rather than double-clicking it into a spreadsheet, shows exactly what is in it before any program guesses and alters it.

Problem 1: everything is in one column

If the whole row appears as one cell, the separator is not the one your program expects. In many European countries the comma is the decimal mark, so spreadsheets there write CSV using a semicolon as the separator. Instruments and Unix tools often use tabs, and some logs use a pipe character or runs of spaces. Look at the first line: if you see semicolons, tabs or pipes between the headers, that is your delimiter.

The fix is to tell the program which separator to use, or to detect it automatically. The Plotiq tools detect comma, semicolon, tab, pipe and space-separated data, and let you pick one manually when a file is unusual.

Problem 2: decimal commas

In files from Germany, France, Turkey, Brazil and many other countries, three and a half is written 3,5 rather than 3.5. If you read such a file with a program expecting decimal points, the number is either treated as text or split into two columns. Watch for the pattern together with semicolons, since both come from the same locale.

Convert the comma to a point only in numeric fields. A safe way is to check that a column consistently looks like digits, a comma and digits. Thousands separators are the opposite problem: 1,234.5 in one locale is 1.234,5 in another, so decide which one the file uses before changing anything.

Problem 3: title lines, comments and metadata above the header

Many data loggers and lab instruments write a few lines of information before the real table: the device name, the operator, the date, a comment starting with #. A program reading the file takes the first line as the header and the data becomes garbled. Delete or skip every line above the real header row, and keep that information somewhere else, because it is often useful for your methods section.

Some instruments go further and write the data in rows rather than columns, with a label followed by values (“pressure,0.1,0.2,0.3”). Those need transposing so each measurement has its own column.

Problem 4: units and symbols inside the numbers

Cells such as “95,0 °C”, “$1,200.50”, “12 %” or “5 mg” are text, not numbers, so no chart can use them. The units belong in the column header: “Temperature (°C)”. Likewise accounting negatives like (300) mean −300, and currency symbols and thousands separators must go before arithmetic works.

Fix this column by column, removing the unit from every cell and adding it to the header. If a column mixes units (some cells in mg, some in g), stop and convert them to one unit first, because stripping the symbols would silently mix scales.

What you seeWhat it meansClean value
95,0 °CDecimal comma with unit95.0 (header: Temperature (°C))
$1,200.50Currency with thousands separator1200.50
12 %Percent sign12 (header: Share (%))
(300)Accounting negative−300
NA, N/A, –, #DIV/0!Missing or errorEmpty cell

Problem 5: missing values, blank rows and duplicates

Missing values arrive as empty cells, NA, N/A, NaN, a dash, -999 or an error code such as #DIV/0!. Pick one representation (an empty cell is easiest for charting) and apply it everywhere. Never leave placeholders like -999 in a numeric column, because they will be plotted and averaged as real values and ruin the scale.

Blank rows and columns, usually left by a spreadsheet export or a trailing comma, should be removed. Duplicate rows need more care: remove exact duplicates that come from a double export, but keep repeated measurements that happen to give the same reading, since those are real data.

Problem 6: encoding and strange characters

If names appear as “Müller” or degree signs become “°”, the file was saved in one character encoding and opened in another. Modern files are usually UTF-8, but Excel on Windows often writes older encodings such as Windows-1252 (Western European) or Windows-1254 (Turkish). The fix is to reopen the file with the correct encoding, or re-save it as UTF-8. Look for a question mark or a black diamond character, which means information was already lost on saving.

A repeatable cleaning routine

First inspect the raw file in a viewer. Then identify the delimiter, the header row and the decimal mark. Remove the lines above the header. Strip units into column names, convert missing-value codes into empty cells, delete blank rows and true duplicates, and check each numeric column for text that slipped through. Finally spot-check a few rows against the original, and keep the raw file untouched so you can always return to it. Plotiq lists every change it makes while parsing so you can verify each one.

Frequently asked questions

How do I open a CSV without Excel changing it?
Use a CSV viewer or import the file through the “From text” import option and set each column’s type. Double-clicking can convert IDs to numbers, remove leading zeros and turn gene names into dates.
Why did my ZIP codes lose their leading zeros?
The program treated them as numbers. Keep identifiers as text. A value like 00123 is a label, not a quantity.
How do I find out which delimiter a file uses?
Open it in a text editor and look at the characters between the column names in the first line: comma, semicolon, tab, pipe or space.
Is it safe to clean a file online?
Only with a tool that works locally. Plotiq reads and processes your file in your browser; it is never uploaded to a server.

Keep reading

Written and reviewed by the Plotiq Editorial Team. Spotted a mistake? Tell us and we will correct it. See About Plotiq for how these guides are made.