98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Importing Excel Data Into Access
Wed 29th December 2010
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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsNucleus Global
Sr Scientific Director Judy Devoti Intermediate Manager Skills - Part One The course was very engaging and provided a nice framework to evaluate my relationship to the people who report to me. The pace was perhaps a little too fast to allow participants to fully explore some of their experiences with the group Australian High Commission
Facilities Officer Gordon Hannon Excel Introduction Excellent course. Professional presentation and depth of knowledge of subject is impressive. Basildon Excellence Cluster
Business Manager Gail Cottage Excel Intermediate What a fantastic course!! Clear, concise and to the point. Fast paced but perfect. Trainer exceptional. Booked via live schedule, fantastic value for money, full days worth of superb training, BEST certainly stands out from the other training providers I have used in the past. Would most definitely recommend. Thank you very much! |
PUBLICATION GUIDELINES