- How CSV differs from an Excel file
- Why text turns into garbage
- Why leading zeros and long numbers change
- How to open a CSV without breaking it
- What to agree before exchanging files
The short answer
A CSV file is a table written as plain text and nothing else. With no colours and no formulas, almost any program or system can read and write it. The catch is that it also carries no information such as "treat this column as text" or "read this with that encoding", so the program opening it has to guess — and when the guess is wrong, the data breaks.
Most breakage is not caused by CSV itself but by one habit: double-clicking the file to open it in Excel, then saving over it. Keep the original, tell the program how to read the file, and agree the format with whoever you exchange files with. Those three habits prevent most accidents.
What CSV is
CSV stands for comma-separated values: a text file where values are separated by commas. Opened in a plain text editor, it looks like this:
- EmployeeID,Name,StartDate,PostCode
- 00123,Hanako Yamada,2024/04/01,0600001
- 00456,Taro Sato,2025/10/01,1000001
Each line is one row of the table, and each comma-separated value is one cell. The first line usually holds column names, but that is a convention, not a rule.
CSV is most often used to pass data between systems or between companies: from an attendance system to payroll software, from an online shop's order list to accounting software, from one customer list to another service. Programs from different vendors can usually all read and write CSV, which is why it serves as a common exchange format.
How it differs from an Excel file
An Excel workbook (.xlsx) and a CSV file can look identical on screen, but they are very different underneath.
| What to look at | Excel workbook (.xlsx) | CSV |
|---|---|---|
| Contents | Values, formatting, formulas and more | Text only |
| Colours, borders, column widths | Saved | Not saved |
| Formulas | Kept as formulas | Only the calculated values remain |
| Sheets | Several | One |
| "This column is text" | Can be recorded | Cannot be recorded |
| What can open it | Excel and compatible software | Almost anything |
Colour a CSV in Excel and save it, and the colours are gone next time you open it, because CSV can only hold text. A useful way to think about it: Excel workbooks are for working in; CSV files are for handing over.
Why CSV breaks so easily
Encodings and garbled text
Computers store each character as a number. The rule that decides which character gets which number is called a character encoding. In Japan, two encodings come up again and again with CSV files:
- UTF-8: an encoding that covers characters from every writing system, used by most modern systems and web services.
- Shift_JIS: an older encoding designed for Japanese, still common in long-established business software and systems built for the Japanese market.
Read a file with a different encoding from the one it was written in and you get strings of meaningless symbols — in Japanese this is called mojibake. The data is not damaged; it is being read the wrong way, and reopening it with the correct encoding restores it. Save over it while it is garbled, though, and the damage becomes real. Some characters also do not exist in Shift_JIS, so converting from UTF-8 can turn certain name characters or symbols into "?" or similar.
Which encoding Excel assumes when you double-click a CSV depends on the Excel version, its settings and how the file was created. That is why a file can open fine on your computer and turn into mojibake on someone else's.
Delimiters
Because CSV uses commas as separators, a comma inside a value causes trouble — "1,200", for example, or "Company A, Sales". The usual convention is to wrap such values in double quotes ("). If quoting is inconsistent, one value splits into two columns and everything to its right shifts along by one.
Line breaks inside a value cause the same problem. An address or notes field containing a line break can make one record look like two. There are also tab-separated files (TSV), and in some countries semicolons (;) are used instead of commas. A file ending in .csv may use a different separator inside.
Leading zeros disappear
Excel may decide that a value made only of digits is a number. As a number, a leading zero means nothing, so it is dropped.
- Employee ID "00123" becomes "123"
- Japanese postcode "0600001" becomes "600001"
- Mobile number "09012345678" becomes "9012345678"
This is not just a display issue. Save the file as CSV and the zeros are gone from the file too. Import it into payroll or a delivery system and the record matches the wrong person, or nobody.
Dates and long numbers change by themselves
The same guessing can cause other changes:
- Part numbers such as "1-2" or "3/4" turn into dates (2 January, 4 March, depending on locale).
- "2026/09/26" is displayed as "2026/9/26", and saving writes it back in that form.
- Long numbers such as card numbers or long invoice numbers appear in scientific notation, like "1.23E+15", or have their final digits replaced with zeros.
The last change cannot be undone. Unless the original file still exists, the correct number is lost.
Opening CSV files safely
Menu names and locations differ between Excel versions, so this section describes the approach rather than exact clicks. For the steps in your version, check Microsoft's official help.
- Copy the file before opening it. Keep the CSV you received as the original and work on a copy.
- Do not double-click it. Excel has a feature for importing text files such as CSV, which lets you specify the encoding, the delimiter and the data type of each column. Set columns such as employee IDs, postcodes and part numbers to text.
- To just look inside, use a text editor. That tells you whether the problem is the encoding or delimiter, or Excel's conversion.
- Never save over a CSV you opened in Excel. Save under a new name, and check which encoding you saved it in.
- Check the row count. Compare the number of rows before and after import. If line breaks or delimiters have split rows, the counts will differ.
Agreements for exchanging CSV files
When exchanging CSV files with a client or another department, settling these points up front saves repeated fixes every month.
- Encoding (UTF-8 or Shift_JIS)
- Delimiter (comma or tab), and whether values are wrapped in double quotes
- Whether the first line is a header, and the column order
- Date format (2026/09/26 or 2026-09-26)
- Which columns have leading zeros (employee IDs, postcodes and so on)
- A sample file, tested by actually importing it
Picture a company of about twenty that imports a CSV from its attendance system into payroll software every month. One month, someone opens the CSV in Excel just to check it, saves, then imports it. Leading zeros vanish from the employee IDs and several people's records fail to import. One line in the procedure — "if you need to look before importing, open a copy" — prevents it.
A CSV file is often a sign that the same data is being handed over manually again and again. If the same exchange happens every month, connecting the systems directly through an API is another option.
In short
CSV is a plain-text table for handing data over. Its simplicity means it carries no encoding or column-type information, so it breaks when the opening program guesses wrong. Garbled text is a reading problem and can be fixed, but lost leading zeros and altered long numbers cannot be recovered once saved. Keep the original, import with the format specified, and agree the format with the people you exchange files with — and write those three habits into the procedure.