Importing Text Files in Excel
Learn how to import TXT and CSV files into Excel, select delimiters, handle text columns correctly, and prepare imported data for analysis.
1. What Is Text File Importing?
Text file importing is the process of bringing data stored in a text-based file into an Excel worksheet. Text files commonly contain records separated by commas, tabs, semicolons, or other delimiters.
- .txt: A text file that may use tabs or another delimiter.
- .csv: A comma-separated values file, although the delimiter can vary.
- Delimited data: Values are separated by a chosen character.
- Fixed-width data: Values occupy defined character positions.
2. Understand Text File Formats
Image 1 — TXT and CSV File Examples
Custom illustration showing how delimited text becomes worksheet columns.
3. Prepare a Sample CSV File
Open Notepad or another plain-text editor and enter the following data. Save the file as Students.csv.
StudentID,StudentName,Course,FeesPaid
101,Samadhan,Java,45000
102,Samiksha,SQL,60000
103,Pradnya,Python,32000
104,Omkar,C#,75000
105,Neha,Java,28000
What Does a Comma Do?
In this example, commas separate the fields. Excel can use the comma delimiter to split each record into four columns.
4. Import a CSV File Using Excel
Image 2 — Excel Data Import Steps
Step-by-step procedure
- Open Excel and create or open the destination workbook.
- Select the Data tab.
- Choose From Text/CSV or the equivalent available import option.
- Browse to Students.csv and select it.
- Check the preview, delimiter, and column data types.
- Select Load to place the data in Excel, or Transform Data to open Power Query.
- Verify that each field appears in the correct column.
5. What Is a Delimiter?
A delimiter is a character or separator that divides fields in a text record. Choosing the correct delimiter helps Excel split data into the intended columns.
| Delimiter | Example | Typical Use |
|---|---|---|
| Comma (,) | 101,Samadhan,Java | Common CSV files. |
| Tab | 101 [Tab] Samadhan [Tab] Java | Tab-separated text files. |
| Semicolon (;) | 101;Samadhan;Java | Files using semicolons as separators. |
| Pipe (|) | 101|Samadhan|Java | Custom structured text exports. |
| Space | 101 Samadhan Java | Some text files; use carefully if values contain spaces. |
6. Delimited vs Fixed-Width Text
Image 3 — Two Ways to Split Text
Fixed-width fields are separated by positions rather than delimiter characters.
Delimited Files
Excel splits a record when it encounters the selected separator, such as a comma or tab.
Fixed-Width Files
Fields are separated according to character positions. During import, check the column boundaries carefully before loading the data.
7. Handle Column Data Types Correctly
Excel may interpret imported fields as numbers, dates, or text. Review the types before loading, especially for IDs, dates, and values that contain leading zeros.
| Value | Potential Problem | Recommended Handling |
|---|---|---|
| 00125 | Leading zeros may disappear if treated as a number. | Import identifiers as Text when zeros are significant. |
| 17-10-2026 | Date interpretation may vary by regional settings. | Check the expected date format during import. |
| 45000 | May be imported as text in some cases. | Convert to a numeric type if arithmetic is required. |
| Samadhan | Extra spaces can affect matching. | Trim unnecessary spaces during cleaning. |
8. Import a Text File Using the Text Import Wizard
Some Excel versions and legacy workflows provide a Text Import Wizard. Its availability and entry point vary by version and settings.
- Select the appropriate text import command if available.
- Choose Delimited or Fixed Width.
- For delimited data, select the correct separator.
- For fixed-width data, adjust the column boundaries.
- Preview the columns and select suitable data formats.
- Choose the destination and finish the import.
In current Excel versions, the Data tab's text/CSV import flow is commonly used instead. Follow the options available in your installation.
9. Common Import Problems and Solutions
| Problem | Possible Cause | Solution |
|---|---|---|
| All values appear in one column. | Incorrect delimiter selected. | Choose the delimiter used by the source file. |
| Leading zeros disappear. | Identifier interpreted as a number. | Import the relevant column as Text. |
| Dates appear incorrectly. | Different date or regional format. | Review the date format during import. |
| Special characters look incorrect. | Encoding mismatch. | Check the file encoding option, such as UTF-8, when available. |
| Columns shift unexpectedly. | Quoted text contains delimiters or source data is inconsistent. | Review the import preview and quote handling. |
| Numbers cannot be calculated. | Numeric values imported as text. | Check data types and convert valid numeric values. |
10. Verify the Imported Worksheet
Image 4 — Imported Data Quality Checklist
Illustrative checklist. Verify these points against your actual imported file.
After importing, compare the row count and sample values with the original file. Check headers, leading zeros, dates, numeric columns, and unexpected blanks before using the data in reports.
11. Practical Exercises
Task 1: Import a CSV File
Create Students.csv using the sample records and import it into Excel with the correct comma delimiter.
Task 2: Import Tab-Delimited Text
Create a text file whose fields are separated by tabs. Import it and verify that each field appears in a separate column.
Task 3: Preserve Leading Zeros
Import IDs such as 00101 and 00102. Ensure that they remain unchanged by treating the ID column as text where appropriate.
Task 4: Fix an Incorrect Delimiter
Import a semicolon-separated file and choose the correct delimiter so that its fields split into the intended columns.
Important Points to Remember
- CSV and TXT files may use different delimiters.
- Check the preview before loading the imported data.
- Keep identifiers as text when leading zeros are significant.
- Verify date formats, numeric values, and character encoding.
- Delimited and fixed-width files require different import settings.
- Use Transform Data when cleaning or reshaping is necessary.
- Validate imported row counts and sample values against the source.
Summary
Excel can import data from TXT and CSV files using supported text import tools. Correct delimiter selection, careful data type handling, and previewing the records help ensure that values are placed in the intended columns.
After importing, verify the headers, row count, identifiers, dates, and numeric values. These checks help prepare reliable data for Excel formulas, PivotTables, and reports.