Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed

Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Events

Visit our Amsterdam Excel Summit

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > Import textfiles
Deze pagina in het Nederlands

Importing text files in an Excel sheet

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

This article has been published on one of the Microsoft blogs: Importing Text Files in an Excel Sheet

Introduction

If you've ever tried to open a text file, you have probably used the Text import wizard.

If you have ever opened a CSV file, you have probably noted you can't set up how it gets imported. The text import wizard does not start for these file types.

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?

I'll tell you.

Setting up which file (type) to import

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

Excel 2013/2010/2007

On the ribbon, click the data tab and then click the "From Text" button on the "Get External data" group.

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

The "From Text" button, located on the Data tab of the ribbon.

Select your file from the next dialog:

Import text file dialog

The import text file dialog.

Click Import. (continue to read after the section on Excel 2003 and older)

Excel 2003 and older

From the menu, select Data, Get External Data, Import Data. The following dialog opens:

The select data source dialog of Excel 2003

The select data source dialog of Excel 2003, selecting source type.

In this dialog, choose the "Text files" option and then navigate to your file:

Select data source dialog, selecting a file

Select data source dialog, selecting a file

All versions: defining the import settings

So far we've selected what to import. Now it is time to define what settings we need for the import. This section applies to all Excel versions as of Excel XP (and maybe even older). The screenshots shown here may look slightly different from yours, but the principle is the same.

Click the Open 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 textfile

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.

Next, we'll need to split this up in two sections again, because the various Excel versions differ in how it is done.

Excel 2013/2010/2007

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.

Excel 2003 and older

In Excel 2003, the refresh button can be found in two places.

1. On the Data menu (you need have a cell selected within an imported table):

The refresh button on the data menu

The refresh button on the data menu

2. On the External data toolbar:

The refresh button on the External data toolbar

The refresh button on the External data toolbar

So there you go, all set!

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.

This article has given you an overview of how this should  be set up.

Feel free to add comments and/or questions below.

 


Comments

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

 


Comment by: Michael (12/18/2014 11:41:29 PM)

Hi Jan,
Great information here, thank you!!
But how can i make all this import automatically?
Creating a import button in excel that automatically import all the information from 10 other files with the same formatation but with different information.

Regards,

 


Comment by: Jan Karel Pieterse (12/19/2014 11:55:03 AM)

Hi Michael,

You could do something like this:

Sub RefreshThem()
    Dim vFileName as Variant
    vFilename = Application.GetOpenFilename("text files (*.txt),*.txt", , "Please select the file(s) to import", , True)
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    For lCount = LBound(vFilename) To UBound(vFilename)
        With Worksheets("Sheet1")
            .Querytables(1).COnnection = "TEXT;" & vFilename(lCount)
            .Querytables(1).Refresh False
            .UsedRange.Copy
            With Worksheets("Sheet2")
                .Paste .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
            End With
        End With
    Next
End Sub

 


Comment by: Sasi (2/24/2015 2:59:30 PM)

Hello,
I have text file with 6165202 lines.
I have managed to import first 1048576 lines in first sheet.
Next I have created the new sheet and typed 1040000 as the first line for import to this sheet.
When the import finished, I saw only 48576 rows in this sheet. Why ?
What can I do to make it possible to import the whole file to many sheets ?

 


Comment by: Jan Karel Pieterse (2/24/2015 3:39:06 PM)

Hi Sasi,

Why are you trying to import that many lines into an Excel file? It is perhaps better to import your data into a database system like Access and then connect an Excel pivottable to that database. That way you do not need to place the data in worksheets and are not limited to 1 million rows.

 


Comment by: prince (3/2/2015 4:17:55 PM)

Hi !
Will you please help me how can i access the name from one sheet to another sheet . the main thing is when i call the data from (sheet1!A1) after that 14 row should be blank in sheet 2. as well as on the row 15 of sheet2 , should store ( sheet1! A2) data. I have created such type of format on Sheet2

NAME :- Rozer 1 Roll-no 801

14 time blank line after that

NAME :- Sammul Roll-no 802

}}}}

All information are accessing from sheet1 . i want if i enter the data in sheet one, then sheet 2 automatically create such type of forms.I hope you will suggest me the best formula.The main problem arising in ( NAME & Roll-No ) column.
Thank you!!!

 


Comment by: Jan Karel Pieterse (3/2/2015 5:29:04 PM)

Hi Prince,

Please go here to ask your question, as there will be more people available to try to help you:
www.eileenslounge.com

 


Comment by: wesley (3/3/2015 9:46:40 PM)

Can I import just the last row and only the last row of a text file every time I refresh?

 


Comment by: Jan Karel Pieterse (3/5/2015 12:04:37 PM)

Hi Wesley,

I don't think you can. However, it is not very hard to write a formula that searches the imported table and returns the last used rownumber. For a column with numerical data:

MATCH(9.99999999999999E+307,A:A)

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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