Skip to main content

Task Office - Excel - Create​

The Excel - Create Task creates an Excel workbook from delimited text, such as a CSV file, or from a value or Variable. Each line of the source becomes a row and each field a cell.

The Main settings, Flow, On error, Conditions and TimeOut tabs are the same for every Task; see Job - Tasks.

The Task settings are on the Excel - Create tab, which has the sub tabs Source, File filter, Columns and Output.

Source tab​

Source tab of the Excel - Create Task, with the Source and Format groups

Source

  • File: Creates one workbook from each file that the File filter tab finds

  • Value/Variable: Creates one workbook from the text in the box, which can be a value or a Variable, for example the output of a previous Task

    Click the Variable button to insert a Variable.

Format

Set these to match the source text:

  • Field separator: The character between fields, "Tab" by default
  • Text qualifier: The character around field values, if any; none by default
  • Line break: The character between rows, "CrLf" by default

File filter tab​

With File as the source, the File filter tab selects the source files; see Task - File filter. The Credential on its Location tab is used to read the source files. The tab is not available with Value/Variable.

When no file matches the filter, the Task raises a "file not found" error, which you can handle on the On error tab (see Task - On Error).

Columns tab​

Columns tab of the Excel - Create Task, with the column list, the Column header source group and Include column headers in destination file

The column list sets the header, width, format and type of each column, in order. Click Add, Edit or Delete to change the list, or Import headers to paste the first line of the source and create one column for each header, using the separators from the Source tab.

The Add/Edit column window has these settings:

  • Header caption: The column header

  • Width and Auto fit: The column width

  • Format: The Excel number format of the cells, "@" (text) by default

    For the format codes, see http://www.ozgrid.com/Excel/CustomFormats.htm.

  • Horizontal alignment: The alignment of the cells

  • Type: How the field is written: "Text", "Number", "Date" or "Formula"

    A "Number" or "Date" field that cannot be converted is written as text. A "Formula" field gets a leading "=" if it has none.

When the list has columns, their type, format and alignment apply to the fields in that order, whichever header source is selected.

Column header source

  • Use columns from file/Variable: The first line of the source is the header row
  • Use columns from definition above: The headers come from the column list

Include column headers in destination file

  • With Use columns from definition above, writes the headers from the column list as a bold first row
  • With Use columns from file/Variable, writes the first line of the source in bold

When it is cleared, no header row is added, and the first line of the source is still written as an ordinary row.

Output tab​

Output tab of the Excel - Create Task, with the Sheet, Save to.., Credentials, Password protection and Sheet name settings

  • Sheet: The format of the new workbook

    "Excel97To2003" and "Excel97To2003Template" create an Excel 97-2003 workbook. All other choices currently create a standard Excel 2007 workbook (".xlsx").

Save to..

With Value/Variable as the source, only Different folder and use file mask is available.

  • Same folder but change extension: Saves to the source folder with the source file name and the extension ".xls" for the Excel 97-2003 formats or ".xlsx" for the others

  • Same folder but use file mask: Saves to the source folder with the name from File mask

  • Different folder and use file mask: Saves to Folder with the name from File mask

    With Value/Variable as the source, File mask must be the full file name, for example report.xlsx.

  • Folder: The destination folder

  • File mask: The name of the new file, "*.xlsx" by default

Folder and File mask can contain Variables.

warning

An existing file with the destination name is replaced without warning.

  • Credentials: The Credential used to save the new workbook; see Credentials

    Select a Credential in the combo box, or click the Settings icon to add or edit Credentials.

Password protection

  • Password protect file with password: Protects the new workbook with the password you enter below it

Sheet

  • Sheet name: The name of the worksheet in the new workbook, "VisualCron" by default; it can contain Variables

Output and errors​

The output of the Task is the path of each workbook it created, one per line.