Import CSV files: delimiters, encodings and headers
Turn a delimited text file into a typed table you can trust.
~10 min read · beginner · Module 2: Importing and inspecting data
By Tahir Nas · Examples run in our simulator · Published · Updated
No prerequisites: you can start here.
A CSV file is just text. Power Query has to be told how to cut it into columns, and almost every CSV import follows the same three steps: read the text into columns, promote the header row, and set types.
Input file StockFile
SKU,Description,OnHand,UnitCost
H-01,"Hinge, brass",40,1.25
H-02,Bolt,250,0.10
let
Source = Csv.Document(StockFile, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted, {{"OnHand", Int64.Type}, {"UnitCost", type number}}, "en-US")
in
TypedResult
| SKU | Description | OnHand | UnitCost |
|---|---|---|---|
| H-01 | Hinge, brass | 40 | 1.25 |
| H-02 | Bolt | 250 | 0.1 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
"Hinge, brass" stays in one column because QuoteStyle.Csv respects quotes.Step 1: Csv.Document options
- Delimiter separates fields. Many European systems use
;because the comma is their decimal separator. A tab is written"#(tab)". - Encoding 65001 is UTF-8. A wrong encoding shows accented letters as garbage such as
é. - QuoteStyle.Csv lets quoted fields contain the delimiter or line breaks. QuoteStyle.None treats quotes as ordinary characters.
If everything lands in a single column called Column1, the delimiter is wrong. That's what happens when a semicolon file is read with the default comma:
Input file EuStock
SKU;OnHand
H-01;40
H-02;250
Csv.Document(EuStock)Result
| Column1 |
|---|
| SKU;OnHand |
| H-01;40 |
| H-02;250 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Fix the Source step (Delimiter = ";") rather than splitting Column1 afterwards. A later split can't tell a delimiter inside quotes from a real one.
Step 2: promote headers
Table.PromoteHeaders turns the first row into column names. Before that, every value is text in columns named Column1, Column2 and so on. If the file starts with a title or a run date, remove those rows first (next lesson).
Step 3: set types straight away
Everything read from a CSV is text, even 40. Set types right after promoting headers, with a culture when numbers or dates depend on it. Later steps then compare and calculate with real numbers and dates instead of text that happens to contain digits.
Where to find this in Excel
- Excel for Windows
- Data > Get Data > Text/CSV
- Excel for Mac
- Data > Get Data (Power Query) > Text/CSV
- What you see
- A preview lets you set File Origin, Delimiter and Data Type Detection before you choose Load or Transform Data. Excel for Windows also offers Text/CSV directly on the Data tab.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Where to get data (Microsoft Learn); Text/CSV connector (Microsoft Learn); Import and shape data in Excel for Mac (Power Query) (Microsoft Support).
Common mistakes
- Splitting Column1 by a delimiter instead of fixing the delimiter in the Source step.
- Leaving numbers as text, so
"100" < "20"sorts and compares as text. - Accepting an automatic Changed Type step that turned an ID column into a number and dropped its leading zeros.
Excel, Power BI and the simulator
- Excel: Data → From Text/CSV. Power BI Desktop: Get data → Text/CSV. Both show a preview dialog where you pick the delimiter and file origin (encoding) before editing.
- The dialog's Data Type Detection option decides whether an automatic Changed Type step is added; you can turn it off and type columns yourself.
- The simulator reads CSV fixtures with Csv.Document and supports the common encodings (UTF-8, Windows-1252, ASCII, UTF-16, Latin-1); other encodings raise UnsupportedFeature.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's free, with no account needed.
Practise: Import a semicolon-separated stock file (hardware)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- beginner~7 minFree
Import a semicolon-separated stock file (hardware)
Read a CSV that uses semicolons, promote headers and set number types.
- beginner~7 minPro
Import a semicolon-separated stock file (pharmacy)
Read a CSV that uses semicolons, promote headers and set number types.
- beginner~7 minPro
Import a semicolon-separated stock file (cafe)
Read a CSV that uses semicolons, promote headers and set number types.
- beginner~6 minFree
Import a CSV whose descriptions contain commas (hardware)
Some item descriptions contain commas and quote marks. Import the file so each description stays in one column.
- beginner~6 minPro
Import a CSV whose descriptions contain commas (pharmacy)
Some item descriptions contain commas and quote marks. Import the file so each description stays in one column.
- beginner~5 minFree
Turn a JSON list of accounts into a table (saas)
A JSON file holds a list of flat account records. Convert it to a table with one row per account.
- beginner~5 minPro
Turn a JSON list of accounts into a table (coworking)
A JSON file holds a list of flat account records. Convert it to a table with one row per account.
- beginner~6 minFree
Load the named table from an Excel workbook (kite shop)
The workbook has a notes sheet, a printed sales sheet and a proper Excel table. Load the table.
- beginner~6 minPro
Load the named table from an Excel workbook (bakery)
The workbook has a notes sheet, a printed sales sheet and a proper Excel table. Load the table.
- beginner~6 minFree
Import a CSV that has no header row (cafes)
The till system writes daily takings without a header line. Name the columns yourself and type them.
- beginner~6 minPro
Import a CSV that has no header row (kiosks)
The till system writes daily takings without a header line. Name the columns yourself and type them.
- advanced~12 minFree
Read a fixed-width text report (parts)
An old system writes each record as fixed-width text. Cut each line at the right positions and type the result.
- advanced~12 minPro
Read a fixed-width text report (library)
An old system writes each record as fixed-width text. Cut each line at the right positions and type the result.