How To Import CSV Files Into Excel – And Other Text Files As Well (Easy)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Not every document you need to open is in XLSX format. Sometimes you get comma-separated value (CSV) files, other tabular data, or even plain-text files.

Luckily, Excel handle those file-formats very well (as well as easy)!

Excel contains some very useful functions for importing other types of files and converting them into spreadsheets that you can work with. It’s especially good with CSVs, but it can work with all sorts of data.

Let’s take a look at how Excel can help you with these file formats…

How CSV files are formatted

CSV files are formatted in a very specific way so they can be read by programs like Excel.

Each line of the file is a “record.” In Excel, that means it’s a row.

Commas separate fields within the record. So Excel sees “excel,csv,formatting” like this:

csv-in-excel

Records are delineated by line breaks. So a CSV file might look like this:

Name,Position,ID number
John Doe,Sales Manager,04071
Allan Xander,Regional Manager,48294
Jennifer Beeler,VP,55724

That would place each person and their information in a different row.

Note that no comma is needed for a line break.

Kasper Langmann, Co-founder of Spreadsheeto

The only other notable thing about CSV files is that quotes indicate text fields. That’s important because text fields can contain commas and line breaks.

If they’re contained within quotes, commas and line breaks won’t end the field or the record.

Get your FREE exercise file

It’s easiest to learn how Excel imports CSV and other text files, by practicing yourself.

We’ve whipped up a small text file with some data in it. Download it below and follow along!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Importing CSV files into Excel

First, use a text editor to open up the example CSV file that you downloaded above.

It looks like this:

csv

The text on the top has standard CSV formatting (ignore the second part for now, as we’ll use it in a moment).

Let’s import it into Excel.

We’ll be doing a manual import to practice, but you can also double-click a CSV file and Excel will open it.

Kasper Langmann, Co-founder of Spreadsheeto

Open a new spreadsheet, then go to the Data tab of the Ribbon.

Click Get External Data, then select From Text:

get-from-text

This will bring up a file explorer. Select the CSV file and click Import.

From here, you’ll see the Text Import Wizard, which walks you through the steps of importing a CSV or other text file.

First, you’ll need to select the original data type. When you’re using a CSV file, you’ll want Delimited.

comma-delimiter

This means that a character—usually a comma—separates the different fields.

Don’t worry about the Fixed width option. That’s for non-tabulated data, and is best for importing columns of text.

Kasper Langmann, Co-founder of Spreadsheeto

Click Next, and Excel will ask you which delimiter your data uses. For a CSV file, select Comma and deselect the other options:

comma-delimiter

Click Next again, and you’ll see a preview of your data.

You can select individual columns and applying specific formatting by clicking them and choosing an option from the Column data format list.

We’ll leave it as General, which is what you’ll most often use.

Finally, click Finish to get your data. Select Existing worksheet to add the data to the current worksheet:

existing-worksheet

Click OK, and you’ll see the contents of the CSV file in Excel.

imported-csv

Our data was imported perfectly; everything separated by a comma is in its own cell, and the line breaks define new records.

You can see that the bottom line, which wasn’t separated by commas, wasn’t imported in the same way. That’s because it uses different delimiters.

Using non-comma delimiters

CSV files are very common, but some applications might give you data in another format. Fortunately, Excel is prepared for this situation!

We’ve included a line of text in our sample file that’s delimited by slashes instead of commas. Let’s see what Excel does with that.

Go to the Data tab in the Ribbon again, select Get External Data and From Text, and open the Text Import Wizard with the sample text file again. Click Next to get to the delimiter choice.

This time, instead of selecting Comma, you’ll select Other and type a slash in the box:

other-delimiter

This tells Excel to look for slashes to separate fields, instead of commas. You’ll see the preview update to take this into account:

data-preview

Note that the comma-separated data in the first part of the text file doesn’t get split into different cells, because Excel is only looking at slashes.

You could, however, also check Comma to get them both to import properly.

Kasper Langmann, Co-founder of Spreadsheeto

The Treat consecutive delimiters as one option is a useful one here. Because some of our data is separated by two slashes, instead of one, we’ll be importing some blank cells.

Check that box and see how the preview updates.

Then click Next and Finish, and the data will be imported!

slash-delimiter-imported

Above, we left Comma and Treat consecutive delimiters as one unchecked.

By using the various delimiters and attendant options, you can import any text file.

From there, it’s just a matter of cleaning up your data so it looks how you want it to!

Import text with a few clicks

Whether you’re working with CSV files or other text-based information, Excel can convert it into a usable format in your workbook.

You just have to know which buttons to push. And now that you do, you’ll have no problem getting text files into Excel!

2018-08-28T08:23:05+00:00