Import Excel Data Into MS Access

hello and welcome back to tips and time-savers I'm Danny rocks and today's lesson is the first in a series where I will demonstrate how to integrate Excel data with an Access database we're going to be taking banking transactions from two different sources our original set of transactions as well as a set that came from another source which contains newer transactions notice that while we're still here in Excel that we come to the data tab and we have a series of commands to get external data from access and some other sources but we don't have a set of commands for sending information directly from Excel to axis so our process will begin over here in axis where we have sets of commands to both import as well as export the data now before we begin this process let's take a look at what we're working with so over here in the banking transactions notice that our top row has the field headers it has the column headers and they are clearly identified as field headers because we set them off with bold formatting we've done the same over here with the new transactions now it's generally a good practice to close down the file that you are going to send to another program it's not necessary but I generally recommend it in today's lesson I'm going to keep this file open and now I'm going to switch over here to access so we have a new database over here in axis but we don't have any information in it yet our table information is going to come from Excel so on the ribbon go to the external data tab and in the import group don't confuse it with export in the import group we want to import information from Excel now we'll walk through the wizard notice over here that I have two choices I can import the source data or I can link to the original data source now we're going to link later on in the lesson so I'm going to choose to import and now let's identify where that in information is so it's in this directory but we want to make sure that we select the file that contains the information so now that we have that selected let's click OK now remember in the Excel workbook we have four worksheets so let's identify the worksheet that we want to import so we have date source branch type and amount now notice over here in the date field notice the serial numbers that we have here if I switch back here to the Excel worksheet you see that these are dates but remember that Excel stores a date as a serial number will format it to show it as a date but it's stored as a number now the other field that I want to draw attention to is the amount so the amount over here we want to make sure that it contains values and not formulas if you try to import an Excel worksheet that contains formulas don't do it that's a formula for disaster so convert them to values alright now our first row contains the column headers yes let's take the next step and now let's go through and define the data type so over here for our date it is going to be stored as a date or a time field the text fields for source and branch and type and then the amount will come in as currency and again remember it's a value and not a formula take the next step now here's a major difference between Excel and axis Access uses primary Keys Access uses Keys over here so that it can relate one table to the next let's take the default and let Access add a primary key to our table click Next and now what do we want to name it well by default it's going to take the name of the worksheet tab that we had so if it were sheet 1 it would bring it in as sheet 1 so we have banking transactions let's click finish and now notice over here that if we are importing information from this excel worksheet on a regular basis we could save this as a definition so with save as time and pretty much automate the process we're going to say this is a one-off transaction so we won't do it now let's come over here and take a look the table so here is the primary key that's been added notice that they're sequential numbers it's what's called an ADD Auto number and then we have our date and remember they were stored as serial numbers but over here the displayed as a date and we can change the format we have our text and over here we have our currency with two decimal places so coming down here we can see that there are 678 records to come down to the last record we click here there's 678 records and if we want to add a new record we can do that let's go back up here to the top and now let's come in to the design view for our access table so moving over here into design view we see that our new primary key is set up as to increment it's set up as an auto number and it is set up to be indexed so that there are no duplicates allowed now access adds this number automatically we do not in this case add that number now let's come back here into table view right mouse click and say let's go over here into datasheet view all right now let's close this and what we want to do next is we want to append our second Excel worksheet so we already imported the banking transactions what we want to do over here is take the new transactions and append them to the table that we established in Access so once again let's come over here into Access will again go to the external data tab of the ribbon we want to import information from excel now in this case we already have a table in our Access database so notice that we have a second choice over here we want to in this case append information from our data set to this table if I had additional tables they'd be in the drop-down once again let's go through and identify the exact file now I have these as separate worksheets but I collected them together into this work so over here I want to make sure that I'm working with the correct file click OK and once again we see the four worksheets that we have here so new transactions and again for headers over here click Next and now again the first row contains the column headers click Next where do we want to import it where we appending it to so now let's click finish and again we have the the dialog box over here that if we're doing this on a regular basis we could save these import steps as a definition so we don't want to do that now remember we have 678 records before we appended now we have 753 records so there you see how easy it was to append that information alright now let's go back and let's I'm going to demonstrate how we can link the information to the original source the Excel worksheet so over here what I'm going to do is I'm going to open up another worksheet I'm going to go to the office button and I'm going to come over here and as the separate worksheet I'm going to come down here to the new transactions this is the table that I'm going to link to access alright so now let's again return to access and this time again external data Excel and what I want to do is I want to link to the original data source which is the excel worksheet let's identify the source now this time remember that I'm linking back here to that separate worksheet to demonstrate how linking works so we're linking the data to the original source click OK and again let's just take all the defaults the information is identified correctly and I want to bring it in as a linked table called new transactions click finish now it's easy to spot when we have a link do you notice the difference between the banking transactions table and the new transaction so the new transactions has an icon demonstrating that it's linked to the original source so if I come over here and I have 75 records but notice that when we've linked to the original Excel source if I try to delete a record notice that it's grayed out if I try to add a new record I don't have that option because the data is controlled over here by the source which in this case is an Excel worksheet so coming back over here what I could do let's use control end to come down here to the bottom let's add in another transaction for June 4th and this time let's make it a teller and let's put it in Main Street and let's make it a CD certificate of deposit and let's make it for $10,000 all right now what we want to do is of course save this so now I'm going to save this and now what I want to do is I want to see if that information has been updated in axis so come over here into access what we want to do is we want to make sure that that record is now in there so in order to do that we're going to have to refresh the data so if I close this down and now open it up again now when we come back here we can see that we have 76 records so we had 75 before we refreshed it and I refreshed it a simple way I closed it and then opened it up again so there's that deposit for $10,000 so when we link to a source when we link to a source which is Excel we cannot make a change in the Access database we make the change in the source which is Excel and then we refresh the data when we're importing the information we have two choices we can import it with a link or we can import it and then we can if we have the data set up with the same headers we can append that data so there you've learned the first step in integrating Excel data with an Access database and I'll look for you in the next lesson

Channel: Danny Rocks

Views: 384,905

Rating: 4.7177615 out of 5

Keywords: Access Database, Import Excel Data into Access, Append data, link to data source, Field settings, Microsoft Access, Microsoft Excel, Software Tutorial, Educational, Office, Microsoft Office, Business, Training, Computer Software

Id: -aH56dxqLhg

Length: 10min 52sec (652 seconds)

Published: Sun Jan 02 2011