It is usually the case that when creating a new Access database, data already exists. It may be that a company has many different spread sheets where they store contact details and sales information. When this is the case it often becomes necessary to put this information into a database so more advanced reporting can be created as well as other benefits a formal database can provide. With this situation a database will be designed and created, but a user will not want to manually add individual records into the database.

This is not the only situation when large amounts of data need to be imported rather than added individually. A company may have purchase another company and wish to add those new records to their own database. Sometimes a company, for example a market research firm, buy large amounts of data such as valid contact details so they can cold call the public. It would be time consuming and problematic to add each record individually. Instead a solution is required where the data is imported directly into the database on mass to save time and therefore money.

Microsoft Access easily allows data to be imported from several data sources. In this example we will use the Excel data importer. The wizard offers simple steps and once the import is complete the user can save the import for use at a later date. This means that if regular imports are made, you can have a file in a particular location and hit the import button to automatically import everything. The same goes for exports. This would allow a user to save each new import file in the same place under the same name so the import can happen automatically.

To begin the import, go to the External Data tab on the ribbon. Click the Excel button to bring up the first step of the wizard. There are three options, importing into a new table in your current database, adding the records to an existing table or creating a linked table. You will be given a chance to select the worksheet you want. Many data imports are from worksheets with lots of different tabs containing all sorts of data. If there are headings on the sheet then you can use the first row as headings so that the headings row does not import into the table as data.

When importing as a new table, access will ask you to give information on each field such as the data type. If there is some redundant information that is not required then you can flag the field not to import. If for example someone has given you a data dump from a CRM database that contains contact information including the email field which will not be used in the table you are creating then you can skip the field. This also means that you can use the same spread sheet to import data more than once, creating a different table for different data as you import. In this way you have the flexibility to import data into existing tables or import it into a new table created at the time of the import.

Once the data has been imported, Access can automatically analyse the database to show if there are duplicates and whether the information is set up in the correct way. Using the import and analyze tools, a user can create a workable database as they go. This negates the need to spend time designing and individually creating tables. Create the tables via the import and then analyse. Obviously for more complex databases this process will be more complicated, but as a start it may well be worth a try!

As well as importing data from an excel spread sheet, Access can import from various other data sources such as a text file, Outlook or another Access database. Each data type has its own wizard allowing the user to manage the import as simply as possible. The best thing to do is to examine the data that needs to be imported. Decide on the type and attempt to import it into a test database environment. Once you are happy with the results you can then import into the live database.