Text files are compact files and hardly prone to attack by viruses.You can get Comma Separated or CSV files from a variety of databases like MySQL. Therefore importing such CSV files into MS Excel provides a great way to perform calculations and analysis on the imported data.
Let’s learn how to implement the process of getting data into MS Excel via CSV text files;
Click on the Data tab
From the Get External Data group we select ‘From Text’
From the window ‘Import Text File’ we’ll select our file from the appropriate folder
Click on Import
A new window call Text Import Wizard Step 1 of 3 opens
It first informs us that that ‘The Text Wizard has determined that your data is Delimited’
It also tells us: If this is correct, choose Next, or choose the data type that best describes your data.
The Delimited option is automatically selected
Sometimes your data may be ‘Fixed Width’ and you may need to select this option
The wizard also offers the option of the row from which you would like to import the data. The default value is 1.
Below this it shows you a preview of the data that you are going to import
We click on Next
In the new window Text Import Wizard Step 2 of 3 we are informed: that this screen lets you set the delimiters your data contains and that you can see how your text is affected in the preview below.
Under Delimiters the Tab option is selected by default
We deselect the Tab option and select Comma because our delimiter is a comma
Also note that you can check the option ‘Treat consecutive delimiters as one’ because while typing the data entry operator can enter two commas
You have the Semicolon and Space as other delimiter options. Using Other option you define your own delimiter
When we click on comma our data separates into columns as in a table which you can previw
Text Wizard Import step 3 of 3 tells us that this screen lets you select each column and set the Data Format.
The column data format ‘General’ is the default option. ‘General’ converts numerical values to numbers, date values to dates, and all remaining values to text.
We click Finish
A new window with the title Import Data opens
This window asks the user ‘Where do you want to put the data?
It offers to put the data in the Existing Worksheet in cell A1
You can place the data in the cell of your choice and also in a New Worksheet
We select cell A3
We click on ‘Properties…’ and here we can select ‘Refresh data when opening the file’ because if edit the file we will automatically get the latest data into our Excel worksheet when we reopen the file
We click OK and the again OK to get our data into the Excel worksheet
Here we can perform more calculations quickly and easily
We have repeated the procedure with another interesting CSV file imported from the Google keyword research tool to demonstrate how important and useful such import of data into Excel is.
Watch the Excel training video:
Import or export text (.txt or .csv) files