Importing and exporting data files
Information Technology MS Access 2007 Users Guide
ACCESS 2007
Importing and Exporting Data Files
IT Training & Development (818) 677-1700 training@csun.edu
TABLE OF CONTENTS
Introduction ............................................................................................................................ 1 Import Excel to Access Considerations .................................................................................. 1 Preparing Excel Data for Import ............................................................................................. 2 Import Process ....................................................................................................................... 3
Check Imported Data for Accuracy ...................................................................................................... 8
Exporting Data to Excel.......................................................................................................... 9
Exporting Data to Excel the First Time................................................................................................. 9
Preparing Access Data for Export .......................................................................................... 9 Run the Export Process ........................................................................................................12 Saving the Export Specification .............................................................................................13 Run a Saved task..................................................................................................................15 Troubleshoot Missing and Incorrect Values in the Worksheet ...............................................16 Class Exercises.....................................................................................................................17
Part I - Using the Import Wizard ......................................................................................................... 17 Part II - Using the Export Wizard........................................................................................................ 23
How to Learn More................................................................................................................26
Microsoft on the Web........................................................................................................................ 26 Access Online Tutorials...................................................................................................................... 26 Printed Material .................................................................................................................................. 26
Training and Support.............................................................................................................27
IT Training .......................................................................................................................................... 27 Troubleshooting and Support ............................................................................................................. 27
i
INTRODUCTION
Have you ever needed to move data from Excel to Access or from Access to Excel? Access has features to assist with these tasks. This documentation will guide you through the following:
? How to prepare your Excel data for successful import
? How to use the import wizard
? Check results for accuracy
? Export data to Excel in Access
? How to prepare the source file for export
? How to use the export wizard
? Save the export specification
The import and export processes are easy to use. The wizards guide you through each process step by step. You have the option to run the import or export one time, or the option to run it on a regular basis.
When you import data, Access stores the data in a new or existing table without changing the data in Excel; when you export data, Access creates a copy of the object then stores the copy in an Excel worksheet. After you've run the processes several times, you'll find it's pretty straightforward, especially if you've prepared the source data beforehand.
IMPORT EXCEL TO ACCESS CONSIDERATIONS
There are some possible conditions you need to ask yourself before deciding to import Excel to Access. For example, suppose you use Excel to store information about all computers in your college. Over time, the worksheet has grown too big to easily manage; you have to scroll through too many columns and rows to find data which in turn makes it harder to find answers. Importing data into Access will make it easier to extract those answers through the use of queries - components that retrieve and process data quickly.
A fast and accurate way to move data from Excel to Access is by importing data from worksheets into a new table.
Access 2007 - Importing and Exporting Data Files
Page 1
PREPARING EXCEL DATA FOR IMPORT
Before starting the import process, the data in Excel must be cleaned up. Review the worksheet and check for items that need to be fixed. Figure 1 shows some common errors.
Figure 1 - Common Errors in an Excel Worksheet
See Figure 1 for the following items:
1. Fix error values like #NUM or #DIV. a. #NUM indicates that a formula or function contains invalid numeric values b. #DIV indicates that a number is divided either by zero or by a cell that contains no value
2. Remove any blank rows or columns. 3. Every column needs a heading. This will make the data easier to use in Access. 4. Make sure each column contains one type of data, for example, all dates or all
numbers. If you import mixed data, Access may not convert the data properly.
Look for and fix problems in your Excel data before you start the import process using the Import Wizard. There are limitations. They are:
? Can't export everything ? Can only import single Excel worksheets, not whole workbooks ? Can import 255 columns of data into a table in Access; if have more columns,
import it into another table ? Can't import all types of data ? Can't import images ? If formulas are used, you can import results of those formulas, not the formulas
themselves
Access 2007 - Importing and Exporting Data Files
Page 2
IMPORT PROCESS To start the import:
1. In Access, on the External Data tab, in the Import group, select the Excel command button (see Figure 1). The Get External Data ? Excel Spreadsheet window opens.
Figure 1 ? Select Import Excel Command Button
2. In the File name box, provide the location (path) and name of your Excel workbook (see Figure 2, #1). Select Browse to help locate your file. By moving the cursor to the right, you will see the name of the file.
Figure 2 - Get External Data ? Excel Spreadsheet Window - Select the Source and Destination
Access 2007 - Importing and Exporting Data Files
Page 3
3. Select the first option, Import the source data into a new table in the current database (default option) (see Figure 2, #2). Select OK. The Import Spreadsheet Wizard window opens (see Figure 3). This starts the Import Wizard.
Figure 3 ? Import Spreadsheet Wizard Window, Select Worksheet to Import
4. Select the worksheet to import (see Figure 3). This screen only shows if your worksheet has multiple sheets. You can also select a range.
5. Select Next. 6. Always use column headings. Make sure every column has a heading. Check the box
for First Row Contains Column Headings (see Figure 4).
Access 2007 - Importing and Exporting Data Files
Page 4
Figure 4 ? Import Spreadsheet Wizard Window - First Row Contains Column Headings
7. Select Next. 8. You can modify each field in the Field Options box (see Figure 5).
Figure 5 ? Import Spreadsheet Wizard Window, Modify Fields in Field Options Box
Access 2007 - Importing and Exporting Data Files
Page 5
Consider the following:
?
If you want to change column names, select the column and enter a new name in
the Field Name box.
?
If a column is searched frequently and contains a lot of data, select the column.
For the Indexed option, select Yes. Indexing a column makes it easier and
faster to find data.
?
You can set a data type for each field, though Access will do that for you. Data
types control what you can enter into a field. For example, you can't enter text in
a field set to contain numbers; that helps keep your data accurate.
?
To remove a column, select the Do not import field (Skip) check box.
9. Select Next. 10. Select Let Access add primary key (default option) (see Figure 6). Primary keys
uniquely identify each record in your table. They allow you to retrieve data more quickly and help ensure accuracy in your data.
Figure 6 ? Import Spreadsheet Wizard Window, Select Let Access Add Primary Key
11. Select Next. 12. Name the table in the Import to Table field (see Figure 7).
Access 2007 - Importing and Exporting Data Files
Page 6
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- exporting data from a pdf to excel
- analyzing and interpreting data worksheet
- structured and unstructured data examples
- qualitative and quantitative data examples
- discrete and continuous data worksheets
- quantitative and qualitative data analysis
- structured and unstructured data example
- repair outlook data files office 365
- repair outlook data files 2010
- repair outlook data files 2016
- race and ethnic data hud
- sun and moon data for one day