How do I open a CSV in Excel without it changing anything?
Stop double-clicking: import the file with Power Query, choosing the encoding, the delimiter and which columns are text yourself.
Free course. Your Maestro, an AI, .
In the earlier modules you saw each problem on its own and fixed it in Python. But at the office a lot of people will keep opening files in Excel, and so will you. This page is about doing it without letting Excel decide for you.
Start with what a double click does. Microsoft describes it this way: when Excel opens a .csv file, it "uses the current default data format settings to interpret how to import each column of data". In other words, Excel opens the file with its assumptions. It assumes an encoding, and with no BOM the accents of a UTF-8 file can show up as Ã. It assumes a separator, and if that isn't the file's (a semicolon file from Latin America or Europe on a computer with US settings, say), the columns split in the wrong places or everything ends up in one. It assumes that what looks like a number is a number and strips its zeros, and that what looks like a date is a date, in the order it has configured.
There's good news on the same help page: "Opening a text file in Excel does not change the format of the file". As long as you only look, the CSV stays intact on your disk. What does change the file is saving: what gets saved is what Excel already interpreted. That's why the golden rule of this module is: if a CSV looks odd in Excel, close it without saving.
The alternative is to import the file instead of opening it. Excel has a tool for that, Power Query (Microsoft also calls it Get & Transform), which shows you a preview and lets you decide before loading the data. When you import, you decide four things, the same four questions you already ask yourself in pandas, and only then do you load:
The encoding
In the preview, the File Origin list says which table to read the bytes with. If you see
éwhereébelongs, change it.The delimiter
The Delimiter list: comma, semicolon, tab or other. If everything is in one column, this is the problem.
Data type detection
The last list decides how it guesses types: from the first 200 rows, from the entire data set, or without guessing, leaving everything as text.
The columns that are codes
In the Power Query Editor, columns like
codigo_postal(postal code) orfolio(invoice number) are changed to the Text type before loading.Load
Only when the preview looks right do you load the data into the sheet.
The exact path differs between Windows and Mac. These are the steps Microsoft gives in its help:
- On the Data tab, in the Get & Transform Data group, click From Text/CSV.
- In the Import Data dialog box, locate the file, double-click it and click Import.
- In the preview, check File Origin and Delimiter.
- Choose Transform Data to open the Power Query Editor (in some versions the button says Edit).
- Select the code column by clicking its header (Ctrl+click selects several) and go to Home > Transform > Data Type > Text. In the Change Column Type dialog, choose Replace current.
- Select Close & Load.
If you don't see Get Data in your Excel, Microsoft gives another path: New Query > From File > From Text.
- Select Data > Get Data (Power Query).
- In the Choose data source dialog, choose Text/CSV and select the file.
- Review the preview. To change types, choose Transform Data: the Power Query Editor opens.
- Change the code columns to Text. Microsoft says that on Mac you shape the data in the editor the way you would in Excel for Windows.
- Select Home > Close & Load.
According to Microsoft, the Power Query Editor on Mac is available to Microsoft 365 subscribers with Excel for Mac version 16.69 or later. Another path it documents: Data > Get & Transform Data > Get Data > From File > From Text/CSV > Import.
The encoding, carefully
Of the four decisions, File Origin is the one people overlook most, because the list almost always comes with something already chosen. But Microsoft documents that Power Query, in that list, doesn't infer the character set, and "UTF-8 is only inferred if it starts with a UTF-8 BOM." Put differently: if your file is UTF-8 without a BOM, or if it comes from the old system in Windows-1252, Power Query won't guess it. You choose it, looking at the preview: when the names look right, it's the correct one. It's the same method you used in Python in module 1.
If accents still look odd in the preview, tell your Maestro what you see.
The old wizard
Maybe you know the Text Import Wizard, the three-step one. Microsoft now treats it as a legacy feature, kept for compatibility, and recommends Power Query instead. If you prefer it, you turn it on under File > Options > Data > Show legacy data import wizards > From Text (Legacy). It has the same decisions in a different order: step 1 has its own File origin; step 2, the Delimiters; and step 3, an Advanced button to tell it the file's decimal and thousands separators, plus a Column data format where you choose Text for codes or the order of dates. Microsoft also documents a shortcut: "To force Excel to run the Import Text Wizard, you can change the file name extension from .csv to .txt before you open it."
Ask your Maestro what it would choose at each step for the Valencia price list.
Warning: A regional change isn't the fix
On forums you'll see someone suggest changing the list separator in Windows' regional settings so that semicolon files open. Microsoft warns that "Changing the Windows setting will cause a global change on your computer, affecting all applications." To read a file, import it and tell it the file's delimiter.