Exporting data to Excel spreadsheets

The Excel Spreadsheet Export action allows you to write into an excel file from a database table. You can:

• Choose the fields to export using a field mapping grid
• Filter the table using the filter section

On the Configuration tab:

1. Define the mapping between input and output schemas, set the auto-increment option.

2. Drag table fields to create a filter for limiting exported records.

3. When Edit Mapping is enabled, use the Map to output option to map an individual column.

4. When Edit Mapping is enabled, use this option to add all unmapped columns.

5. When Edit Mapping is enabled and output schema exists, use this option to change the output data type.

6. When this option is enabled, use it to clear the mapping.

Export table to Excel spreadsheet

1. Drag the Excel Spreadsheet Export icon onto the design surface
2. On the Connections tab , enter a Name and a Description to identify the action, then specify the input database table and output Excel file options:

   Input -

   • Select the Database Connection, or choose New Database Connection to establish a new connection.
   • For Table Name, select the name of the table being exported.

   Output -

   • Existing connection
     • Select Use Existing Excel Connection, then select the Excel File Connection.
     • For Create/Overwrite Worksheet, enter the name of a new worksheet or choose an existing worksheet.
   • Excel file
     • Select Create/Overwrite Excel File.
     • Enter or select the File Path for the saved file (or a parameter identifying the saved file) , and specify the name the Excel Worksheet.
     • To include a date/timestamp postfix to the end of the output filename, select Append Postfix and choose either Date or DateTime.
3. On the Mapping tab , indicate how the data will be mapped between the input database and output Excel worksheet:
   • Select Match the output table schema to the input if the input and output table columns are arranged in the same order, with the same number of columns and the same column names. If selected, then mapping cannot be edited.
   • If the match option is selected, you can optionally select Remove auto-increment from output to exclude the _autokey field created and used by Data Guru to uniquely identify table rows from the exported data.
   • Click Edit Mapping to display the schema for the input database table and output Excel file and, if necessary, make any necessary changes to the output Excel file. Several buttons are enabled -
     • Click the button in the bottom left corner of the Input Database Table Schema area to add all unmapped columns to the output table if it does exist, or map all columns if the output table does not exist.
     • Click the button in the bottom left corner of the Output Excel File schema to clear all mappings that have been applied on this tab.
     • In the Input Database Table Schema area, hover over an individual column in the Maps to row to display buttons for creating or clearing the mapping for just that column.
     • In the Output Excel File area, hover over an individual column in the Maps to row to display a button for clearing the mapping for just that column.
   • To more narrowly define the records for which the export applies, open the Filter area and build the necessary expression by dragging icons from the Table Fields area. Refer to Filters for more information.