Learn IT

Question of the week of 1/17/11 – Importing Excel file into Access 2007

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:

  1. Open an existing database or create a new blank Access database.
  2. Click the External Data tab in the ribbon and in the Import category, click Excel.
  3. Click Browse to search for and select your Excel file.
  4. 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
  5. Select the Sheet (or named range) and you’ll see how the data will import. Click Next.
  6. Check if your top row contains column headings. Click Next
  7. Look at each column and check if you wish to change column headings, data type or not to import. Click Next.
  8. Select the primary key option (unique field which identifies each record). Click Next
  9. Name your table.  Click Finish.
  10. 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.

Leave a Reply