Ms Office Tune Up

In order to import from an Excel file, your data should be organized in a basic table. Ideally, you have column headings that match the fields in your database. You should trim out any data that you don’t want to import (like other cells under the table that aren’t a part of the table). You should also remove values calculated using Excel formulas. (As you learned in Section 2.5.5, you shouldn’t store calculated values in a table, because they introduce the risk of inconsistent data.)

Once you have a cleaned-up table of data in an Excel file, you’re ready to start the import process:

1. Choose External Data Import Excel, choose your Excel file, and then specify how you want to add the imported information to your database. Then, click OK.

   You learned how to make these decisions in steps 1 to 3 in Section 10.3.2.

2. Choose the worksheet that houses your data (Figure 10-5).

   Excel files, or workbooks, begin with three worksheets. Most people plop their data on the first one, which is initially named Sheet1. If you’re an Excel expert, then you might have designated a section of a more complex worksheet as a named range. If so, you can pick that named range from the list.

3. Click Next.

4. If your Excel data has a row with column headings, then choose First Row Contains Column Headings.

   These headings become the starting point for your field names. If you don’t choose First Row Contains Column Headings, then Excel treats the first row as an ordinary record.

5. Click Next.

   If you’re creating a new table for your imported records, then Access asks you to configure the fields you’re creating. If you’re appending the records to an existing table, then skip ahead to step 7.

   
   Figure 10-5. This Excel workbook file has the standard three worksheets: Sheet1, Sheet2, and Sheet3. When you make a selection, you see a preview of the data.

6. For each field, you can choose a field name, the data type, and whether or not the field should be indexed (Section 4.1.3). Then, click Next.

   Access makes some intelligent guesses based on the data that’s there, but it’s up to you to fine-tune the details. For example, if you have a column with whole numbers, you may want to change the data type from Double (which supports fractional numbers) to Integer, as shown in Figure 10-6.

7. Choose whether you want Access to create the primary key.

   Choose “Let Access add primary key” if you’d like Access to create an autonumbered ID field (which is generally a good idea). If the data you’re importing already includes a field you want to use as a key, then select “Choose my own primary key”, and then pick the right field.

8. In the Import to Table text box, type the name of the table you want to create or add your records to.

   
   Figure 10-6. To configure a field, select it in the preview, and then adjust the settings. If you decide you don’t want to import a field at all, then you can choose “Do not import field” to ignore it altogether.

9. Click Finish to finalize your choices.

   Once the import’s complete, you can choose whether or not to save your import steps for reuse.

You’ll find some potential stumbling blocks when importing data from Excel. Blank values and fields, the commonest problems, occur when the Import wizard assumes there’s data in a part of your worksheet that doesn’t contain any information. (This could happen if there’s a cell with just a space somewhere on your worksheet, or even if you have a cell that used to contain data but has since been deleted.) After you perform your import, you may need to clean up your table to fix problems like these by deleting empty fields and records.

Popularity: 10% [?]