Stop Opening CSV Files in Excel — Try This Instead

It’s 3:12 PM. You just got an email from logistics with orders_export_20240618.csv. You double-click it—and Excel opens with all columns smashed into column A: "ID","Customer","Order Date","Amount" on row 1, then "1082","Luis Mendoza","2024-06-15","$2,495.00" on row 2. No formatting. No dates recognized. No way to filter. Your report is due in 47 minutes.

Quick Answer

Don’t double-click the CSV file. Instead, open Excel first, go to Data → Get Data → From Text/CSV (Alt+A → T → T), select the file, and use the preview window to set delimiter, encoding, and column types *before* loading. This prevents text smearing, date misreads, and number truncation—especially for Spanish-language CSVs with commas as decimals.

All the Methods

Method	Steps	Best For	Limitations
Double-click CSV	None—just click the file	Urgent one-off glances (not analysis)	Fails on semicolon-delimited files, ignores UTF-8 BOM, treats "1,5" as text not 1.5 (common in Spain/Mexico)
Data tab → From Text/CSV	Alt+A → T → T → browse → preview → load	All production work—clean imports, consistent formatting, repeatable queries	Requires Excel 2016+; won’t auto-refresh if source file changes
Power Query Editor (advanced)	Import → Edit → split columns → change types → add custom column → Close & Load	Large files (>100k rows), multi-step cleaning, scheduled refreshes	Steeper learning curve; overkill for simple lists
Text Import Wizard (legacy)	Data tab → From Text (Legacy) → choose Delimited → check Comma → Finish	Older Excel versions (2013 or earlier); strict comma-only files	No UTF-8 support; can’t handle embedded quotes properly; no preview
Copy-paste from Notepad++	Open in Notepad++, select all, paste into Excel, then Data → Text to Columns	When Excel refuses to open the file at all (corrupted header)	Manual, error-prone; loses leading zeros (e.g., ID "00123") unless formatted as text first

Method 1 Deep Dive

This is the method you’ll use 90% of the time. It’s fast, visual, and handles edge cases like Spanish CSVs where 12,50 means twelve-and-a-half—not twelve thousand five hundred.

Here’s what your raw CSV actually looks like (saved as UTF-8 with BOM):

"ID","Cliente","Fecha Pedido","Monto"
"1082","Luis Mendoza","15/06/2024","1.250,50"
"1083","Sofía Ruiz","16/06/2024","890,00"
"1084","Acme Corp","17/06/2024","3.420,75"
"1085","María Gómez","18/06/2024","550,25"

Note the commas-as-decimals and DD/MM/YYYY dates. Double-clicking this in Excel gives you four columns crammed into A1, and “1.250,50” becomes text—not a number you can sum.

Do this instead:

1. Open a blank Excel workbook.
2. Go to the Data tab.
3. Press Alt+A → T → T. (Yes—three keys. Hold Alt, tap A, release, tap T, release, tap T.)
4. Browse to your CSV file and click Import.

You’ll land in the Power Query preview window. Here’s where most people rush and break things.

First, click the gear icon next to Source in the right-hand Applied Steps pane. In the pop-up, change Encoding from “Default” to UTF-8. This fixes accented characters like “Sofía” or “Gómez”.

Next, look at the preview. If your columns are still mashed together, click the Delimiters dropdown and try Semicolon or Comma. For Latin American CSVs, try Comma first—but if numbers like 1.250,50 show up wrong, switch to Semicolon and manually promote headers.

Now highlight the Fecha Pedido column. Click Transform → Date → Using Locale…, choose Spanish (Mexico) and DD/MM/YYYY. That converts “15/06/2024” into a real Excel date (serial number 45457) you can sort and filter.

For Monto, right-click the column header → Change Type → Decimal Number. Excel will warn that some values couldn’t be converted—click Replace Errors, then enter 0. Why? Because Power Query sees 1.250,50 as text with a dot thousands separator and comma decimal. You need to tell it: “Treat dots as thousands separators, commas as decimals.” So instead, go to Transform → Format → Replace Values, find ., replace with nothing, then change type to Decimal.

Click Close & Load. Your data lands cleanly in Sheet1, starting at A1. Dates are sortable. Amounts are numeric. IDs retain leading zeros if formatted as Text beforehand (more on that below).

Surprising tip: If your CSV has a header row with spaces or special characters (e.g., "Order #"), Power Query auto-replaces them with underscores (Order_). To keep spaces, go to Home → Advanced Editor, find the line Table.TransformColumnNames(#"Previous Step", Splitter.SplitByNothing()), and delete the entire Table.TransformColumnNames step. Then click Done.

Method 2 Deep Dive

Sometimes you get a CSV that’s 237,000 rows, includes duplicate order IDs, and needs a running total by region. That’s when you go full Power Query.

Use the same Alt+A → T → T path, but after previewing, click Transform Data instead of Load. You’re now in the Power Query Editor.

Let’s say your sample data looks like this (from sales_q2_spain.csv):

ID	Producto	Región	Unidades	Precio Unitario	Fecha
SP-00123	Monitor LED 24"	Madrid	12	189,95	2024-04-02
SP-00124	Teclado Mecánico	Barcelona	8	125,50	2024-04-03
SP-00125	Ratón Gaming	Valencia	15	78,25	2024-04-05
SP-00126	Cargador USB-C	Madrid	22	42,90	2024-04-07
SP-00127	Auriculares Bluetooth	Sevilla	6	89,99	2024-04-10

Step-by-step:

• Select the ID column → Transform → Data Type → Text (keeps leading zeros like SP-00123)
• Select Precio Unitario → Transform → Replace Values: find ., replace with nothing; then Change Type → Decimal Number
• Select Fecha → Transform → Date → Date (auto-detects YYYY-MM-DD)
• Right-click Región → Group By → New column name: Total Ventas, Operation: Sum, Column: Unidades × Precio Unitario (use Advanced option and enter formula: List.Sum(Table.AddColumn(_, "Total", each [Unidades] * [Precio_Unitario])[Total]))

That last step creates a grouped summary table. Click Close & Load To… → choose Only Create Connection and place it in a new worksheet. Now you’ve built a live summary that updates when you hit Data → Refresh All.

Cheat Sheet

Action	What to Do	Shortcut	Pro Tip
Open CSV safely	Excel → Data tab → From Text/CSV	Alt+A → T → T	Always check Encoding first—UTF-8 fixes “Sofía”, “café”, “niño”
Fix comma decimals (Spain/Mexico)	Replace dots → blank, then change type to Decimal	Transform → Format → Replace Values	Never use “Detect data type” — it guesses wrong on “1.250,50”
Preserve leading zeros in IDs	Before loading, select column → Change Type → Text	Right-click column → Change Type → Text	If already loaded, use =TEXT(A2,"00000") — but better to fix upstream
Refresh imported data	Right-click any cell in the table → Refresh	Alt+F5	Set automatic refresh: Table Design → Properties → Refresh every X minutes
Split messy combined columns	Select column → Transform → Split Column → By Delimiter	Ctrl+Alt+T → S	Use “Each occurrence of the delimiter” for addresses or names with commas