Selecting to Excel
The [highlight txt=’select to Excel’] instruction allows a user to select data (via a query) in a Access Table and copy the selected data to an Excel worksheet.[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 contains the following data:
The target file that we are going to use is a standard fresh Excel workbook which contains 3 empty worksheets.
1. Create a ‘Select to Excel’ instruction.
2. Link the file named “Source” as the source file.
3. Open the Expert SQL Editor by clicking the ‘View/Edit SQL’ hyperlink.
4. In the Expert SQL Editor, you can write the query you want to run, as well as specify the Table you want to run the query on. Here, we will write a query which selects the ‘Carrier_Name’ field from the Table. The Editor should be populated with “SELECT” (with a white-space after it) by default. Type “[Table1].[Carrier_Name] FROM [Table1]” immediately after the white-space.
5. Save the query.
6. Tick the box for ‘Use advanced selection?’. A fresh 3-part condition will appear.
7. Type “Carrier_Name” into the Column field.
8. Set the variable condition to “not equals”. (This can be done by clicking on the field to the right of the Row field, which should by default be set to “equals”. Clicking on this field will open up a dropdown. Select “not equals” from this dropdown). This variable condition can then be matched to a value.
9. Type “British Airways” into the Value field. This field is located to the immediate right of the variable condition. This field should contain the word “value” in italics by default, though the word only serves as an indicator, thus is nothing is entered into the field, the field will be treated as empty (instead of as being populated by the word “value”). The Value field can also be treated with sensitivity to case. It can also be linked to a data property existing elsewhere in the process.
10. Link the file named “Target” as the target file.
11. Use Sheet1 from the target file.
12. Use cell address A1 from Sheet1.
13. Save and run the step.
14. After the step finishes running, download and open the target file. As you can see, the “Carrier_Name” field has been copied as a column, without the field name as a column heading and without the “British Airways” value: