Access to Excel: How to transfer data from Access to Excel File
Step
1. Create a new job, and open the [Design Job] interface.
2. Create a new ADO dataset as target in [Design Job] interface, and open the property dialog of ADO dataset, set connection parameters with Access in [Database] sheet, then click [Import field definition] button in [Fields] sheet to import field definition information.
Following is the detailed parameters of Access should be setted.
Property name
Description
Connection string
Set ConnectionString to specify the information needed to connect the access.
Click the button on the Connection string box right, to open [Data link properties] dialog.
Select "Microsoft Jet 4.0 OLE DB Provider" , and click [Next] button.
Select mdb file by click button on the "Select or enter a database name" combox right.
Table name
Once you set up the parameters for database connection, click the combo box, all tables will be listed.
Databasetype
Select Microsoft Access 2000 or 95/97.
Field
List the definition information of the field that has the above format.
Before reading source data, you must define fieldĄ¯s information. About how to get field information.
3. Create a new Local file dataset as target in [Design Job] interface,
and open the property dialog of Local file dataset, set table type as "Excel spreadsheet(*.xls)" in [General] sheet, and set other parameter,
then click [Import field definition] button in [Fields] sheet to import field definition information.
4. Connect source and target. Create a new DBLink between MySQL dataset and Local File dataset, and open the property dialog of DBLink.Be careful that only "Append records" item is valid in "update mode" parameter.
5. Mapping fields. Click [Field mapping] button in [General] sheet in property dialog of DBLink to open [Set DBLink Fields Mapping] interface. You can edit calculation expression for each target fields, or drop source field to target field by mouse to generate field mapping list.
