Importing text files in an Excel sheet

Content

Introduction

In this article I'll explain how to ease importing of .txt, .prn and/or .csv files into an existing sheet in Excel.

If you've ever tried to open a text file in Excel (File, Open, select "Text files" as the file type), you have probably used the old Text import wizard. If you have ever opened a CSV file by double-clicking the file in Windows, you have probably noted you can't set up how it gets imported. The text import wizard does not when these file are opened from a double-click. What if you have the situation where you get files with similar structure, which need the same text import settings over and over? How do you import the data from those files into the same worksheet each time, without having to re-define the import settings over and over again?

In this article I describe how to do this both using Get & Transform and the old-style text import wizard.

Using Get & Transform Data (AKA Power Query)

Before jumping into defining what the PowerQuery import steps are, you first need to ask yourself a couple of questions:

  1. Will the name of the file I am importing change?
  2. Will the location of the file change (regularly)
  3. Do I want to import one file, or perhaps more than one?

If the answer to 1 is yes, you may want to consider using different steps than described here. In such cases, consider using the From Folder option: Data tab, Get Data button, From File, From Folder. That route enables you to e.g. filter files by extension, maybe sort them so the most recent file is on top and then open only that file. Or you might want to open all .txt files in that folder which have a name starting with the word "balance". The possibilities are endless.

Anyway, let's import a single CSV.

Setting the locale

CSV files do not adhere to any international standards. The only thing that is certain is that in such a file columns are separated by a character. The same applies to other things in CSV files: There is no agreement on what exactly a date must look like, nor is there a standard for the decimal and thousands separators. All of this depends on the locale of the machine that generated the CSV. So you might have to deal with a CSV which is separated by semi-colons, has numbers using the comma as decimal separator and shows dates in the format dd-mm-yyyy. In the legacy text import wizard (shown below), you choose these settings after choosing the file. In PowerQuery it's easiest if you choose them up front.

It is a good idea to open your csv in Notepad so you can see what it contains. This allows you to find out things like

  • Column separator
  • Decimal separator
  • Thousands separator
  • Date format order

Look at your CSV file in Notepad

If you're lucky, the CSV you have has adhered to a system with settings taken from a known locale (unlike the example above). In that case you can first tell PowerQuery which locale to use when importing. Click the Data tab, select Get Data and click "Query Options". Then on the Regional Settings tab choose the correct "Locale":

Setting the Locale prior to importing a CSV

How do you decide which locale you need? The dialog does not tell you which separators and formats belong to each locale. Google to the rescue, Chris Webb has figured this out for us! Chris even made a workbook available which lists all of the formats.

Having set the locale we're now ready to start the actual import. Click Data, From Text/CSV. After choosing the file you want to import, this dialog opens.

The first step of the PowerQuery From Text/CSV option

As you can see, Power Query has made a best guess on things like the delimiter of the file and has tried to defer the data types from the first 200 rows of data. Change those options if these are not to your liking.

The best step to take next is almost always to click "Transform Data". Simply clicking Load is hardly ever good enough as you'll have to retrace your steps if anything is amiss, like dates not being recognized as true dates. So always click Transform Data. You will thank me later.

After you've clicked Transform Data, the Power Query window opens. Check all your columns now. The icon next to their headers indicate the data type that was detected:

The PowerQuery window displaying a preview of the data

If any of the columns have the wrong datatype, click its icon to change it. You MUST choose the right data type, otherwise your data may be incorrect!

Now its the time to do things like deleting columns you don't need and sorting the data the way you want it sorted. Once you're happy with the data, click on the Home tab of the PQ ribbon and on the drop-down beneath "Close & Load", choose "Close & Load to..."

The PowerQuery window close and load

In the next dialog you get to decide where you want the resulting data from your query to go:

The Import data dialog after clicking close and load

Unfortunately at this stage the Properties button is unavailable, click OK with these settings if you want the data to appear as a table in your worksheet.

To access those properties, click in the newly imported table, click the "Table Design" tab and find the Properties button:

Accessing the query properties

In the dialog that opens you can set things like what to do when less or more rows of data are imported and whether or not to adjust column widths automatically:

The data range properties dialog

There are even more properties to modify, you find these additional properties  by making sure the Queries & Connections pane is visible by clicking the appropriate button on the Data tab:

The Queries & Connections task pane

Then right-click on the highlighted query and select Properties to access properties like when the query must be refreshed:

The Queries properties dialog

Using the old text import wizard

Getting back the old import wizards

As of Excel 2016, Power Query is built in to Excel. To promote usage of PowerQuery, the old-style text import wizard is hidden from the user interface. Here is how to get it back:

Click File, Options and click on the Data tab. Check the boxes you need as shown in the screen-shot:

Enabling legacy data import wizards

After that, you find the legacy Text import wizard here:

Import data from text files on Excel 2007/2010 ribbon.

Selecting the file to import

First select the sheet where your data needs to appear. Note that the following steps differ for the various Excel versions.

On the ribbon, click the Data tab and then click Get Data, Legacy Wizards and finally "From Text (legacy)":

 Import data from text files on Excel 2007/2010 ribbon.

Select your file from the next dialog:

Import text file dialog

We've selected what to import. Now it is time to define what settings we need for the import.

Click the Import button. The text import wizard opens up:

Step 1 of the Text Import Wizard, define file type

Step 1 of the Text Import Wizard, define file type.

In this example I have selected to import a delimited file and set the file origin to Windows (ANSI). Click Next when you're happy with the settings.

Step 2 of the wizard allows us to define the delimiters. I selected Comma:

 Step 2 of the Text Import Wizard: define delimiters

Step 2 of the Text Import Wizard: define delimiters

Click Next again to go into the third step, where you can select a format for each column of your file. I changed the date format of the first column to dmy order. Click a column to set up its formatting.

Step 3 of the wizard, Define column formats

Step 3 of the wizard, Define column formats

You can click the advanced button to set up details like the decimal and thousands separators (note I switched them here):

The Advanced text Import Settings dialog

The Advanced text Import Settings dialog

Note that any changes made in this dialog apply to all columns.

After you finished defining all columns, click the Finish button. Excel opens the Import Data dialog, asking where to put the results. Select the proper location.

The Import Data dialog

The Import Data dialog

Hold your horses, don't click OK yet! Click the properties button instead. The External Data Range Properties dialog opens up. There are some very important settings to be made here!

The External Data Range Properties dialog

The External Data Range Properties dialog

Note that I have highlighted some important features in this dialog. I'll discuss them below.

Save Query Definition

We wouldn't want to redo the settings each time, so lets keep this one checked!

Prompt for the file name on refresh

Checking this box forces Excel to prompt you for a file name each time you hit the refresh button. Leave this box unchecked if you are importing the same file over and over.

Overwrite existing cells with new data, clear unused cells

What selection is best here depends on your needs entirely. The first two will force Excel to push existing data down and pull existing data up, depending on the size of your file.

I recommend NOT to put anything below an area where you are importing data;
use a dedicated worksheet for this purpose.

Fill down formulas in columns adjacent to data

A very useful option. If you are planning to do calculations on each row, this option ensures each row of data will have your formulas.

Hit OK if you're happy with the settings.

Import your data

Finally, click OK to have your data imported. My sheet looks like this:

Results after importing the textfile

Results after importing the text file

Now that you have properly set up your import, save your file!

Refresh your data

All of this was meant to predefine an import routine you have to do over and over again. So how do you import a new file? Easy. Click any cell within your sheet that holds data of a previous import.

On the Data tab, find the group called "Connections". Click the dropdown "Refresh All" and select "Refresh:

The Refresh All button on the ribbon

The Refresh All button on the ribbon

If you have set the option to prompt for a file, Excel will open the appropriate dialog. Select your file and click Import.

Conclusion

Importing text files into Excel can be a labor-intensive process if you're going through the same steps over and over again. With proper use of the data import options Excel offers, you can save yourself quite some work and at the same time improve consistency.

Whether you want to use the modern PowerQuery method or the old-school Text Import wizard is a matter of both personal preference and balancing the pros and cons of both methods. The legacy wizard makes it easy to select a different file on refresh. But the PowerQuery method offers way more flexibility in reshaping the data.

Feel free to add comments and/or questions below.

 


Comments

Showing last 8 comments of 287 in total (Show All Comments):

 


Comment by: Joe L (27-11-2017 22:52:21) deeplink to this comment

Does anyone know an easy way to bring multiple rows into one row. I have a nasty fixed width report that I need to condense into one row per record in excel. There are four rows of data for each record that need to be combined into one row and then broken out into a column for each field. This seems to be very difficult to do unless you're a VBA ninja. Thanks for the help.


Comment by: Jan Karel Pieterse (30-11-2017 10:42:20) deeplink to this comment

Hi Joe,

Can you share two rows of sample data?


Comment by: Pete Maz (18-10-2018 16:32:09) deeplink to this comment

Hi. I've been trying to find a way to import the delimited text file which was last saved to a directory. I have a directory with over two dozen files, each of which is date in the title;
i.e.
\Dir\TextFile_01012001
\Dir\TextFile_02012001
     etc.
I want to import only \Dir\TextFile_17012001 and ADD it to the files already delimited into excel.

Any ideas, please


Comment by: Jan Karel Pieterse (18-10-2018 16:57:48) deeplink to this comment

Hi Pete,

If you're onm Office 365 I'd recommend using "New Query", "From File", "From Folder" from the Data tab of the ribbon.


Comment by: Excel User (4-3-2020 17:10:00) deeplink to this comment

Is there any way to use the saved query definition without clicking on refresh?
I import two files each week in the same format (one from a legacy system and one from the new). The import layout is exactly the same.
Ideally I would like to be able to go through the import data from text process, but just pull up the saved layout.


Comment by: Jan Karel Pieterse (5-3-2020 10:31:00) deeplink to this comment

Hi Excel user,

WHat exactly do you mean by "use the query definition"?


Comment by: bittarelli (30-11-2020 15:58:00) deeplink to this comment

when i download data into text wizard it appears as "blink" It no longer shows the file I am attempting to download in the usual delimeters etc. Why is that. Yesterday I tried to create a file that would save headings and since then I cannot download files correctly.


Comment by: Jan Karel Pieterse (30-11-2020 16:53:00) deeplink to this comment

Hi bittarelli,

Hard to say, have you tried this with several txt or CSV files?


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].