How to Import Data into Excel: Complete Guide to CSV, TXT, Web, and Database Sources

Learning how to import data into Excel is one of the most valuable skills any analyst, accountant, or knowledge worker can master. Whether you are pulling sales figures from a CSV export, scraping a public website table, or connecting to a corporate SQL Server warehouse, Excel provides multiple pathways to bring external information into your workbook. The modern Get Data engine, built on Power Query, has replaced most of the legacy import wizards and offers transformation capabilities that rival dedicated ETL tools while remaining accessible to everyday users.

This guide walks through every major import method available in Microsoft Excel 365, Excel 2021, and Excel 2019. You will learn how to load text files with custom delimiters, refresh web queries automatically, parse JSON responses from APIs, and establish live connections to relational databases. We also cover the common pitfalls that frustrate new users, such as leading zeros being stripped from product codes or date columns being misinterpreted by regional locale settings.

Before we dive in, it helps to understand the philosophy behind Excel's data import architecture. Power Query records every transformation step you apply as a reusable script written in the M language. This means a one-time import becomes a repeatable pipeline you can refresh with a single click whenever the source file updates. Combined with features like bill and ted's excellent adventure cast style statistical analysis, imported data becomes immediately actionable for reporting.

Many users still rely on copy-paste workflows that introduce formatting errors, broken formulas, and inconsistent data types. Mastering proper import techniques eliminates these issues at the source. You will spend less time cleaning data manually and more time analyzing it. The investment of learning Power Query pays dividends within weeks for anyone who handles repetitive data refreshes, monthly reports, or consolidates information from multiple departments.

This tutorial assumes you are working with a desktop version of Excel on Windows, where the full Power Query ribbon is available. Mac users will find a reduced subset of features, and Excel for the Web supports basic CSV and text imports through OneDrive integration. We will note platform-specific limitations as they arise. For learners brushing up on broader spreadsheet competencies, resources like the institute of creative excellence and tools comparable to inner excellence book methodology help build foundational fluency alongside hands-on practice.

By the end of this article, you will be able to import structured data from at least eight distinct source types, automate refreshes on a schedule, and troubleshoot the most common encoding and locale issues. We will also touch on adjacent skills such as how to merge cells in excel cleanly after import, how to freeze a row in excel for navigation in large datasets, and how to remove duplicates excel introduces during multi-source consolidation.

Let's begin with the foundation: understanding where the import commands live in the Excel ribbon and how the modern Get Data experience differs from legacy methods you may have used in Excel 2010 or 2013.

CSV and TXT files remain the most common data exchange format in business computing. A comma-separated values file is plain text where each line represents a row and commas separate column values. Variations include semicolon delimiters (common in European locales), tab-separated values often saved with a .tsv or .txt extension, and pipe-delimited files used by legacy mainframe exports. Excel handles all of these through Power Query with minimal configuration once you understand the dialog.

To import a CSV using the modern method, click Data > Get Data > From File > From Text/CSV. Browse to your file and Excel opens a preview window showing the first 200 rows. The dialog displays three dropdowns at the top: File Origin (the character encoding), Delimiter, and Data Type Detection. The default encoding of 1252 Western European works for most US files, but international data often requires switching to UTF-8 to display accented characters correctly.

The Delimiter dropdown auto-detects the most likely separator but you can override it. The Data Type Detection setting determines how many rows Excel scans to guess column types. The default 200 rows is usually fine, but datasets with mixed content lower in the file may benefit from scanning the entire file. Click Load to bring the data in as a formatted table, or Transform Data to open the Power Query Editor for cleaning before loading.

One common headache involves leading zeros. Product codes like 00457 or zip codes like 02134 get converted to numbers by default, stripping the leading zeros and turning text into integers. To preserve them, click Transform Data, select the affected column header, and change the data type to Text using the dropdown above the column name. Power Query records this change as a step, so future refreshes will apply the same fix automatically without manual intervention.

Date columns present similar challenges, especially with international files. A date written as 03/04/2025 means March 4 in US English locale but April 3 in UK or European locale. Power Query uses your Windows regional settings by default. To force a specific interpretation, in the Power Query Editor select the date column, right-click the header, choose Change Type > Using Locale, and pick the correct source locale. This is far more reliable than letting Excel guess.

For fixed-width text files where columns are aligned by character position rather than delimiters, the legacy Text Import Wizard still has an edge. Enable it under File > Options > Data and check Show legacy wizards. Then use Data > Get Data (Legacy) > From Text. Step 2 of the wizard lets you click between columns in the preview to manually set break points. This is invaluable for older banking or scientific data exports that lack delimiters entirely.

When working with very large CSV files exceeding several hundred megabytes, consider loading them directly to the Data Model instead of a worksheet. In the Load dialog click Load To, then check Only Create Connection and Add this data to the Data Model. This loads the file into the in-memory Power Pivot engine, which can handle hundreds of millions of rows that would crash a normal worksheet. After import, you can apply tools like excel high school filtering patterns or build pivot tables against the model.

Connecting Excel to a database opens up enterprise-grade reporting capabilities directly within a familiar spreadsheet interface. Microsoft supports native connectors for SQL Server, Azure SQL Database, Oracle Database, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata, and SAP HANA, plus a generic ODBC connector that works with any database driver installed on your system. For most users, Data > Get Data > From Database is the starting point.

The SQL Server connection dialog asks for the server name (or IP address with optional port), an optional database name to filter to a specific schema, and credentials. You can choose Windows authentication, which uses your current login token, or Database authentication, which requires a SQL username and password stored in Excel's credential manager. For Azure SQL databases, Microsoft Account authentication via OAuth is also supported and is generally preferred for security.

Once connected, the Navigator pane displays a hierarchical view of databases, schemas, tables, and views. You can select multiple objects to import simultaneously, which is useful when building star-schema reports that need a fact table joined to several dimension tables. Excel imports each object as a separate query, and you can establish relationships between them in the Data Model using primary and foreign key columns.

For complex requirements, click Advanced Options in the connection dialog and enter a custom SQL statement. This lets you join tables on the server side, filter rows with WHERE clauses, aggregate with GROUP BY, and apply transformations that would be expensive to do in Power Query. The general rule is to push computation as close to the data source as possible. A query that returns 10,000 aggregated rows from a 50-million-row table will refresh in seconds, while loading all 50 million rows to Excel will likely fail.

ODBC connections handle the long tail of databases not directly supported. Install the appropriate ODBC driver from your database vendor, create a DSN through the Windows ODBC Data Source Administrator (search for it in the Start menu, choosing the 64-bit version to match modern Excel), then use Data > Get Data > From Other Sources > From ODBC. Select your DSN and provide credentials. This same pattern works for cloud data warehouses like Snowflake, Amazon Redshift, and Google BigQuery, though many now have dedicated connectors as well.

Microsoft Access remains a common source for departmental databases. Use From Database > From Microsoft Access Database and browse to the .accdb file. You can import tables, queries, and even linked tables if Access is itself connected to SharePoint or SQL Server. Note that connecting requires the Access Database Engine to be installed. If you get an error about the OLEDB provider, download the 64-bit Access Database Engine from Microsoft and install it. This requirement catches many users on freshly imaged work laptops.

Performance matters when working with database connections. Use server-side filters wherever possible by adding WHERE clauses or by using the filter pane in Power Query (which translates to native SQL via query folding when conditions are met). Avoid loading lookup tables in their entirety when you only need a subset. And consider scheduling refreshes during off-peak hours for very large reports, especially when shared via OneDrive or SharePoint where multiple users may trigger refreshes simultaneously.

Even with the best preparation, imports occasionally produce unexpected results. The good news is that most issues fall into a handful of recognizable patterns. Learning to diagnose them quickly will save countless hours of frustration. The first category involves character encoding problems, which manifest as garbled text where you expected accented letters, currency symbols, or non-Latin scripts. The fix is almost always to change the File Origin dropdown in the Power Query source step to UTF-8 or to Unicode UTF-16 depending on the source system.

The second common issue is data type mismatches. A column that should be numeric loads as text, breaking your SUM formulas. Or worse, a phone number column with leading plus signs loads as a number, stripping the formatting. The solution is to explicitly set data types in Power Query before loading, not after. Click each column header, use the type icon to the left of the column name, and pick the correct type. Power Query records the type change as a step that runs on every refresh, ensuring consistency.

Duplicate rows often appear when combining multiple files or refreshing incrementally. Use Power Query's Remove Duplicates feature available under Home > Remove Rows > Remove Duplicates. Select the columns that define uniqueness before clicking the button, otherwise Excel uses all columns and may miss near-duplicates. For more sophisticated deduplication that keeps the most recent record per key, use Group By with an All Rows aggregation followed by sorting and taking the first record.

Header detection sometimes fails when the source file has multiple header rows or metadata above the actual data. In Power Query, use Home > Remove Rows > Remove Top Rows to strip metadata, then Home > Use First Row as Headers to promote the real header row. If headers span multiple rows, you may need to combine them first using a custom column with a formula like equals Table.AddColumn or by transposing the table briefly to combine column names.

Refresh failures after a successful initial import usually indicate that the source location or credentials have changed. Open Data > Queries & Connections, right-click the failing query, and choose Edit to see the error in the Power Query Editor. Common causes include moved files, expired passwords, network drives that did not mount, or permission changes on database tables. The Data Source Settings dialog under Data > Get Data lets you update credentials and file paths without rebuilding the entire query.

Memory and performance issues plague large imports. If Excel becomes sluggish or shows out-of-memory errors, consider three strategies. First, load to the Data Model instead of a worksheet, which uses columnar compression and handles vastly more data. Second, filter the data at the source to bring in only what you need. Third, switch to 64-bit Excel if you are still on 32-bit, which is now standard but worth checking under File > Account > About Excel.

Finally, document your imports. Add comments in the Power Query Editor by right-clicking each step and choosing Properties to add a description. Include the source URL, the refresh schedule, and any quirks. Future-you, or the colleague who inherits the workbook, will be grateful. Combine these import skills with companion techniques like how to create a drop down list in excel for parameter selection, and you have a complete self-service analytics workflow built entirely in Excel.

Now that you understand the major import methods and how to troubleshoot them, let's cover practical tips that elevate your work from competent to expert. The first principle is to think of imports as pipelines rather than one-time events. Every dataset you import will eventually need to be refreshed, audited, or extended. Design your queries with this future maintenance in mind, using descriptive names, breaking complex transformations into multiple staged queries, and documenting non-obvious decisions inside the M code itself.

Naming conventions matter more than they seem. Default query names like Query1 and Query2 become impossible to navigate in workbooks with a dozen connections. Adopt a prefix system such as src_ for raw source queries, stg_ for staging transformations, and rpt_ for final report tables loaded to worksheets. This makes the Queries pane self-documenting and helps colleagues understand the data flow at a glance. Use parameters for values like file paths and date ranges so they can be updated in one place.

Build a robust folder structure for source files. If a Power Query imports from C:\Users\YourName\Desktop\report.csv, the query breaks for anyone else who opens the workbook. Use shared network paths, OneDrive synced folders, or SharePoint document libraries that all collaborators can access with the same path. Even better, parameterize the folder location so a single cell in the workbook defines the source root. This makes the workbook portable across machines and users.

For recurring monthly or weekly reports, combine multiple files automatically using the From Folder connector. Data > Get Data > From File > From Folder points Power Query at a directory. It then iterates over every file matching your filter and combines them into a single table. This is perfect for monthly sales exports where each month produces a new file with identical structure. Adding the file name as a column lets you trace each row back to its source.

Take advantage of staging queries to avoid repeating work. If you import the same large customer list and use it in five different reports, do not import it five times. Create one query that loads customers as a connection only (no worksheet output), then reference it from five downstream queries using Power Query's Reference feature. This reduces refresh time dramatically and ensures consistency across all reports built on that data.

Test your refresh workflow before you depend on it. Once a query is built, change the source data slightly and refresh to confirm the pipeline handles the new state correctly. Test edge cases like an empty file, a file with one new column added, or a file with a column renamed. Power Query will fail in different ways depending on the change, and knowing how it fails helps you write more defensive queries that include guards like Table.SelectColumns with MissingField.Ignore.

Finally, invest time learning the M language itself. While the Power Query Editor UI generates M code automatically, hand-editing the code in the Advanced Editor unlocks capabilities the UI cannot express. Custom functions, dynamic column generation, and conditional logic all become straightforward once you can read and write M. There are free Microsoft Docs references and excellent community resources that walk through M syntax for analysts who never thought of themselves as programmers.