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.
Question: I have a number of Excel spreadsheets and Access tables with similar data that I would like to merge into one large database to make queries and reports. What is the best way to do this?
Answer: This is a more complex question to give a quick and simple answer without knowing what the data looks like. Basically, if the data has the same headings (fields) and field types, it should import well into an Access table. In looking and this user’s data, even though the headings were similar, they were not exact the same. They needed to modify the headings to be sure they were the same in all tables involved. Then the Excel files can import into the master Access table. If the old tables are in Access, you create an Append action query and select the old table with the fields you wish to append to the new master table. You may want to unselect primary keys in case ID fields are used in individual tables. You can then use a Query Wizard to search for duplicates or unmatched data in a table. In many cases, however, more one-on-one help is required to assist users when moving data from one application to another, especially if it involves several data sources.
Question: I’m trying to count only unique records in an Access table and can’t seem to figure it out.
Answer: You need to create a query containing the fields that are duplicated. (If there is just one record with an item in a field that is different that will consider that record unique). Then in the query options (right click in the light gray area of the query) select Properties and change Unique Values to Yes. Then run the query and it should show the unique records, from which you can click the Total button on the Home tab of the ribbon and select Count from the bottom row of the query results screen. We will show this query example in our Queries part 1 class on Thursday, July 21 from 10 a.m. – 12 noon.
Question: I my database I would like to fill multiple cells with the same info. In Excel there is that lovely function where you highlight all the cells you want to fill the same info and do a fill right or fill down select and it’s like magic. I am not finding the easy way to do this in Access but there has to be one.
Answer: That feature is not really available in Access, although you can create autonumber fields that will automatically add incremental values. But if you want to fill in a table with repeated values, you must add the field individually or create an update query. You can however, save typing by pressing Ctrl ” to repeat the contents of a field in the previous record.
Question: Can I convert my Excel data into an Access database?
Answer: Yes, it is very easy to import Excel data into Access, providing it is set up correctly. Row 1 of your Excel worksheet should contain unique column headings and should not contain special symbols and column headings should not have wrapped text. Your data should be entered below the headings with no blank rows or columns within the dataset. Any cells with formulas will be imported as values. Close your Excel file and open Access 2007:
- Open an existing database or create a new blank Access database.
- Click the External Data tab in the ribbon and in the Import category, click Excel.
- Click Browse to search for and select your Excel file.
- Specify how and where you want to store the data in the current database. If you’re just wanting to create a table from your excel spreadsheet, select the first option and click OK
- Select the Sheet (or named range) and you’ll see how the data will import. Click Next.
- Check if your top row contains column headings. Click Next
- Look at each column and check if you wish to change column headings, data type or not to import. Click Next.
- Select the primary key option (unique field which identifies each record). Click Next
- Name your table. Click Finish.
- You may save your import steps if you plan to import the same file more often.
Your Excel worksheet is now an Access Table. Anything changed in Access will NOT be updated in Excel worksheet. If updated in Excel, it will NOT be updated in Access. It is totally separate.
If you choose to Link your Excel file into Access, changes made in Excel will automatically be updated in Access. However, you will NOT be able to make changes in Access. It will be used for creating filters, queries, forms and reports.
To export Access into Excel, select table you wish to export and click the External Data tab and in the Export group select Excel. Give it a filename, check Export and I like to see the data after it exports, so click Open the Destination file after the export operation is complete. With just a few clicks, you will see your Access table in Excel. It is much easier to email someone an Excel spreadsheet rather than an Access database.
For more information about Access, come to the ITS Access classes. See the schedule at http://www.unmc.edu/its and click on the Classes/Training link.