Import Excel Data to the ACCESS Database

Manual mode:

See Access help:   Help> Import or link data and objects If you manually From workbooks Import or link data in a workbook Before proceeding, make sure that the data in the workbook is arranged in the appropriate form and each field (column) in the workbook) all have the same data type, and each row also has the same field. Open the database, or switch to the "Database" Window of the database. Perform one of the following operations: To import a workbook, point to "get external data" in the "file" menu and click "import ". To connect to a workbook, point to "get external data" in the "file" menu, and click "chain table ". In the "import" (or "Link") dialog box, select "Microsoft Excel (*. xls)" in the "file type" box )". Click the arrow on the right of the "search range" box, select the drive and folder where the workbook file is located, and double-click its icon. Follow the prompts in the import data table wizard dialog box. If you are importing from a workbook of Microsoft Excel 5.0 or later, you can import a worksheet in the workbook. However, it cannot be imported from other multi-workbook files, such as Microsoft Excel 4.0 workbooks. To import these files, you must first save each worksheet as a separate file. Note: You can import or link all data in a workbook, or just data from a specified range of cells. Although you usually create a new table in Microsoft Access for import or link, you can append data to an existing table as long as the title of the workbook matches the field name. Access will try to assign the appropriate data type to the imported fields, but you should check the fields to check whether they are set to the desired data type. For example, in the Access database, the telephone number or zip code field may be imported as a number field, but in Microsoft Access, it should be changed to a text field, because these types of fields are not expected for any calculation. When necessary, check and set field attributes (for example, set the format ). Note that the above article still contains two methods and two extension methods: 1. Direct import 2. Use a chain table 3. When you only want to import EXCEL data to an existing table, you can first link an xls file and then directly use the jet SQL statement to do the following: Insert into [Access Table name] (field1, field2) select field1, field2 from [xls linked table] 4. According to the extension method 1 ("3,"), you can also directly use the IN clause for import. However, compared with the chain table, the performance and efficiency are poor. Insert into Table SELECT * FROM [Excel 5.0; HDR = YES; DATABASE = c: \ test.xls]. [sheet1 $];

Original post address: http://blog.csdn.net/cmgg/archive/2005/11/15/529618.aspx