Importing a text file using Excel can be achieved by following a few simple steps, there is no need to purchase Excel add-ons or learn VBA. This guide will show how even a beginner can import text files with Excel…
Text files are common place in the world of big-data as they are an efficient way of storing and transferring large quantities of data. There are various formats of text files but the majority will be denoted by the file type of .txt and you can usually open them directly from the NotePad application on your computer (note they can take a while to open if they are huge!). A sample text file, opened in NotePad, might look something similar to the below:
The reason they are referred to as text files because that is what they are, a file of plain text. You might be able to see numbers and dates when you open the text file in Notepad but you can’t do anything with them, it would be like trying to add numbers together in MS Word…you’d simply be called crazy (or would you!)
Why save data as a text file to start with?
If saving data in a format where you can’t do any calculations or interrogation against it seems a bit pointless to you consider the two main advantages:
- Memory Space: The first advantage is memory space. Data saved in a text file will take up much less room than stored in Excel format, or other spread-sheet software applications. The reason for this is because a text file is not saving calculations, formulas or multiple data formats it makes it a lot less memory hungry.
- Practical for multiple users of the data: The second advantage of text files is that they can be imported into every single database or spreadsheet based application on the market. That means if you are the creator of a data file that is used by multiple people you can just create the one text file and everyone can use it, regardless of what application they intend to use to interrogate the data.
How to Import text files using Excel
Right this is what you came for, let me show you how to import a text file using Excel.
First open up Excel and select “Data” from along the top menu bar, above the Ribbon. Then in the left corner of the Ribbon you will have an icon that says “From Text”, select that icon:
When you have selected “From Text” a window will open asking you to locate the text file that you wish to import. Navigate to the text file, select it by clicking the left mouse button on the file icon and then click on Import:
Now that you have told Excel where the text file you want to import is located it needs to know a little about the text file before it imports the data into an Excel spreadsheet.
After you click Import a new window will open, showing the Import Wizard for text files:
As you can see this is Step 1 of 3 of the import process and this is where we set any criteria or options for the import.
It is worth mentioning from the outset that Excel is very intuitive when it comes to importing data, you will find the majority of times all the pre-selected options in the import wizard are correct but it’s always best practice to check.
Step 1 of 3
The first choice is whether to select “Delimited” or “Fixed Width”. Here you are telling excel what separates the various fields in your data. Older text files were often fixed width whereby each field had a specific length of characters but these days you will find most text files are delimited, meaning that the fields are separated using a tabspace or a comma.
So change the preset options if needed and click next, if you are unsure just proceed with clicking next as you can always come back and change if the data looks wrongly separated on the next step.
Step 2 of 3
In this step we can check that our text file looks like it will be imported into Excel correctly – the first step is to look at the Data Preview at the bottom of the window – here you want to make sure the fields are separated correctly – if they have then you are fine to proceed.
If the Data Preview looks wrong then it is a case of setting the correct delimiter and possibly selecting what the text qualifier is on the file. If you are struggling with this section then you should open the text file from within the NotePad application – it will be easy to see then if you have tabspaces or commas or semicolons separating the fields.
A text qualifier is where the text start and end point is identified a character, usually quotation marks. For example a line in your text file might look like:
“Mr A Smith”, 199, 06/07/2012, “Sales”
If it did look like that then you would need to select the Text Qualifier as the quotation marks, otherwise Excel will import in the quotation marks around all your text data, which you probably don’t want!
When you are happy with the Data Preview select Next to move onto the final step.
Step 3 of 3
In step 3 of 3 you can select the finer details of the individual fields in your data.
Again there is a Data Preview but only this time it tells us what format Excel intends to apply to each column of data.
The default is “General” and in 99% of occasions that will suffice as the “General” format will convert numbers to numbers, dates to dates and leave text and text so unless you have some uncommon data types there is usually no need to change this section.
If you do need to change the type then select the column you want to alter in the Data Preview box and then select the type from the options on the top left. For even more advanced options, like decimal places etc. then click on the “Advanced…” button while the column is still highlighted in the Data Preview.
When you are happy with everything click on Finish, Excel will then ask you where to put the Imported data:
Change this if needed and click on OK – the text file will now be imported into Excel:
When the data is in Excel you have all the obvious benefits of being able to do calculations, change formats and build charts and reports. The file can be saved as an Excel file so that you can use the data in Excel in the future if needed.
There are a lot of uses for this technique, particularly in the business world where data comes to the Excel analyst in many formats.
The process for importing text files into Excel is very straight-forward and with Excel’s intuition you will find that once you have told Excel where the text file, on many occasions, the rest will be done for you. However, it is always good practice to review the options when importing a new text file into Excel.
The crucial parts in the Import wizard are to make sure the data looks correct in the Data Preview, if that looks fine then you will usually find the import runs perfectly every time.