How to Import Delimited Text Files into Microsoft Excel

AOL recently released a large amount of search data in the form of text files to the general public through their research website (they’ve since taken it down). I was able to download a copy of that data which is roughly 2 Gigabytes in size and contains over 30,000,000 searches from 650,000 subscribers.

After unzipping the files, you are presented with 10 text files that are tab delimited and divided into five columns. The data set includes the following five fields: AnonID, Query, QueryTime, ItemRank, and ClickURL. There is only some data within the ItemRank, and ClickURL columns, but the columns are still tab delimited, so they will become null when imported into Excel.

So getting to the point of this article, I wanted to be able to sort through the AOL search data easier, instead of just trying to look at the records in a text file. Using the Import External Data option in Excel I was able to quickly and easily import these delimited text files into Excel for analysis. The Text Import Wizard in Excel not only lets you import Tab delimited flat files, but it also allows you to import files that are divided by fixed width columns or delimited by a Semicolon, Comma (also known as Comma Separated Value (CSV) file format), Space or Other (you choose).

While I was importing the delimited text files into Excel, I did come across one major limitation of Excel and that is that Excel will only import 65,536 rows into a single worksheet. So if your text file has more than 65,536 rows, you will need to split the text file up and import them across multiple worksheets. In my case, each AOL search text file I was trying to import had over 3,000,000 rows of data, so instead of importing this data into Excel, I really should have been importing it into a database program like MySql or Sql Server 2005. Lesson learned. See: Import Delimited Text Files into a SQL Server 2005.

With that said, here is a step-by-step example of how you can import delimited text files into Microsoft Excel version 2002.

Importing External Tab Delimited Text Files

1. Open Microsoft Excel and start a new blank worksheet.
2. Click Data on the Menu bar, and select Import External Data, then click on Import Data. See Figure 1.
3. In the Select Data Source dialog box browse to the text file you want to import and once it’s selected, click Open. See Figure 2. The Text Import Wizard dialog box will automatically pop-up.
4. In Step 1 of the Text Import Wizard, choose Delimited as the file type, and select which row to start the import with. If the first row of your text file has headers, then you will probably want to Start import at row 2. See Figure 3.
5. Click Next to go to Step 2.
6. Select Tab as the Delimiter and click Next to go to Step 3. See Figure 4.
7. Choose the column format for each column if you don’t want to use General as the default. Once you’re done formatting the columns click Finish. See Figure 5.
8. Now choose where you want the data to be located on your Existing worksheet or on a New worksheet, and then click OK to start the import process.

If all goes well you should immediately see the imported tab delimited data in your Excel worksheet, divided into columns. That’s all there is to it!

To read more about the AOL search data that was used while writing this step-by-step, check out TechCrunch’s article titled AOL Proudly Releases Massive Amounts of Private Data.  AOL has since removed the search data files from their research website due to the privacy concerns, but you can still find the data available on mirrored sites across the web.