< Day Day Up > |
Excel is commonly used to build a workbook that performs a series of calculations and returns a result set for analysis. Whether you think about it that way or not, that boils down to Excel's core functionality. The convenient thing about leaving data in Excel is that the result set changes as you make changes to the data. However, that is also the inconvenient thing about leaving data in Excel. There will be times when you will want to take a snapshot of result data and not want to have it changed by new data. Consider the input parameters in Figure 4-1 and the resulting data in Figure 4-2. Figure 4-1. An Excel worksheet providing inputs that drive calculations on the amortization pageFigure 4-2. Result data that changes when you make changes to the inputs on the LoanInformation worksheetAssume that you want to load that data into a new table in Access, and you don't want it to change. In Access, create a blank database application by going into Access and selecting File New. Depending on the version of Access, you will either get a brand new empty database or a menu like you see in Figure 4-3. If you see the menu in Figure 4-3, click on Blank Database and it will bring up the dialog box that you see in Figure 4-4. Next, give the database a name. Figure 4-3. The new database menu that comes up in Microsoft Access 2003 when you select File NewFigure 4-4. The dialog box that comes up when you want to create a new database, similar to the Save As dialog box that you would see in other Office applications
Once you have done this, you will have a blank database as shown in Figure 4-5. Figure 4-5. A brand new blank database in Microsoft Access 2003, showing the Tables tab of the databaseNow you are ready to import data into this new database application. To accomplish this, right-click on whitespace when you have the Tables tab selected, and you will see the menu in Figure 4-6. On that menu select Import. Access brings up a File Dialog box like the one in Figure 4-7. In the Files of Type box, select Microsoft Excel and then the file that you want. Access brings up the Import Spreadsheet Wizard shown in Figure 4-8. You will notice that you can choose to bring in entire worksheets or named ranges. Using named ranges is very useful if you have data resembling tables throughout one worksheet. However, generally I try to keep data from tables on its own worksheet. In this example, the data needed is in the worksheet called Amortization, so to import it, click it and then Next. Figure 4-6. A context dialog with ImportAccess brings up the screen shown in Figure 4-9. In this case, check the box that says the first row contains column headings and click Next. (If you have headings and do Figure 4-7. The import file dialognot check this box, you run the risk of your data types being incorrect.) This brings up the next step in the wizard, shown in Figure 4-10, where you select whether you want to import the data into a new table or an existing table. Since this is a blank database, select In a New Table and click Next. The next step in the wizard, shown in Figure 4-11, allows you to select columns and either change the name or choose not to import it.
The next step of the wizard allows you to either pick the field that is the primary key or select the option that there is no primary key. Normally, I let Access add the primary key. However, since you won't have two payments with the same number in this example, you can select Payment as the primary key, as shown in Figure 4-12. The final step in the wizard, shown in Figure 4-13, is to give the table a name. The table will default to the name of the worksheet or named range. I prefer to place the identifier "tbl_" in front of table names, "qry_" in front of query names, "frm_" in front of form names, etc. So my suggestion is to name this table tbl_Amortization. While this may seem like a lot of steps, the wizard makes it very quick, particularly if you accept the defaults. Also, you will find this to be a welcome change if you ever had to enter the information already in a spreadsheet into a database. Figure 4-8. The first step of the Import Spreadsheet Wizard, when you can select importing worksheets or named rangesFigure 4-9. The second step of the importing process, when you can select whether the first row contains the column headingsFigure 4-10. The third step, when you select whether you want the spreadsheet imported into a new table or an existing tableFigure 4-11. The step when you can change field names and choose whether you want to skip importing a particular fieldFigure 4-12. The step when you choose which field, if any, to use as the primary key of the tableFigure 4-13. The final step in the wizard, when you choose the name of the table and have the choice of two options for Access to help you and/or analyze your new tableThe other benefit to importing data into a new database is that you don't have to go through the grueling process of creating a table using Design View. Access uses the information that you load to determine whether the field should be text, integer, double, currency, etc. This feature can be a real time saver if you have many fields. Sometimes when I am creating a new database, I mock up a few records in Excel and import the spreadsheet just to save time creating and naming tables. Also, if you are working with database novices, they are often more comfortable in Excel. If you need to obtain data from someone who is not familiar with Access, you can give them an Excel worksheet and have them type in the data using the Excel user interface where they feel comfortable. Then you can take the spreadsheet and import the data into your Access database. 4.1.1. Using Excel to Ease ImportingAs you saw in Figure 4-10, you can import data into an existing table. Unless the spreadsheet was designed to work with the existing database, however, you will often work with database tables that may not have the same field names (columns) and data types. For example, a cost center in Excel might be a number without leading zeros , while the same column in Access might be text and expect leading zeros. You can deal with this in Excel. Create a new worksheet and references to the cells that you need on the original worksheet. Once you have done that, create formulas to modify the data so that the field names and data types match those in Access, and save the workbook. Now it is as simple as importing the new worksheet from Access.
Another easy way to put data from Excel into Access is to use the Copy command in Excel and then, with the table open in Access, use the Edit Paste Append command from Access. (If you attempt to use the paste command, Access gives you an error message.) You might find that Paste Append still gives you errors. However, Access does create a table of the paste errors for you to review. You should carefully review the errors to make sure that your tables are not too restrictive. For example, you might find a field in the database listed as required when there are times it is OK for it to be blank. In addition, you also want to determine what is more important, having some of the data for all of the rows or having all of the data for some of the rows. I have been asked to work on many projects where data integrity was an issue, and things like missing customer records, missing billing records, and so forth were problems. Often, there were data issues causing records to not load. While these issues could have also been found by reviewing a log, it is sometimes easier to loosen restrictions so that you can load the data you have and then use exception reports to find out where you need to fill in the data gaps. You can also end up with unexpected logic errors when importing or pasting data from Excel into Access. In some cases, Access performs the import without noticing the error. This is a particularly large problem when you inadvertently load a number with decimals (double) into an Integer field. When importing data, some common errors to look for are:
If you have successfully addressed the issues, this type of integration with Access and Excel can lead to significant savings of data entry time. Keep in mind that if your Excel workbook's primary function is to provide data to a database, it should be designed with ease of importing in mind. If, on the other hand, the database is secondary to the form and functionality of the workbook, I suggest creating an extra worksheet linked to the primary worksheet in the workbook that will be used specifically for importing/exporting data. |
< Day Day Up > |