Subscribe in a readerImporting 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 2010/2007
On the ribbon, click the data tab and then click the "From Text" button on the "Get External data" group.
The "From Text" button, located on the Data tab of the ribbon.
Select your file from the next 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, selecting source type.
In this dialog, choose the "Text files" option and then navigate to your 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.
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
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
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
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
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
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
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 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
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
2. 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 59 in total (Show All Comments):Comment by: Grace Jiang (11/7/2011 7:08:50 PM)Dear sir or madam,
First, thanks for sharing your knowledge with us.
I have a small business with hundreds of customers. A third party will collect rental for me every day depending on customers' billing days, then they send Invoice to me. After receiving the Invoice, I need to enter the payment I received from the customer to my customer list every day. It is very time consuming. I wander how I can use Excel data function to make the work easy and faster.
My data base which contains all customer information is an Excel 2003 file. The Invoice I received from the third party is a .cvs or .dat file.
My question is how I can transfer the rental to my customer list by using Excel 2003 data function.
Thanks,
Grace Jiang
Comment by: Jan Karel Pieterse (11/8/2011 12:55:15 AM)Hi Grace,
This is too complex a question for me to answer on the website I'm afraid.
I suggest you to either contact me directly (see email address below) so we might start a project for this, or to ask your question in smaller portions at http://www.eileenslounge.com.
Comment by: Andrea (1/11/2012 9:14:08 AM)The text file I am working with is a copy of a detailed financial report,usually over 50 pages. It contains headers, blank rows, subtotals, and lots of extraneous stuff that I don't want. (Unfortunately, a transaction report is unavailable, and this is all I have to work with.) Therefore, once I import the file, it requires much manual manipulation to parse it down to the real data to import into my transaction database. The problem then is that it contains old and new transactions, and there is no distinction between the two. I need it to import the new data without overwriting the old, because those records have been changed in my database and overwriting them would remove my changes. Is there a way for excel to compare the two sets of data and import only the "new" records, without overwriting the other records?
Thanks in Advance!
Comment by: Lani (1/11/2012 4:31:02 PM)Hello!
I am importing data into excel which has around 21,000 columns, so I am importing on different worksheets using the "do not import column" option in the "text import wizard" to exclude the columns i have already imported on the previous sheet. The only issue is that the data preview area (from which I choose the columns not to import) will not preview enough columns so i cannot import the last 2000 or so columns. How can I get around this?
thanks so much for your help.
Comment by: Jan Karel Pieterse (1/11/2012 11:42:38 PM)Hi Andrea,
I'm afraid there isn't a simple solution, you will need to (have someone) write VBA code to do all of this.
You could try splitting your problem into small parts and asking your questions over at Eileen's lounge (see link above comment box).
Comment by: Jan Karel Pieterse (1/11/2012 11:44:21 PM)Hi Lani,
The only way I would know is by using VBA code to handle the entire import. Unfortunately I don't have time to create an example for you. Perhaps someone at Eileens lounge can help? (Link above the comment box)
Comment by: mark nelson (1/21/2012 2:00:28 AM)Great site!
I am writing a program that repeatedly dumps out a csv file that I load into excel. I had been doing all the settings each time, so the info you provided saves me lots of time and headaches.
One additional question: in some generic-type csv files that my program creates, all I want is to import the file into excel and have the first row frozen (freeze pane). I sort of doubt there is a way to do the following, but I will ask just in case: is there anything I can put at the top of my csv file that will tell excel, once I click on the file, that I want the top row frozen? (My program could generate whatever command is needed and print it as the first line of the file).
Thank you.
Comment by: Jan Karel Pieterse (1/22/2012 10:23:45 PM)No, not in the CSV file. But I would think you can just freeze panes before the import, it should "stick".
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.