Spreadsheets

When you import a spreadsheet file, each spreadsheet column becomes an Access table field:

• The first row in the spreadsheet (the column headings) becomes the field names.

  If you want to use the first row for field names, you must check the First Row Contains Column Headings check box during the import process.

  An ideal spreadsheet for import will have field names in Row 1.

• Each following row becomes a record in the Access table.

  An ideal spreadsheet for import will have data starting in Row 2.

When you import data from spreadsheets, watch for these quirks:

• Double-check spreadsheet data to be sure that it’s consistent and complete.
• Make sure that all entries in each spreadsheet column (field) are the same data type (numbers, text, or whatever).
• Make sure that the first ten rows of the spreadsheet contain data so Access can correctly recognize data types.
• Remove titles and blank rows from the top of the spreadsheet.

• Make your spreadsheet column headings short and unique.

  Shorten your spreadsheet column headings to the field names you’d like Access to use so you don’t get scolded during import about field name issues.

• If you’re adding data to an existing Access table, make sure the spreadsheet columns are of the same number and in the same order as the Access table fields.

  Your spreadsheet columns and table fields must line up exactly.

Text files

If you have difficulty importing a format (such as an Excel spreadsheet) into Access, you may be able to import the data as text. Text is the most widely recognized form of data known to man (or computer). Try these steps to import the data as text:

1. Open the file with the existing software product (like Excel).

2. Use the existing product’s exporting tools or Save As command to export your data into a text file.

   A delimited text file is preferred if the existing product supports this type of text file. A delimited file contains a marker character (such as a comma) to separate each field from the next so Access can easily understand where one field ends and another begins.

3. Import the text file into Access (as shown later in this chapter).

Steps for importing

Here are the steps for importing or linking data sources to your Access database:

1. Open the Access database that will hold the imported data.

2. Click the External Data tab on the Ribbon.

   The Import & Link group of buttons appears on the Ribbon (see Figure 9-1).

   Each button is connected to a wizard that walks you through the process:

   • Common file formats such as Excel or Text (labeled as Text File) have their own specific buttons.
   • You can find the less common file formats using the More button.

3. Click the button that matches your file format.

   A Get External Data dialog box specific to the selected file format appears onscreen. (See Figure 9-2.)

4. Use the Browse button to select the data source that you want to import or link to Access.

   Usually this is a file. It could also be a SharePoint site or an Outlook folder.

   If typing filenames is not your thing, click the Browse button to locate the file.

5. Select the method of data storage.

   This is where you tell Access whether to import or link the data. You can link to all file formats except XML.

   The sidebar “Storing data from external data sources” shows the common data storage options.

6. Follow the remaining steps in the Get External Data dialog box.

   From this point forward, the steps depend on which data format you’re importing. Follow the prompts carefully. The worst that can happen is that you get an imported (or linked) table full of gibberish. If you do, check the format of the source file. For example, if the source is a text file and you get gibberish, you may need to confirm that the text file was saved as a delimited file (with a character — a comma, for example — placed between fields). It’s also possible that the source file isn’t in the correct format (for example, you may think it’s an Excel spreadsheet but it’s not).

   If you expect to import or link to this type of file often, click the Save Import Steps check box. (The check box will be located on the last screen of the wizard.) After you check the box, you’ll be prompted for a name for your import, as shown in Figure 9-3.

The External Data tab’s Import group on the Ribbon contains a button called Saved Imports. (Refer to Figure 9-1.) You can use this button to call up saved imports and run them as often as you like. (See Figure 9-4.) Data sources must remain in their original locations with their original names to rerun a saved import.

Troubleshooting

During the import or linking process, Access may have difficulties. If it does, you’ll know — either the import or linking process will take a noticeably long time to finish or Access will display an error message. This section describes some common problems that crop up during importing and linking — and how to fix these problems.