import data into an Excel worksheet

import data into an Excel worksheet

If the data is already in some sort of row-and-column format, you can import it. Excel accepts imported data from Word tables, delimited text files, Access databases, or any of a variety of other formats. Let's talk a minute about preparing each of these for the import:

• Word table: If your data is in a Word table already, great. To prepare it for import into Excel, delete everything from the file except the table itself. Make sure the field names are in the top row of the table; add a row if needed.
• Word (not a table): If the data is not in a table, it's best to convert it to a table in Word before importing into Excel. One way to do this in Word is to select the text and then choose Table > Convert > Text to Table. Note that this works only if you have a consistent delimiter character in use. (See below.)
• Delimited text file: A delimiter character is one that separates the data to show breaks between fields. For example, in a comma-delimited text file, a record might appear like this:

Notice in this line that each of the text strings between the commas is a separate field. In a tab-delimited file, the tab character is used instead of the comma. To prepare a delimited text file, scan it to make sure there are no missing delimiters and that each line has exactly the same number of them.

TIP
One way of checking your delimiters in a text file before importing it into Excel is to open it in Word and use Table > Convert > Text to Table on it. If you get the right number of columns -- and the correct data in each column -- the delimiters are okay. If you get more or fewer columns than you expected, your delimiters are somehow off. Note that you shouldn't use spaces with delimiters.

• Access: An Access database file may contain multiple tables, so make sure you know the name of the table you want. Open the file in Access and browse to confirm the name if needed.

After checking the data to confirm it is Excel-ready, import it as follows:

• To start a new workbook based on the data, open the data file as you would an Excel file, with File > Open. Change the file type to All Files to find it.
• To import the data into an existing workbook, open the existing workbook and choose Data > Import External Data > Import Data.

When you import data, a wizard runs to guide you. The wizard and its steps are different depending on the data type you're importing, so we won't go specifically through the steps here; try it out on your own. You also get to practice this in the assignment for this lesson.