Importing tables

- [Instructor] If you already have information stored in a file such as an Excel workbook, then you don't need to retype to get it into Access. You can use the tools available to you on the External Data tab in order to pull that information in. You'll notice that we have lots of different file types that we can bring into Access underneath the New Data Source button. We can bring information in from an Excel file, or an HTML document, or an XML file. We can also bring it in from other Access databases, or from SQL server databases. So let's bring in an Excel file to replace the guests table that we began to create in the prior movie to this chapter.

The Excel file that I'm gonna bring in has additional fields that we didn't create in the original, so adjust fields for the address. It also has details on about 200 guests, and I don't want to type all that information out manually. Instead of creating these fields, and then importing the data, we can create the entire structure of the table and import the data, all in one step. First though, we need to get rid of the existing guests table. To do that, right click on it in the Access navigation pane, and choose Delete. It's gonna prompt me to make sure that I do want to delete this table. Go ahead and say yes to that.

Then we'll come back to the external data tab, point to New Data Source, then from file, and then click on Excel. Now the first thing we need to specify is the file that we wanna bring in. Go ahead and press the Browse button to bring up the file picker. Then I'll go ahead and go into my Chapter 2 folder, and choose the Guests Excel file. Press Open and that file path gets added here into the wizard. Then we have three different options down below. The first one is what we're going to actually do, the "import the source data into a new table "in the current database", but we can also append a copy of the records into a table that exists already in our database by choosing it from a dropdown menu there, or we can link to the data source by creating a linked table.

This third option would keep the data inside of Excel, but allow us to use it inside of our Access database. Now I actually want to bring the data into the Access database itself, so make sure that the first option is chosen and press Okay. Then we get this Microsoft Access security notice. It just wants to make sure that our Excel file is from a trustedworthy source, and I know that it is, so go ahead and press the Open button. That'll start up the import spreadsheet wizard. Now inside of our Excel workbook, we have a worksheet here called Guests, and that's actually where our data lives, but we could also choose from named ranges if that's how our data is organized.

Make sure that the Show Worksheets option is checked, and that we have the Guests sheet selected. Go ahead and press the next button. Inside of the Excel workbook, I also already have the column names listed as the first row of data, and we can see that represented here. Make sure we turn on this box that says, "the first row contains the column headers", and that will be imported as the name of our columns, and not the first row of data. Let's press the Next button. On this screen, we get to find the data types for each column of data. Now we're actually not going to import the Guest ID column, we're going to create a new auto number data type to represent the primary key for this data.

So for a guest ID, make sure you choose the box that says do not import field. For the others, I wanna make sure that I click through them, and make sure that the data type is listed as short text. All of these are imported correctly as a short text data type, so everything looks good. Let's go ahead and press the Next button. Finally, we're gonna let Access add a new primary key. Now if we just imported the primary key as the guest ID field, we wouldn't get the benefit of Access's auto numbering feature. By letting Access add the primary key as this ID column, instead of using the guest ID as it exists inside of the Excel file, we get the benefit of the auto number field.

So if we were to add in new guests, they would automatically get a new ID number assigned in this way. Make sure that Let Access Add Primary Key is checked, and press the Next button. Finally, we're going to create the new table here, and inside of our Access database, we'll call it Guests. It's just pulling the name of the Excel spreadsheet there. That name will work here for our database, so press the Finish button. Then finally, it's going to ask if I want to save the import steps, and I don't need to do that here, so I press the Close button. When it finishes, we have the new table here added to our navigation pane called Guests.

Let's go ahead and double click on it to open it, and we'll see that it correctly imported the columns names here across the very top, and we have about 200 records added for the guests of the Landon Hotel. So if you already have your data set up in Excel, the import tools will make it really easy to not only bring in that data, but also quickly create the entire structure of your tables. Just choose the type of file that you wanna bring in, and let the wizard walk you through the options. At the end of the process, you'll have your table set up and ready to go. And if you already have a table structure set up in Access with some data in it, it will use the append option in the wizard to add data to an existing table.