Click here for great value web hosting.

ALL ABOUT OFFICE

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT




Search for
 

help


EXCEL

Importing Data

Sometimes you may want to bring information from another application into an Excel spreadsheet.  One of the easiest ways to import data is to copy a table from a Word document or a database query and then paste the results into your spreadsheet.  To do this, you simply need to highlight the area of the table you want to copy and press CTRL and C together to copy it.  Now click in the cell at the top left of where you want to paste the data and press CTRL and V together to paste.  Excel will work out how many cells are needed to paste the data and the contents of each cell of the copied table will be placed in the corresponding cell in your spreadsheet.

For larger amounts of data it is easier to use comma-separated or tab-separated values in a data file.  Such a file might look like this:

Comma-Separated

Forename,Surname,Company,email

Andrea,Jones,Allaboutoffice,webmaster@allaboutoffice.co.uk

John,Smith,Smith’s Buses,john@smithbus.com

Tab-Separated

Forename Surname Company email
Andrea Jones Allaboutoffice webmaster@allaboutoffice.co.uk
John Smith Smith's Buses john@smithbus.com

If using a comma-separated file you need to ensure that any text data containing commas (e.g. 14, High Street) is placed within inverted commas “14, High Street” within the file so that the comma within the data item is not confused with the commas separating the data. The inverted commas are called ‘Text qualifiers’ and it is important to let Excel know if you are using qualifiers.

When you have your data file to import (normally as a .txt, .csv or .dat text file) you can click on Excel’s Data drop-down menu and choose Get External Data and then Import Text File.  A window appears as below where you can select the text file you want to import, we are going to import the comma-separated file called contacts.txt.

Click on the name of the file and then click the Import button.  The Text Import Wizard window will appear as below:

Check that the correct type of file is selected at the top of the window, if you are using comma-separated or tab-separated values then you need to select Delimited, if you have data that is padded out with spaces or zeroes so that a fixed width of characters holds a field’s data then you need to choose Fixed width.  If you don’t want to import the whole of the data you can set the Start import at row box to start importing further down your list.  Click Next when you have checked these settings and you will be taken to the second screen of the Text Import Wizard.

Enter a tick in the appropriate Delimiters box to show what character is used to separate your data.  If you have used your own custom character, e.g. |, you can tick the Other box and enter this character in the box to the right to show which character you are using. Check that a Text qualifier is set if you need one from the drop-down box on the right. A preview of your imported data is shown in the bottom of this window shown above so you can see whether it looks as you would expect.  When you have set your delimiters, click the Next button and you will be able to set the format for your imported data.

To set the format, click on each of the columns in the preview section shown above and set the data format from the list at the top right of the window.  For our data we can set all columns to Text but if you are importing dates it is a good idea to format them at this point.  If you click on the Advanced button you can choose whether you want different separators for decimals and thousands in any numeric data.

When you have set the data type for each of your columns, click the Finish button and you will be asked where your data should be placed.

If you want the data to be placed in the current worksheet you should not have to do anything.  If you want the imported data to be placed further down the worksheet then you can click on the right hand end of the box underneath the words Existing Worksheet shown above and select the top left cell of the area where you want the imported data to be placed. If you want your data to be placed in a new worksheet, click the bullet beside New worksheet.

By clicking on the Properties button in the window shown above you can set your worksheet to update itself from the text file or other data source.

By clicking on the Refresh every box and setting a time in the minutes box you can make Excel refer back to your text file and update the spreadsheet if the data has changed.  This facility is normally more useful when you are importing data from web sites or databases.

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk