Importing data to Excel from other programs and sources enables you to further analyse and generate reports or dashboards from what is often 1000's of rows of data.
On the Data tab of the ribbon in the section Get External Data there are four options:
- From Access – a Microsoft Access Database
- From Web – from a web page
- From Text – from a .csv or .txt file
- From Other Sources – like an SQL, XML or other database
1. Importing Data from Access
I’m not going to go into a lot of detail on Access because this can be a topic all on its own insofar as you can either import data into Excel from Access, or export from Access to Excel.
And then there are options as to how you go about this, for example you can cut or copy and paste data, write a macro or Visual Basic (VBA), or export the Access data to an XML file form importing to Excel.
Having said that; if you follow the prompts in Excel to import data from Access it’s fairly self explanatory:
To import data into Excel from Access:
a. Choose the From Access option from the Get External Data group on the Data tab of the ribbon.
b. Browse to your Access file location and follow the prompts.
2. Importing Data from a Web Page
When you click on the From Web button on the ribbon a mini web browser opens in Excel.
From here you can browse to the page that contains the data you want to import.
You can see in the above image there are arrows in yellow boxes indicating the tables in the web page. Some pages will have lots of these and some will have only one.
When you hover your mouse pointer over the arrows a box is shown around the data in the table. Click the arrows on the tables you want to import. You’ll notice the arrows change to a tick in a green box.
I’m going to import currency cross rates as you can see in the image below:
Once I click on the Import button Excel will ask me where I want to place the data:
After a few seconds it will load the data from the web page and you can see the Get External Data group of icons is now inactive and the Properties button in the Connections group is active.
You can now use the data to create charts, or insert formulas to the right or below to the data to analyse it further.
Refreshing Your Imported Data
You can set when you want to refresh your data by modifying the External Data Range Properties. You'll find the properties button on the Data tab of the ribbon under Connections (see image above).
You can set it to refresh when you open the file, or if you’re looking at stock or currency prices you might want to update it every minute.
There are also other options here with regards to the formatting and layout of the data you import.
3. Importing Data from a Text File
This is possibly one of the most common ways to import data since often data exported from other systems is saved as a .txt (text file) or a .csv (comma separated file).
A .csv file contains one page of text with little or no formatting. Each column of data is separated by a delimiter. A delimiter is a character like a forward slash, semi colon or it can even be a tab or a space which separates each column of data.
A .txt file is the same as a .csv file except the data is not always separated by a delimiter, however you may find that the data in each column is exactly the same width. This allows you to manually insert columns where you want. More on this later.
With both file types each new line of text is imported into a separate row in Excel.
How to Import Data from a .CSV File
You can either browse to the file location as you normally would (changing the view to file types Text Files *.prn; *.txt; *.csv) or you can go to the Data tab on the ribbon and select From Text and then browse to your file location that way.
When you've selected your file the Text Import Wizard will open.
Since I’m opening a .csv file I want to choose the file type as Delimited.
You can see a preview of the data you’re going to import in its raw form i.e. without any columns separating the data.
Click Next.
You can now tell Excel what type of delimiter is used in the file. Often you won’t know which delimiter was used but Excel will typically choose the correct one. You can change it by selecting another one, or more than one in step 2 of the wizard.
You can see in the preview pane above that Excel has now inserted columns into the data. Use the scroll bars to check for any anomalies.
Click Next when you’re happy with the delimiters.
In step 3 of the wizard you can tell Excel what type of data occupies each column.
In turn select each column by clicking on the heading in the preview pane (the selected column will be highlighted in black). Then select the data format from the Column data format box.
Tip: Make sure you set the correct format for date columns and set any number columns to General.
Click Finish and tell Excel where you want your data inserted.
Note: if you open the file, as opposed to importing it through the Get External Data group on the ribbon, when you save your file Excel will give you an error message and ask you if you want to change the file type to the latest Excel format. I recommend you choose 'Yes' otherwise any formulas, formatting or additional sheets you insert while working with the data will be lost.
Opening a .txt File Without Delimiters
If your .txt file doesn’t have a delimiter but the text is still in a tabular format you can choose Fixed width in step 1 of the wizard.
Click next and Excel will allow you to insert columns where you want by clicking anywhere in the data preview pane:
You can then move the lines by clicking and dragging them into place.
Or delete them by double clicking on them.
Be extra careful to use the scroll bars in the preview pane to check for any data that has been truncated by your columns. This is particularly common with numbers.
Step 3 is the same as stated earlier.
4. Importing Data from Other Sources
This is where you connect to an external system, for example your finance department’s accounting software. You will need to contact your IT department to co-ordinate importing data from these types of systems as they typically have security in place to prevent access to the data.
Liz says
Hi,
I am exporting data from a software program that only will save in .txt format. Sometimes the data rows have been split in two, perhaps by a character or return in the original data line. As a result I get rows as follows:
1 Abs Drab 1234 Teeks Road Maryville
22 Abs Fab 34 Weeks Road Maryville
34 Abs Tab 12 Weeks
Road Maryville
94 Abs Hab 123 Weeks
The .txt file shows the data like this so it doesn’t matter how I import it into Excel, I will still have the problem.
Is there a relatively simple way you can think of that will get the 4th line back where it should be? The line return is never uniform and can sometimes be from the middle of a cells text or can spread a record over more than two lines.
Thanks in advance.
Mynda Treacy says
Hi Liz,
You can use Excel’s Power Query tool to get data from a text file and clean it before importing to Excel. Power Query is the new ‘Get & Transform’ tool from Microsoft.
However, you need to be able to apply some logic to the data to help Power Query identify what rows have been split from the row above. For example, if you can always assume that a row that doesn’t begin with a number must be part of the row above, then you can use this to identify the split rows and join them back together again.
It’d be best if you can post a sample file on our Excel Forum for Power Query and we can take a closer look at the data to see if it would be a candidate for cleaning with Power Query.
Mynda