I have data in an Excel spreadsheet that I would like to use in an Access Database. What are the steps?
Importing or linking data from Excel to Access works well if the Excel data is in rows and columns without any subtotal rows, formulas or blank rows or blank columns within the worksheet or range. Column headings should not be wrapped or contain special characters and must be unique names. Then the data should import pretty cleanly by:
- Create a blank database in Access (or an existing database you wish to place it) and select the External Data tab > Excel (from the Import group).
- Locate the Excel file and select “Import the source data into a new table in the current database” or “link to the data source by creating a linked table.”
- Follow the screens until the process is finished.
If you have cells which contain formulas, they will import as values. When importing, the Access table is completely separate from your Excel file. Any updates made in Excel or Access after the import are not updated in both applications. However, if you “Link” the data, you can only update from the Excel file, but the data is automatically updated in Access so that it can be used for filters, queries and reports.
Keep in mind that just because your Excel data imports well into Access does NOT mean that your file is set up ideally to work in Access. You may have to “normalize” your data – add fields, split the data into more tables, add primary keys, add validation properties, etc. In addition, Microsoft Access requires a considerable investment in time to learn how to use it, compared to Excel. But Access works well if you have a lot of data in separate but related tables to analyze. To learn more about database design concepts, come to ITS’ mini-session on Thursday, 11/29 from 2-3 p.m. in the ITS training room, 8011 Wittson Hall. Additional Access training is available in December. Click here for the ITS class schedule and on-line registration.