How to Import a CSV Into Excel With Power Query Reliably

Double-clicking a CSV in Excel is a gamble with your data, and the import route is the safer bet: it lets you say what the file is instead of letting Excel decide. The part most people miss is that the import is not a one-off dialog. It builds a query, a list of steps Excel runs again every time, which is why Power Query is the right tool for a file that arrives weekly. You configure the encoding, the delimiter and the column types once, and next week's file goes through the same steps. This article builds that query by hand, shows the code behind it, and covers the places where the defaults will hurt you. The wider set of traps is in opening CSV files in Excel and Sheets without damage.

The route through the menus

Open a blank workbook and choose Data > Get Data > From File > From Text/CSV, or the shorter Data > From Text/CSV. Select the file. Excel shows a preview with three controls at the top.

File origin is the encoding. A file exported from a modern system is 65001: Unicode (UTF-8), and an older Windows export may be 1252: Western European (Windows). If the preview shows Café, you picked the wrong one; change it and the preview redraws. Delimiter is Comma, Semicolon, Tab, Space or a custom character, and Excel's first guess is right most of the time. Data type detection has three settings: based on the first 200 rows, based on the entire dataset, or do not detect. The default is the first of these.

The two buttons at the bottom are Load and Transform Data. Load puts the table on a sheet immediately, with the guesses applied. Transform Data opens the Power Query editor first. Use Transform Data every time, because Load is the same gamble as double-clicking with a nicer dialog.

What the editor wrote for you

In the editor, the right-hand Applied Steps pane lists what ran. A typical import has four: Source, Promoted Headers, Changed Type, and sometimes a renamed column. Open View > Advanced Editor to see the code:

let
    Source = Csv.Document(
        File.Contents("C:\data\orders.csv"),
        [Delimiter = ",", Columns = 4, Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Typed = Table.TransformColumnTypes(
        Promoted,
        {{"order_id", type text}, {"placed_on", type date}, {"total", type number}},
        "en-US"
    )
in
    Typed

Each argument corresponds to a choice in the dialog. Encoding = 65001 is UTF-8. QuoteStyle.Csv tells the parser to honor double quotes the way RFC 4180 defines them, so a comma inside quotes stays in its field; the alternative QuoteStyle.None ignores quotes and splits on every delimiter. The Columns number is a hint for how many columns to read, and a file whose rows have a different number of fields produces nulls or extra columns rather than an error, so watch the preview when a feed changes. The final step passes a culture, "en-US", which is how the query knows that 1,234.50 is a number and 04/03/2024 is April 3 and not March 4.

Excel inserts its own Changed Type step right after the header promotion, and you should read it before trusting it. It is the equivalent of the type guessing you turned off in the dialog, written as code. Delete it and write your own, as above.

Choose the types yourself

The type step is where the damage is prevented. The rule of thumb is simple: a column you will add up or average is a number; everything else is text, including zip codes, phone numbers, account numbers and anything with leading zeros. Dates are the exception that deserves care, because a date type in Power Query needs the right culture to read the text.

Set a type by clicking the icon in the column header, and pick Using Locale to name the culture. A European export with 1.234,50 and 04.03.2024 becomes correct data when you choose Decimal Number with the German (Germany) locale and Date with the same locale. The mistake to avoid is converting a text column to a number by clicking the icon without a locale, because the query then uses the machine's own, and the same workbook gives different results on a colleague's laptop.

Why not trust the first 200 rows? A code column that holds 10023, 10024 and so on for the first 3,000 rows and then reaches 10A25 will be typed as a whole number, and the cell that does not convert becomes an error, or in some configurations a null, in the rows below. The error shows as Error in the cell and a count in the status bar. Check it by looking at the column quality indicators under View > Column quality and Column profile, with the profiling set to the whole dataset and not the top thousand rows. A column marked 100 percent valid is what you want to see.

Reload next week's file

Once the query is saved you rarely reopen the editor. Replace the source file with the new one at the same path and press Data > Refresh All. The query reads the new rows, applies the same steps and fills the table. If a column is renamed by the sender, the step that refers to the old name fails with The column 'total' of the table wasn't found, which is a feature: it tells you the feed changed instead of silently loading nothing, and it matches the problem in handling a CSV provider who changes columns without warning.

Keep the path out of the code. A hard-coded C:\Users\you\Downloads\orders.csv breaks the moment someone else opens the workbook. Two options hold up: a parameter (Home > Manage Parameters) that you reference in File.Contents(FilePath), or a one-cell table on a sheet, read by the query and edited by the user. For a folder of files that grow, use Data > Get Data > From File > From Folder, which lists the files and lets you combine them, header handling included, as described in merging several CSV files with the same header.

Where the data lands

At the end, Close & Load To offers a table on a sheet, a PivotTable, or Only Create Connection. A connection loads the rows into Excel's Data Model without writing them to a sheet, which is how you analyze a few million rows when a sheet could only hold 1,048,576. You cannot edit the rows, and you can build PivotTables and Power Pivot measures on top. If the table does land on a sheet, remember that editing a cell in it changes nothing in the source file, and the next refresh overwrites your edits.

A few habits make the table behave. Name the query something other than orders, since the default is the file name and a second import of a different file with the same name creates orders (2). Turn off Include row numbers if you never use them. Keep the identifier columns Text in the final step, because a later Change Type on the whole table silently converts them again, which is the most common way a good query goes bad six months later.

When Power Query is more than you need

For a file you will open once, a table of a few hundred rows, and no leading-zero columns, the import wizard is enough and the query is overhead. Use Data > From Text/CSV, press Load, glance at the result and move on. That is still better than double-clicking, since it shows the preview before the change.

It is also not the answer when the file is too big for a person to review. At a few hundred megabytes, DuckDB or a script reads the file, applies types and writes a clean result faster and with less ceremony; querying a CSV file with DuckDB instead of loading it shows the shape. And for a pipeline that no human opens, Power Query is the wrong place for logic that belongs in the loader. If the data has to be correct for a database, validate it there (validating a CSV file).

If the file arrives with a semicolon, see opening a semicolon separated CSV in Excel for the delimiter and locale details. Microsoft documents the dialog in import or export text (.txt or .csv) files.