9. Encodings and Delimiters When Importing CSV

A CSV file is just text, and text in a file is stored as bytes. The encoding says which letter each byte stands for. Two encodings are common in the West:

• UTF-8 (code page 65001) is the modern standard: websites, banks, exports from cloud services;
• Windows-1252 (Western European, code page 1252) is older software and many legacy exports.

If you open a file in the wrong encoding, you get "mojibake" instead of letters: UTF-8 read as 1252 turns "café" into café, and 1252 read as UTF-8 turns it into diamonds with question marks. A formula cannot fix this: you have to import the file again with the right encoding.

The right way to import: do not double-click the CSV. Use "Data › From Text/CSV". In the preview window, choose:

• File Origin: "65001: Unicode (UTF-8)" or "1252: Western European (Windows)". Switch until the text is readable;
• Delimiter: semicolon, comma or tab. In countries where the comma is the decimal separator, CSV files usually use semicolons;
• Data Type Detection: codes like "007" are better kept as text (in Transform Data), otherwise Excel strips the leading zeros.

When you save a CSV for other programs, choose "CSV UTF-8 (Comma delimited)" so accented letters survive. Excel writes a BOM mark at the start of such a file, and other programs use it to recognize UTF-8.

More common surprises: numbers like "1.250,75" (the period is thousands, the comma is the decimal part) or "1,250.75"; non-breaking spaces between thousands (in Excel, SUBSTITUTE(A2,CHAR(160),"") removes them); dates as a string such as "20260315". NUMBERVALUE(text, decimal, group) helps with numbers (and ignores spaces), and so does DATE(LEFT…, MID…, RIGHT…) with dates.

The trainer has no import window, and no CHAR function either. Here the file has already been read in the right encoding but not split into columns: each CSV line sits in one cell of column A. The fields are: city; amount; date YYYYMMDD; branch code.