How to open a CSV in Excel correctly: UTF-8, zeros and dates

Excel opens a double-clicked CSV without asking about encoding, delimiter or column types. It guesses, and saving the file writes the damaged values back out.

Excel only assumes UTF-8 when a CSV starts with a UTF-8 byte order mark (BOM), and many tools, rows.page included, export UTF-8 without one. Excel also turns anything that looks numeric into a number: leading zeros vanish, digits past the 15th become zeros, and codes with slashes or hyphens turn into dates.

Skip the double-click and import the file instead. This works for any CSV, including rows.page exports, which are UTF-8 without a BOM.

1. In a blank workbook, choose Data > Get Data > From File > From Text/CSV (some ribbons show Data > From Text/CSV directly).
2. Pick the .csv file and open it.
3. In the preview, set File Origin to 65001: Unicode (UTF-8) and check that accented characters look right.
4. Check the Delimiter. Excel detects it, but you can pick Comma, Semicolon, Tab or another character.
5. To keep leading zeros, long IDs and date-like codes, set Data Type Detection to Do not detect data types, which loads every column as Text.
6. To change only some columns, select Transform Data instead, click the type icon in each ID, ZIP or code column header and choose Text. If Excel asks, choose Replace current so the original text is kept.
7. Select Load (Close & Load in the editor), then save the workbook as Excel Workbook (.xlsx) with File > Save As.

Saving as .xlsx matters: a .csv stores plain text only, so the column types you set are lost if you save back to CSV.

An Excel sheet holds 1,048,576 rows by 16,384 columns. For a bigger file, or one you want to clean first, open it in rows.page: it's free, needs no account and reads the file inside your browser with DuckDB-WASM.

CSV and TSV files up to 1.5 GB open in full, and a bigger file loads partially with the note "Loaded the first N rows: the file is X, more than the browser can hold." The sniffer detects comma, semicolon, tab and pipe delimiters and the header row. A file that isn't UTF-8 is read as Latin-1, unless it holds Windows-only characters such as the euro sign or curly quotes, in which case it doesn't open.

Narrow the rows with the filter box (country:NL, price:>20, or -name:* for empty values) or by clicking a histogram bar in the Columns panel. To aggregate, type SQL into the same box; the table is called data:

SELECT country, status, count(*) AS orders, sum(total) AS revenue
FROM data
WHERE status != 'cancelled'
GROUP BY country, status
ORDER BY revenue DESC

A SQL result shows and exports up to 1,000,000 rows, which fits in one sheet, while a filtered view exports every matching row. To keep every row inside Excel instead, choose Load To in the From Text/CSV dialog, pick Only Create Connection and tick Add this data to the Data Model.

rows.page doesn't write .xlsx files. It exports your filtered view or SQL result as a CSV that Excel imports cleanly:

• Comma-delimited with a header row.
• UTF-8 without a BOM, so use the File Origin step above; there is no option for another delimiter, encoding or a BOM.
• Text cells that start with =, +, -, @, a tab or a carriage return get a leading apostrophe so Excel doesn't run them as formulas.
• Filtered views export every matching row; SQL results stop at 1,000,000 rows.

To hand over the data without a spreadsheet, press Share ("Upload and get a link") or push the file from a terminal. One push is capped at 100 MB, and the link expires after 7 days unless you press Keep it on the dataset page.

curl -T data.csv https://rows.page

To produce an .xlsx file in a script, DuckDB 1.2 and later writes one with its excel extension. This keeps a long order ID as text so Excel can't round it:

INSTALL excel;
LOAD excel;
COPY (
  SELECT * REPLACE (CAST(order_id AS VARCHAR) AS order_id)
  FROM read_csv('data.csv')
) TO 'data.xlsx' (FORMAT xlsx, HEADER true);

DuckDB keeps a ZIP code such as 02134 as text on its own, and it refuses to write more than 1,048,576 rows to a sheet, header row included. In pandas (pip install pandas openpyxl), pass dtype for the columns that must stay text:

import pandas as pd

df = pd.read_csv('data.csv', dtype={'zip': str, 'order_id': str})
df.to_excel('data.xlsx', index=False)