Import data from large CSV and TXT files into Excel

Importing data from CSV, TSV and other text formats is an everyday task for SEO specialists, analysts and marketers. Exports from ad platforms, search consoles, crawlers and CRMs often reach hundreds of megabytes, and sometimes gigabytes.

The standard Excel methods that work fine with small files fail on serious data volumes. In this article, I’ll go through why the built-in tools can’t cope, what goes wrong even with small files, and how to import large text files properly.

Built-in ways to import text files into Excel

Excel has several built-in ways to load text files. Let’s look at their strengths and weaknesses.

1. File → Open

The most obvious way is to open a CSV file like a regular Excel workbook, or just double-click it. For .txt files, Excel launches the Text Import Wizard, where you choose the delimiter and the column formats; a .csv file opens straight away with the default settings.

The problems:

• Excel loads the whole file into memory. If the file is larger than the available RAM, the program freezes or shows an error.
• A sheet holds at most 1,048,576 rows. A longer file is cut off with the message “File not loaded completely”, and the rest of the data is simply lost.
• There is no way to filter out unnecessary rows at the opening stage: absolutely all data is imported.
• Excel guesses the data types on its own. More on what that leads to below.

2. Power Query (Data → From Text/CSV)

Power Query (in Excel 2016 and later: Data → Get Data → From File → From Text/CSV) is a much more powerful tool. It lets you filter rows, change data types and combine files before loading them onto a sheet. It can also load data into the Data Model instead of a sheet, bypassing the row limit.

The perfect solution? There are catches:

• Power Query also works with the whole file in memory during processing.
• With files of 500 MB and more, Power Query can “think” for tens of minutes, and the progress indicator doesn’t always show whether the process is running or Excel has frozen.
• Complex transformations and merging several large files can exhaust the memory and crash Excel.
• The data in the Data Model is available in pivot tables, but not as regular cells you can edit.

Power Query is an excellent tool for transforming data, but for simply filtering huge files it’s overkill and inefficient in memory.

The legacy Text Import Wizard

In Excel 365, the old wizard is hidden. To bring it back, go to File → Options → Data, check From Text (Legacy) under Show legacy data import wizards, and it appears in Data → Get Data → Legacy Wizards. It’s the easiest way to set the type of each column manually, for example Text for codes and IDs.

Why imported data looks wrong

Even a small file can be spoiled at import, because Excel interprets the data in its own way:

• Broken characters. Excel opens text files in the system default encoding (usually Windows-1252 in English Windows). A UTF-8 file without a BOM turns “café” into “café”, and non-Latin text becomes completely unreadable. In the import dialog, set File Origin to 65001: Unicode (UTF-8).
• Lost leading zeros. ZIP codes, phone numbers and article codes like 00123 become 123. Import such columns as Text.
• Long numbers in scientific notation. IDs of 12+ digits are shown as 1.23457E+11, and digits after the 15th are replaced with zeros forever. Excel stores only 15 significant digits, so import IDs as Text.
• Mixed-up dates. 03/04/2026 is March 4 in the US and April 3 in Europe. If the file uses a different date order than your system, some dates become wrong and others stay text. Set the date format of the column in the wizard, or use DATEVALUE afterwards.
• The whole row in one column. The file uses a semicolon or a tab as the delimiter, while Excel expected a comma (or vice versa, depending on the regional settings). Choose the delimiter manually.

If the data is already on the sheet, see how to convert numbers stored as text to numbers.

Reading large text files line by line

The main weakness of all built-in methods is loading the whole file into memory. For big data, reading line by line (streaming) is much more efficient. In this case, the program:

• opens the file and reads one line at a time;
• immediately applies the filter conditions to the current line;
• if the line matches, writes it to the output file or the Excel sheet;
• if not, moves on to the next line without keeping it in memory.

This way, even a 100 GB file (and more!) can be processed on a computer with 4 GB of RAM. The only limit is the disk read speed. That’s exactly the approach of the two !SEMTools tools below.

Two ways to sample data from large CSV files with !SEMTools

!SEMTools has two tools for sampling data from CSV files, and they solve the problem in different ways. Both are in the Web (parsing, SEO, PPC) group → Extract → Sample from CSV.

Method 1: Flexible sampling with conditions

This tool fits when you need fine-tuned filtering. You can set two conditions at once and combine them with AND / OR.

• 7 comparison types: contains, begins with, ends with, matches a regex, greater than, less than, is any of (comma-separated);
• two patterns and a choice of the logical operator;
• automatic delimiter detection when you select the file, and a preview of the first 10 rows;
• different encodings: UTF-8, Windows-1251, KOI8-R, KOI8-U, ISO-8859-5;
• the choice of the column to apply the conditions to.

The tool reads the source CSV line by line, applies the conditions and outputs the matching rows to a new Excel sheet. When the row limit (1,048,576) is reached, the next sheet is created automatically.

When to use it: when you need complex conditions or several patterns, or you’re not very familiar with regular expressions.

Method 2: Fast filtering with ripgrep

For maximum speed on very large files (500 MB and more), there is the second tool. It’s based on ripgrep, one of the fastest line search utilities.

• Maximum performance: ripgrep is written in Rust and uses multithreading.
• Simplicity: select a file (CSV, TSV, TXT), enter a pattern and get a filtered file.
• Hidden mode: the command line doesn’t get in the way.
• A timer shows how long the filtering took.

On the first run, the tool checks whether ripgrep is installed. If it isn’t, you’ll get an offer to open a page with installation instructions.

Installing ripgrep

The tool needs the ripgrep utility (rg.exe) in the C:\Windows\System32 folder.

• Download ripgrep from the official GitHub releases page: github.com/BurntSushi/ripgrep/releases.
• Find the Assets section of the latest version.
• Download the Windows archive, for example ripgrep-15.1.0-x86_64-pc-windows-msvc.zip (the latest version at the time of writing).
• Open the archive (in Windows, you can just open the zip folder).
• Find the rg.exe file.
• Copy rg.exe to C:\Windows\System32\.
• Done: the procedure will find and use the utility.

To check the installation (optional), open the command prompt (Win+R → cmd → Enter) and run rg --version. If you see the version information, everything is installed.

When to use it: the file is very large and the condition is simple (one pattern, searched in all columns). Ideal for quickly extracting the rows that contain a certain word or phrase.

Comparing the methods

The tools complement each other and cover 99% of scenarios with large text files in Excel. For example, make a basic sample with ripgrep, and then apply complex conditions to the resulting file, which is much smaller.

And if, on the contrary, you have many small files of the same kind, see how to merge several CSV files into one.

Summary

I hope you found what you were looking for on this page. For small files, File → Open or the import wizard with the right encoding and column types is enough, and Power Query helps when the data must be transformed. For files of hundreds of megabytes and more, read them line by line: the !SEMTools sampling tools take only the rows you need, whatever the file size.