Importing Data into Access

Importing data into Access can be like trying to re-enter the United States after traveling overseas. Sometimes the process goes so smoothly you wonder what you were worried about. Other times, there seems to be an officious agent standing at the gateway of your Access table, making it as difficult as possible to let any data in.

But there's good reason for the intrusive inspection. When it comes to the domestic security of any nation, it doesn't make sense to worry exclusively about internal threats and be indifferent about who is entering your country. Similarly, it makes little sense to put tremendous effort into maintaining data integrity for values you enter directly into Access yourself while waving through any piece of data from an external source. Indeed, data from outside your computer could be especially likely to break validation rules, exceed field sizes, have the wrong data type, and otherwise breach data integrity.

Thus, it's essential to do a through review of the external data before you import it into Access. Even with a careful inspection of the source data, you might find that you have problems importing it. The exercises in this section should illustrate a few of the data integrity issues you might encounter.

Text Files

As noted earlier, a delimited text file (in which the data is not formatted) is useful for transferring data between programs. You might find that the data you want to import into Access exists in files of this type.

Import a Delimited Text File to an Existing Table

Of course, the absence of formatting eliminates just one of the issues in importing data. As the following example shows, it is sometimes difficult to anticipate the problems you might have in importing data into Access into an existing table.

1.	Copy (i.e. download) DelimitedFieldNames.txt and DelimitedAllDigits.txt to a convenient folder on your computer. NOTE I ask you to copy other files at various points in this chapter. You might want to copy all of them at this point: Assets.xls, ShipperErrors.xls, and EmployeesLink.xls.
2.	Open DelimitedFieldNames.txt. Review the data and note that that there are three rows of customer contact data, beginning with a row for ID 32.
3.	Open the tblCustomers table and review the data. Note that the last row in the table has ID 31. Switch to Design view and review the fields. Compare the table and the text file (see Figure 13.13). Figure 13.13. The tblCustomers table and the DelimitedFieldNames.txt file have the same number of columns with the same names. Everything seems in place for an easy import. Both files have the same number of columns. The field headings match nicely, and all your data in the text file is in the same format as the table.
4.	Close the tblCustomers table and the text file.
5.	With tblCustomers selected in the Database window, choose File, Get External Data, Import.
6.	In the Import dialog box, select Text Files (*.txt; *.csv; *.tab; *.asc) in Files of Type. Navigate to the DelimitedFieldNames.txt file and double-click it.
7.	In the first dialog box, Delimited should be selected. Click Next.
8.	In the next dialog box, make the following selections: Semicolon should be selected as the delimiter. Check the First Row Contains Field Names check box. Open the drop-down list for Text Qualifier and choose double apostrophes (").
9.	Click Next.
10.	Click In an Existing Table. Open the drop-down list and choose tblCustomers.
11.	Click Next. In Import to Table, keep tblCustomers. Click Finish.
12.	A message appears that not all the data was imported (see Figure 13.14). Click Yes, then OK. Figure 13.14. The complex error message that tells you the records weren't added. Even though the message might indicate otherwise, none of the records was added.
13.	Open tblCustomers and look at the last row. None of the rows was imported.
14.	Click View to go to Design view.
15.	Click in the CustPhone field. Note that the field's validation rule is Like "##########". Although the numbers are formatted in Datasheet view to include non-numeric characters that make the values appear as telephone numbers, the value that is stored has 10 numbers and no other characters. In Figure 13.13, note that telephone numbers in the delimited text file include the normal formatting characters.
16.	Close the tblCustomers table.

Modify the Source File

When you discover why Access won't import the data, you can modify the source file accordingly. Let's try to import the same data, which has been modified to meet the validation rule.

1.	Open DelimitedAllDigits.txt and review the data (see Figure 13.15). Figure 13.15. The CustPhone column now includes only 10 digits and no other characters. This file is the same as DelimitedText.txt, except for one thing: The customer phone numbers (but not the fax numbers) have 10 digits and no other characters.
2.	Close DelimitedAllDigits.txt.
3.	Choose File, Get External Data, Import.
4.	In the Import dialog box, double-click the DelimitedAll Digits.txt file.
5.	In the first dialog box, Delimited should be selected. Click Next.
6.	In the next dialog box, make certain that… Semicolon is the delimiter. First Row Contains Field Names is selected. The Text Qualifier is a double apostrophe (").
7.	Click Next.
8.	Click In an Existing Table. Open the drop-down list and choose tblCustomers.
9.	Click Next and click Finish.
10.	Click OK. Open the tblCustomers table and scroll to the bottom. The fields have been imported.
11.	Close the tblCustomers table.

Import to a New Table

You can also import a text file as a new table. This process might go more smoothly because you don't have existing fields and field properties to worry about. Still, you need to make certain that the data in tables is consistent. Access reviews the first few records to assign data types, but you can always change these selections after importing in Design view.

Here's an exercise that creates a new table from DelimitedField Names.txt, the file you previously had difficulty importing into an existing table.

1.	Choose File, Get External Data, Import.
2.	Double-click DelimitedFieldNames.txt.
3.	Delimited should be chosen. Click Next.
4.	In the next dialog box, make the following selections: Semicolon should be selected as the delimiter. Select First Row Contains Field Names. Open the drop-down list for Text Qualifier and choose double apostrophes ("). It's a good idea at this point to scroll through your data to make sure that the fields and columns look OK.
5.	Click In a New Table. Click Next. Click the Advanced button.
6.	In CustomerID, click Skip. Because it's a new table, you can start the primary key from 1 (as you know, though, there would be no problem using ID of 32, 33, and 34). You can choose to create a primary key in the next dialog box.
7.	Click in the Data Type column of the CustZIP row. Open the drop-down list and select Text. Using the first several rows of data, Access does its best to select data types for the new tables. Selecting a Number field data type (that is, Long Integer) for CustZIP makes sense from Access's viewpoint. But, of course, you want a ZIP field to be Text.
8.	Click OK to close the Import Specification dialog box with your changes, and click Next.
9.	Let Access Add Primary Key should be selected. Click Next.
10.	Name the table tblContactInfo and click Finish. Click OK in the message.
11.	In the Database window, open tblContactInfo (see Figure 13.16). Figure 13.16. The CustZIP field has been imported as text, not as a number, and a primary key has been assigned in the first column. Access had little problem importing the table. In Design view, you can inspect and modify field properties. For example, you could assign captions to each field so that the columns are easier to read.
12.	Close tblContactInfo.

Create a New Table Using Excel

You can similarly import files from Excel. Let's try an example in which you create a new table.

1.	If you haven't already done so, copy Assets.xls to an appropriate place on your hard drive. Open the file. This simple spreadsheet contains columns with a variety of data, including text, dates, numbers, dollar amounts, and true/ false entries. TIP As shown in the Depreciable? column, if you want Access to identify the data type of Yes/No fields correctly, edit the values to true and False; don't use Yes and No or -1 and 0.
2.	Close Assets.xls and close Excel.
3.	Choose File, Get External Data, Import.
4.	Navigate to Assets.xls. Double-click the file to import it.
5.	In the first dialog box, select Show Worksheets and Sheet1. If your Excel file contains more than one worksheet or range, Access asks which you would like to import.
6.	Click Next. First Row Contains Column Headings should be checked.
7.	Click Next. The In a New Table button should be checked.
8.	Click Next. In this dialog box, you should be able to click on any column and modify its field name, index, and data type. Unfortunately, a bug in Office 2003 might prevent you from doing so. You might also find that the Data Type drop-down list is grayed out and inaccessible. I ignore any fixes here because the workaround is simple enough. After Access creates the table, review and modify field properties as necessary. At the same time, you can review data types in Table Design view.
9.	Click Next. Click Let Access Add Primary Key and click Next.
10.	Name the new table tblAssets. Click Finish. Click OK in the message.
11.	Open tblAssets in Datasheet view and briefly review the records. The columns and data appear reasonable. In the Depreciable? field, which has a Yes/No data type, the values are correctly stored as -1 and 0. If you want, you can change to an easier-to-understand text display (such as Yes/No). In Design view, click in the Depreciable? row and edit the Format property.
12.	Click View to switch to Design view (see Figure 13.17). Figure 13.17. Access has successfully guessed the data types of all fields based on its inspection of the first few rows of data. Access did a good job of figuring out the data types. As I suggested earlier, you'll want to review all field names, data types, field properties, and so on to make sure they meet your needs.
13.	Close the table.

Q&A

Q1:	When I import data from Excel, I sometimes get an extra row in Access. It has an ID number but no other data. What's going on, and how do I fix it?
A1:	In Excel, you probably entered something in a row below the data. Whatever you entered is now gone, and the row is vacant. But Excel remembers that the row has been used and extends the range of the worksheet. The cure is to delete the last few blank rows in the Excel worksheet before importing.

Add Excel Rows Using Copy-and-Paste

Sometimes the best way to add rows from Excel to an existing Access table is to copy and paste. As with all imported data, Access rejects any values that violate data integrity. You'll find that Access often allows some records to be appended but blocks others. The rejected rows are pasted into a new Paste Errors table that can be accessed from the Tables section of the Database window. You can modify these values and paste them into the table. Sometimes it's easier, however, to modify the records in the source document and copy and paste them again.

Access might decide not to paste records for a variety of reasons. You've already seen one example in this chapter: a violation of a validation rule. Other causes include wrong data type, values that are too long and thus violate the Field Size property, and duplicate values in primary keys.

The following exercise illustrates several problems you might encounter in appending Excel records to an Access table.

1.	Open tblShippers in Design view. Briefly review the information. If you have not already done so, copy ShippersErrors.xls to your hard drive. Open the file.
2.	Minimize all other windows besides tblShippers and ShipperErrors.xls. Right-click on any open area of the toolbar and choose Tile Horizontally (see Figure 13.18). Figure 13.18. The columns in the table and spreadsheet match. If you can't get the windows to tile successfully, don't worry about it; you can just switch between windows using the taskbar.
3.	Compare the field names in tblShippers and the column names in ShipperErrors.xls. The field names and column names match exactly in number, order, and name, so Access shouldn't have any problems lining up the data correctly.
4.	In ShipperErrors.xls, select the rows for ShipID5, ID6, and ID7, and copy them to the Clipboard. Click anywhere in tblShippers and click the View button at the far left of the toolbar to switch to Datasheet view.
5.	Select the New Record row (the bottom, empty row). Choose Edit, Paste to paste the records. Confirm the paste. Access had no problem pasting the records. There might or might not be a gap in the ID numbers between the new records and the existing ones. As you know, if there is a gap, it's irrelevant.
6.	Switch back to Excel. Copy row ID#8.
7.	Select the New Record row in tblShippers. Paste the row. You get the error message in Figure 13.19. Click OK. You get another message that the record has been pasted into a Paste Errors table. Figure 13.19. The error message tells you the field size is too small.
8.	Click View to switch to Design view. Click in the ShipAcctNumber row. In the lower pane, note that the field size is 10. Access couldn't paste the record because the field size couldn't accommodate the account number.
9.	Edit the field size to 20. Save the change and click View to switch to Datasheet view.
10.	Select the New Record row and paste the row. The record pastes with no problem.
11.	Select the ID#9 row in ShipperErrors.xls and copy it to the Clipboard. Select the New Record row in tblShippers and paste. You get the error message in Figure 13.20. Click OK. You get another error message; click OK in that message as well. Figure 13.20. The error message tells you that the data isn't valid for the field, which indicates an error in data type.
12.	Click View to switch to Design view. Note that the OvernightDelivery field is a Yes/No field. Review the ID#9 record in the Excel spreadsheet. In the OvernightDelivery column, the entry is Possibly. This is not a value that is accepted in a Yes/No field.
13.	In ShipperErrors.xls, edit OvernightDelivery for ID#9 to False. Copy the row again.
14.	In the table, switch to Datasheet view. Paste the ID#9 row as a new record. This time you had no problem pasting the record.
15.	Copy the ID#10 row in the spreadsheet and paste it in tblShippers. Click in the next empty row in tblShippers. You get the error message in Figure 13.21. Figure 13.21. Phone is the only field with a null value, so you must either change the field property or enter a phone number. This time Access pasted the record, but when you moved to the next row, you were unable to save it. The upshot is the same: The record is unacceptable. ShipPhone cannot contain a null value.
16.	Click OK. In tblShippers, click in the ShipPhone field for the record you just pasted. Enter the telephone number [phone]. Press Tab twice; Access has saved your record.
17.	Close ShipperErrors.xls. Click No when you are asked to save your changes. Click No if you are asked to save copied data to the Clipboard.
18.	Close tblShippers.
19.	In the Database window, open the Paste Errors table.
20.	This table is now superfluous because you corrected all the errors.
21.	Close the Paste Errors table. Delete the table and confirm.

Usually when you paste records, you don't step through it as we did in the example. Access pastes the records it can, gives you a series of error messages of why other records weren't pasted, and then throws all the problem rows into the Paste Errors table. The lesson is clear: If you do the spade work of reviewing data before you paste records, you'll save yourself an awful lot of trouble.