Importing Excel Data Into Access

Wed 29th December 2010

Although Microsoft Office applications interact very well, there are some operations that you might expect to be possible that actually aren't - such as pasting Excel data directly into an Access database. Instead the data must be prepared and then imported - and here's how.

Incompatibility causes misery wherever it occurs. When your new girlfriend calls to break things off because she doesn't share your passion for train spotting in January, that's incompatibility. Back in the fledgling days of the home video player, that night in watching a new release was scuppered when it was discovered that the tape was VHS - completely incompatible with the Beta Max player that sat beside the television. And a more current example of incompatibility is that you cannot play a Wii game on the X Box 360.

So it is pleasing to know that within Microsoft Office compatibility is king. You want to embed an Excel worksheet into a Word document? No problem. You think that Excel pie chart might add visual impact to your PowerPoint presentation? Consider it done. You want to use Word as your email editor in Outlook? You're only a few keystrokes away. Yes, Office applications really do get on very well with each other. It's like the high-tech equivalent of an episode of Friends.

Among all of this camaraderie, however, there are some inter-application tasks that cannot be performed as you might imagine. For example, one thing that you cannot do in Office is copy your Excel data and paste it directly into an Access database. This does not mean, however, that they are incompatible; it's just that Access must import the data rather than it being pasted. But this is a straightforward process as you can see below.

To import your Excel data into Access you must first open your Excel worksheet to make sure that the data to be transferred meets the following criteria.

1) The data is in list format - i.e., columns and rows and not in the form of a chart.

2) Each column has a label,

3) There are no blank columns or rows within the data to be exported, and

4) Your first label is in cell A1.

If you have ticked all four of these boxes then close down the worksheet. If you do not have some sample data that you could use to try this out, just fill a few rows with names and numbers and save it. But, and this is very important, your columns must contain similar data. You should not have a mixture of names and numbers within the same column so even if you are just knocking up a quick random list be wary of this rule. Close down your Excel worksheet and move on to Access.

For the purpose of this demonstration it would probably be best to create a new database but if you wanted to import Excel data into an existing database then you would simply open that one. So with your database open and ready to import, here is what you should do.

For pre-2007 versions of Access, go to the File menu and select Get External Data. From the options that appear click on Import and this will open up a dialog box. In the Files of Type box select Microsoft Excel and in the Look In box, browse for the file you wish to import and double click it. The Import Spreadsheet Wizard will offer you a few prompts and your Excel data will now be waiting for you as an Access database.

For Access 2007 and beyond, open your database, select the External Data tab from the ribbon and click on the Excel icon. In the File Name box browse for the Excel file you wish to import and click on it. Check the radio button to Import the source data into a new table in the current database, and click OK. Again the Wizard will activate to guide you through the rest of the process.

This is just the straightforward import of basic Excel data and there are many more aspects to it, some of which I will cover in detail in another article. But I hope this introduction has shown you that, although you can't copy and paste directly from Excel into Access, the ease of use when importing demonstrates that these two Office siblings really are quite compatible.

Author is a freelance copywriter. For more information on excel training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1335-importing-excel-data-into-excel.html