databasedev.co.uk - database solutions and downloads for microsoft access

Import Excel Data into Microsoft Access

Creating a new table using an Excel file

As I became familiar with Access, I was very pleased to know that you could import existing information to use in Access. This saved retyping and editing time. In most cases, it is easier to use Access to store, retrieve, manipulate, and report data than other applications. This article will explain, in a step-by-step format, how to create a table by importing an existing Excel file.

Importing from Excel (.xls)

Prior to importing from Excel it is necessary to eliminate all of the formatting in the file. This includes, but is not limited to, centering, bold, underlining, etc. Also, ensure that there are no spaces in front of the column headings. By eliminating the spaces, you prevent importing errors and column heading issues so Access can recognize the headings. Below, you have the step-by-step guidelines for creating an Access table using an Excel file.

  1. Save then close the .xls file.
  2. Next, go to "New" on the "Tables" tab.
  3. Click on "import table."
  4. Change the "files of type" to Microsoft Excel.
  5. Navigate to the "Excel" file that you would like to import.
  6. Select the worksheet or named ranges you would like to import.
  7. In the bottom half of the "Import Spreadsheet Wizard" dialog box, you will see the information that will be imported.
  8. Click "Next."
  9. If you have put the column headings in the Excel file, they are transferred to Access. If the first row of your data contains the column headings, check the box "First Row Contains Column Headings". If your first row of information does not contain column headings, do not check the box.
  10. Click "Next". You will see that the first row of your data is grey and will be the column heading for your table.
  11. To create a new table based on the data you are importing, click on "In a New Table". If you would like to import the information into an existing table, click on "In an Existing Table" and use the drop-down box to select the table name.

    Please understand that you will be adding the information to an existing table daily. Please ensure that you want to do that. Instead, you will be taken straight to the last step of the wizard to click on "Finish."
  12. Click "Next."
  13. Select the indexing options for each field by clicking on the field heading to highlight the column then go to the "Indexed" field drop-down box and determine whether or not you would like to allow duplicate values (allows fields on different rows to contain the same information), to not allow duplicate values (fields on different rows must contain unique information), to not index the field.

    Indexing the field allows the database to run faster. If you do not want to import a column, click on the column heading to highlight the entire column, then check "Do not import field (Skip)." You can adjust the data types after the table has been imported.
  14. Click "Next."
  15. At this point, decide if you want Access to add a primary key, if you want to choose your own primary key, or if you do not want a primary key. If Access creates a primary key, it will create an ID field which numbers your records. If you choose your own primary key, select a column that does not contain duplicate fields. For example, an invoice number field cannot contain invoice 12345678 twice in the same column.
  16. Click "Next."
  17. Type the name of your table into the "Import to Table:" field. If you do not want to overwrite an existing table, choose a name that is not currently being used.
  18. Click "Finish."
  19. Once you have successfully imported the file, a dialog box will display that reads: "Finished importing file [filepath] to table [table name]."

You can go to the table’s design view to format the data types, field sizes, create drop-down boxes, index fields, etc.

Related Products:

4TOPS Excel Import for Microsoft Access - The easy way to export data from Microsoft Excel to Access.

4TOPS Excel Import Assistant is an add-in for Microsoft Access. It enables you to import data from Microsoft Excel into Microsoft Access in a controlled way. Moving row after row through your Excel table(s) you can instantly fill in missing data or alter erroneous data before transferring them to your database. The Excel Import Assistant is a time saving alternative to either writing an ODBC query on Excel as a data source or retyping all the data.

4TOPS Excel Import Assistant is useful in the following situations:

  • You have to convert Microsoft Excel to Microsoft Access.
  • You are currently working in Microsoft Excel and you want to export Microsoft Excel to Microsoft Access.
  • Import multiple files or sheets in one go.
  • You obtain data from different sources using mail and Microsoft Excel attachments.
  • Replace or update data in Microsoft Access from Microsoft Excel.

Download a 30 day FREE TRIAL of 4TOPS Excel Import Assistant