applied tips : february 2008
microsoft access : importing from anywhere
Microsoft Access can read everything from Excel workbooks to text files and beyond.
People from different countries speak different languages. Likewise, different types of computer programs save files in different formats. Fortunately, just as some people can speak several languages, Access can read and write in other file formats. In this article weíll show you how easy it is to work with information in other file formats.
To link or to import
The first decision to make when working with other file formats is whether you want to import, or copy, the information into Access, or just link to the informationís original location. Hereís a quick description of both methods:
Imported: When you import information, you copy and place it in a new or existing table in your database. There are many reasons to import information into an Access table. Perhaps you want to use the most powerful features of Access to analyze data in an Excel workbook, or maybe you need to import a list of names stored in a comma delimited text file. This is a good option if the information in Access and the original file donít need to correspond with one another.
Linked: When you link information, that data stays in its original location. You can add, delete, and edit records in a source file from within Access, but you canít change its structure. If the data in the source file changes, the changes will appear in your database as well. Linked tables are most frequently used to connect Access to data sources that are accessed by other users. Maybe you want to create a link to information that is stored in your organizationís Microsoft SQL Server database. Use this option if the information in Access and the original file need to correspond.
External information must often be cleaned and tweaked before being imported into Microsoft Access. Make sure that you delete any fields or extraneous data that you donít want to import. If you want to import an Excel workbook, make sure that your data is the only data on the worksheet. If not, you'll have to cut and paste the data into a new worksheet.
Finally, try to make sure your data includes field names, such as FirstName, LastName, etc. Microsoft Excel works great for cleaning up information before itís imported into an Access table. If you're looking at a spreadsheet or text file, the very first line needs to be your field names.
So, letís import already
Now that youíve cleaned up your information, you can import it into Microsoft Access. Hereís the step-by-step procedure for importing:
- Select File, Get External Data, Import from the menu.
- Select the type of file you want to import from the Files of type list. For example, if you wanted to open a Microsoft Excel workbook, you would select the Microsoft Excel format.
- Find and open the file you want to import. The Text Import Wizard will appear. At this point you have to provide Microsoft Access with some more information, such as how the information is stored in the file. The questions you are asked will vary depending on the type of file you are importing. For example, if you opened a text file you would need to tell Access whether the data was stored in delimited or fixed width format.
- Follow the on-screen instructions of the Import Text Wizard to import the data.
If everything goes as planned, Access will import the fileís records into a new table. However, youíve undoubtedly worked with computers long enough to know that things donít always work exactly the way theyíre supposed to. If Access encounters any problems with your imported data, it will inform you of the problem and save the offending records in a separate table for your review.
The missing link
As we mentioned earlier, the other way that Microsoft Access can use external information is with linked tables. Linked tables are helpful for gaining access to information that is shared by several users. Hereís how to create a linked table:
- Select File, Get External Data, Link Tables from the menu.
- Find and open the file you want to link to.
When the files are linked, the information will appear in a new table with an arrow next to it.
The following is a collection of file types that are compatible with Microsoft Access. Read the file type descriptions to find out how you can use different file types to optimize your databases.
- Microsoft Excel workbooks (.xls, .xlt) - Microsoft Excel also works great for cleaning up information before importing it into Access.
- Lotus 1-2-3 spreadsheets (.wk4, .wk3, .fm3, .wk1, .wks)
- Comma delimited text files (.csv)
- Tab delimited text files (.txt) - A file containing values arranged so that each field has a certain width.
- HTML files (.htm, .html) - Access can import information from a Web page as long as itís in a list or table.
- dBase database files (.dbf) - An old but very popular database format thatís almost universally understood.
- Paradox database files (.dbf)
- FoxPro database files (.dbf)
- ODBC Protocol - The goal of the Open Database Connectivity (ODBC) protocol is to make it possible to access any data from any application. For example, you could use ODBC to connect Access to a Microsoft SQL server database.
You can also link Access databases to other access databases (.MDB and .ACCDB). This is extremely useful when you have two database files on the network that need to exchange data with each other, even though the serve two different purposes.
schedule a class or get consulting on access
Request an on-site Access class from Applied Office or get some helpful consulting. Sessions are just $125/hr. Learn more here
quick reference card
Get the Quick Reference Card on Microsoft Access! Download it for free and print it on your own printer. You might even want to laminate it.