Skip to content
Tutorials Center
My learning

Topics

Lesson 67 of 93 Β· Data Cleaning and Import

Importing Data from CSV, Text and the Web

Learn to import CSV and text files into Excel safely: delimiters, encodings, locale and date pitfalls, leading zeros, web and folder imports, with pandas.

Most data does not start life in Excel. It arrives as a CSV export from a shop, a text file from an old system, a table on a web page or a folder of monthly files. If you simply double-click a CSV, Excel guesses how to read it, and its guesses are the source of many famous spreadsheet disasters: ZIP codes that lose their leading zero, product codes turned into dates, accented letters turned into gibberish.

This lesson explains how to import on purpose. Excel cannot run on this site, so the menu steps are text. The live examples use Python's csv module and pandas read_csv as stand-ins: they have the same decisions to make (delimiter, encoding, column types), and showing them in code makes those decisions visible. Excel's own importer is a different program.

Open or import?

Open Quick, but Excel guesses.
Import You choose each setting.
Query Refreshable, repeatable.
1 / 2

A CSV file is just text

A CSV (comma-separated values) file is plain text. Each line is a record and a separator splits the fields:

TEXT
id,name,city,amount
007,Ana Souza,Lisbon,"1,250.50"
012,JΓΆrg KΓΌhn,KΓΆln,980

Notice the quotes: a field that contains the separator (1,250.50) must be wrapped in quotes. There is no formatting, no formulas and no data types in the file. Everything that looks like a number, date or ZIP code is only a guess made by whatever program reads it.

Open versus import

Opening a .csv file (File > Open, or double-click) loads it right away with Excel's defaults: the separator from your Windows or Mac regional settings, automatic types for every column. It is fast, and fine for clean, simple files.

Importing lets you decide. In current Excel use Data > From Text/CSV. A preview window shows the file with three dropdowns: File Origin (the encoding), Delimiter and Data Type Detection (first 200 rows, the entire data set, or none). Press Transform Data to continue in Power Query (see Power Query: Get and Transform), or Load to put the table on a sheet. Choosing "Do not detect data types" keeps everything as text, which is the safest start; you then convert the columns you want.

Older Excel versions had the Text Import Wizard; it can still be enabled under File > Options > Data > Show legacy data import wizards > From Text (Legacy). It asks the same questions in three steps, and its third step lets you set each column to General, Text, Date or "Do not import".

Delimiters

Not every "CSV" uses commas. Many European exports use a semicolon, because the comma is their decimal mark, and others use tabs (.tsv or .txt), pipes | or fixed-width columns. Excel's default for opening a .csv follows your system's list separator, so the same file may open in one column on one computer and split correctly on another. When everything lands in column A, the delimiter is wrong: re-import and choose the correct one, or use Data > Text to Columns on the selected column (see Joining and Splitting Text).

Python Same data, different delimiters

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Encodings and strange characters

Text files are stored as bytes, and an encoding says which bytes mean which letters. UTF-8 is the modern standard and handles every language. Older files, especially from Windows tools, may use Windows-1252 (Western European) or other code pages. If you read bytes with the wrong encoding you get "mojibake": JΓΆrg KΓΌhn becomes Jârg Kühn.

In Excel, change File Origin in the import preview until the names look right (65001 is UTF-8; 1252 is Windows Western European). When saving data from Excel for other systems, choose CSV UTF-8 (Comma delimited) so accents survive. Some programs need a BOM (a hidden marker at the start) to recognize UTF-8; Excel's "CSV UTF-8" option writes one, and plain "CSV (Comma delimited)" uses the system's legacy code page instead and silently turns characters it cannot represent into question marks.

Python Wrong encoding makes mojibake

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Leading zeros and other lost digits

Excel turns 007 into 7 because it is "obviously" a number. It does the same with long numbers such as 16-digit card numbers (it stores only about 15 significant digits and turns the rest to zeros), and with gene names like SEPT2 or codes such as 1E5 (read as scientific notation). The cure is to make those columns Text at import time. Once Excel has converted 007 to the number 7 and saved the file, the zeros are gone for good: the information was never stored.

How to protect such columns:

  • In the Text/CSV preview, choose Do not detect data types, or in Power Query change the column type to Text (the zeros survive because the conversion happens on the raw text).
  • In the legacy wizard, select the column in step 3 and choose Text.
  • For data you type yourself, format cells as Text before typing, or start with an apostrophe ('007).
  • If zeros are already lost and the length is fixed, rebuild them with =TEXT(A2,"00000"); see TEXT, VALUE, NUMBERVALUE and Number-as-Text Problems.
Python Column types decide whether zeros survive

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Locale and date pitfalls

A locale is a set of regional rules: the decimal mark, thousands separator and date order. The text 1.250,50 is one thousand two hundred fifty and a half in Germany, but in the US it is not a valid number at all. The text 03/04/2025 is March 4 in the US and April 3 in most of the world. Excel's importer applies your computer's locale unless you override it.

What goes wrong:

  • A date like 13/04/2025 on a US system fails to convert and stays text, while 03/04/2025 silently converts to the wrong date. Wrong dates that look valid are the worst kind of bug.
  • Numbers with the "wrong" decimal mark may stay as text (left-aligned, with a green triangle) or become something huge.
  • Dates before 1900 or in an odd format are kept as text.

How to avoid it: in Power Query use Change Type > Using Locale and pick the data type with the locale of the source file (for example, German for 1.250,50). In the preview you can also set Locale for the legacy wizard's Advanced button (decimal and thousands separators). Better still, ask data providers to deliver dates as ISO 8601 (2025-04-03), which is unambiguous.

Python The same text, two meanings

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Importing from other sources

The Data > Get Data menu and the From Web button load more than files:

  • From Web: paste a URL. Excel finds HTML tables and lists them in a navigator; choose one and load it. It reads public pages only, and the page layout may change and break your query. Check the site's terms before you import its data.
  • From Folder: point at a folder and combine all files with the same layout (for instance twelve monthly CSVs) into one table. When you add next month's file, press Refresh and it appears.
  • From Excel Workbook, From JSON, From XML, From PDF and database connectors work in the same way.

All of these create queries: recorded steps that you can replay with Data > Refresh All. That is the biggest difference from simply opening a file, because cleaning steps (change types, trim, remove rows) run again on the new data without you redoing them. If you plan to import the same kind of file more than once, use a query. When a source file moves, update the path in Data > Queries & Connections or the query's Source step.

Databases and SQL statements

Data > Get Data > From Database connects to SQL Server, MySQL, PostgreSQL, Oracle, Access and many more (you need the server address, a database name and an account; the connection may ask you to install a driver). In the connection dialog, Advanced options has a SQL statement box. Typing SELECT region, SUM(amount) AS total FROM sales WHERE year = 2025 GROUP BY region there makes the database do the filtering and grouping, so only the result travels to Excel. That matters when the table has millions of rows: Excel's limit is 1,048,576 rows per sheet, and a query that returns a summary is also faster. For small tables, load the whole table and let Power Query's own steps shape it; Power Query tries to fold those steps back into SQL automatically.

Older workbooks use Microsoft Query (Data > Get Data > From Other Sources > From Microsoft Query) or ODBC drivers; they work the same way and show a "connection" under Data > Queries & Connections. You can also run SQL over Excel's own data through VBA with ADO (the ACE.OLEDB provider), or with add-ins and Python tools; those are outside this lesson, and Microsoft 365 users can instead use FILTER, GROUPBY and PivotTables for the same job (Filter, Sort and Unique).

Put database passwords only in the connection dialog, never in a worksheet cell. Credentials stored in a workbook can be read by anyone who has the file.

Sanity checks after every import

  1. Compare the row count with the source (the status bar shows it when you select a column).
  2. Check that totals of key numeric columns match the source system.
  3. Look for numbers left-aligned (text) and dates that did not convert.
  4. Scan names and codes for broken characters and lost leading zeros.
  5. Keep the raw file unchanged and work on the imported copy.

Then continue with Cleaning Messy Data Step by Step.

Check your understanding

Quick check Β· 5 questions

of 5 answered You scored / 5 Β· perfect!

  1. 1. A CSV opens with all its data crammed into column A. What is the most likely cause?

    Excel splits on its regional list separator. If the file uses semicolons or tabs, choose the right delimiter when importing or use Text to Columns.
  2. 2. How do you keep the leading zeros in ZIP codes when importing?

    Zeros are lost at conversion to a number. Setting the column type to Text during import keeps the original characters.
  3. 3. What does choosing the wrong File Origin (encoding) usually cause?

    The same bytes mean different characters in different encodings, so UTF-8 data read as Windows-1252 turns JΓΆrg into Jârg.
  4. 4. Why is the text 03/04/2025 risky to import?

    Day-first and month-first formats look the same when the day is 12 or less. Importing with the right locale, or using ISO 8601 dates, avoids silent errors.
  5. 5. What is the main advantage of importing with a query instead of just opening the file?

    A query stores its steps, so Refresh All repeats the import and clean-up on updated files.

Summary

  • A CSV is plain text; every type (number, date, ZIP) is a guess by the reader.
  • Open is quick but guesses; Data > From Text/CSV lets you set encoding, delimiter and type detection.
  • Fix wrong delimiters at import (or with Text to Columns) and wrong encodings with File Origin; save as CSV UTF-8 when accents matter.
  • Import identifiers and codes as Text so leading zeros survive; lost zeros cannot be recovered from the number.
  • Locale decides decimal marks and day/month order; prefer ISO dates and check results after every import.
  • Queries from files, folders and the web can be refreshed, which beats repeating manual steps.

Next: Cleaning Messy Data Step by Step.