x

How to Import Excel Spreadsheets Into Access

by Morris Wall

Many spreadsheet users take advantage of the database features in Microsoft Excel when working with their spreadsheet data. Simple search, sort and filter functions are available using the "Data" menu within Excel. Microsoft Access is a full-featured, relational database that is compatible with Microsoft Excel. Importing Excel spreadsheet data into Microsoft Access provides several benefits, including improved data input, multiuser access and advanced reporting options. Excel column data labels are converted to field names, column data is converted to fields and each row is converted to a record during the import process.

Step 1

Save your spreadsheet data in Excel using the "File," "Save" menu. Although Access recognizes a range of alternate formats, including dBASE and text delimited, it is best to use the native Excel format for your saved file. Access is configured to import Excel files directly and provides options for continuing to work with the data in both products after the import process.

Step 2

Start Microsoft Access and open the database into which you want to import your Excel data. You can create a new, empty database in Access by clicking "File," "New" and selecting the "Blank Database" option. Provide a name for the file when prompted. Click "File," "Get External Data," "Import" and select the Excel format. Locate your spreadsheet subdirectory and import your spreadsheet into Microsoft Access. If you are using Microsoft Access version 7, you can also click the "External Data" tab at the top of your screen and select "Excel" as the format to import the spreadsheet data. You can append the data to an existing table or create a new table for your Excel spreadsheet data.

Step 3

Select the worksheet or range you want to import from the Import wizard and click "Next." Select the Excel column headings you want to import as field names and verify that you want to create a new table or append data to an existing table. Click "Next" to continue. You can change field names, skip fields that you do not want to import and select indexing options, including a primary key for sorting your spreadsheet data in Access. Click "Finish" when you are satisfied with your selections.

Review the Table Analyzer wizard results to verify the structure of the database tables that Access has created from your Excel worksheet. The wizard will divide tables containing duplicate information into separate tables so that each category of data is stored only once. Verify field placement within tables, select the primary key used for sorting and correct any typographical errors prior to completing the import process.

Tip

  • Worksheets that share a consistent structure and purpose can be imported into a single table. Worksheets with inconsistent data or table structures should be imported into separate tables.

Warning

  • Eliminate any empty rows in your Excel spreadsheet prior to importing it into Access. Empty records can generate errors when calculated using functions in Microsoft Access.

Video of the Day

Brought to you by Techwalla
Brought to you by Techwalla

More Articles