Exporting Excel to Access

Overview

The [highlight txt=’export Excel to Access’] instruction allows a user to export data from an Excel worksheet to a Table in a Access database. The various ways in which this instruction can be configured will be explained as we proceed further through this article.

[message type=”warning”]For the purposes of this article we are going to use two simple and fictitious files. They have already been uploaded into a data step. You will want to replace these files with those of your own.[/message]

Before we get started

Please further create a ‘Manipulate a file’ or ‘Multiple file manipulations’ action step so that you can add and use instructions.

The source file that we are going to use looks like this:

The target file that we are going to use looks like this:

Steps

1. Create an ‘Export Excel to Access’ instruction.

2. Link the file named “Source” as the source file.

3. For the Source file, select Sheet1.

4. Use an Advanced Range from A1 to the last cell in ColumnA as far as D.

5. Link the file named “Target” as the target file.

6. For the Target file, select Table1.

7. The ‘Recreate table structure?’ rule should be enabled by default. Enabling this rule enables the instruction to 1) clear all the existing data and designs from the Table, and 2) impose the structure of the Excel data onto the Table. What this means is that any fields and any data within those fields which pre-exist in the Table will be cleared, and new fields will be created. The number of new fields created will correspond to the number of columns found in the selected Excel data. The Table is therefore re-designed to conform to the Excel data. The structure of the Excel data is therefore ‘re-created’.

[message type=”warning”] Please note that if the the ‘Recreate table structure?’ rule is not enabled, the instruction will run successfully only if the structure and the formatting of columns in the Excel data perfectly match the structure and the formatting of the fields which already exist in the Table. Furthermore in this scenario, the Excel data would be appended to the bottom of any existing data in the Table unless the ‘Clear old data’ rule is enabled.[/message]

8. Enable the ‘Include column headings?’ rule by ticking its box. This allows the Table to be re-designed using the cells in the first row of the Excel data as column headings for the new Table structure. If this rule is not enabled, the re-designed Table will have default field names of “F01,” “F02,” “F03,” and so on, while the first row of the Excel data will be imported into the Table as part of the data.

[message type=”warning”] Please note that if the ‘Recreate table structure?’ rule is not enabled, the ‘Include column headings?’ rule, if enabled, would force the instruction to ignore the cells in the first row of the Excel data. In this scenario, the first row of the Excel data would be not be copied into the Table.[/message]

9. Save and run the step.

10. After the step finishes running, download and open the target file. As you can see, the Table has been completely re-designed to match the structure of the Excel data:

Recent Posts

Leave a Comment