Define a File Query

A file query copies data from a file, which you can use as a data model. Supported file types are CSV and Open XML.

We'll walk you through the process to define the file query.

Add a File Query data model


Select a connection to file storage.

For more information, see Define a Connection to an External Storage System.

Define the file query and select the file type

CSV Files Configuration Parameters

Parameter
Description
Name
The name you select for the query. This should be a meaningful name so you can easily identify and understand what the query is.
Connection to File Storage
Connection you defined to the external storage system.
Source PathPath to the folder where you want to import the file from. It is appended to the connection you defined to the external storage system. There are a couple of ways to define the source path. You must use forward slashes (/) when you define the file name.
  • Manually: type a target path in the text box.
  • Expression: define the target path from a previously returned value in the workflow, for example, a path returned from a Web Service Listener Activity.

Example: https://{externalstoragesystem}/Completed_Applications


File Name
Name of the file you want to use to define the query.
File Type
  • CSV
  • OpenXML


CSV Files

CSV Files Configuration Parameters

Parameter
Description
Encoding
  • ASCII
  • Big-Endian Unicode
  • Unicode
  • UTF32
  • UTF7
  • UTF8
First row contains header
Select if the first row of the source file contains a header. This row is skipped when the file is processed.
Has fields enclosed in quotes
Fields contain quotations when they are used to count separator symbols as part of the field value.
For example, if a field value is 52,345 and comma is a defined separator, the entire field is wrapped in quotations "52,345".
Cases in which a field is wrapped in quotations for other reasons, and the Has fields enclosed in quotes checkbox is selected, the quotations are removed from the field.
Trim white spaces
Removes unnecessary spaces in cell values. This option is selected by default.


OpenXML Files

OpenXML Files Configuration Parameters

Parameter
Description
Encoding
  • ASCII
  • Big-Endian Unicode
  • Unicode
  • UTF32
  • UTF7
  • UTF8
Sheet Name
If the file contains more than one sheet, specify the sheet name that you want to process. If this field is empty, the first sheet in the file is selected.
First row contains header
Select if the first row of the source file contains a header. This row is skipped when the file is processed.


Define Field Properties - All File Types

This window depends on previous configurations. In this example, we selected a CSV file with fixed width.

File Field Properties Configuration Parameters

Parameter
Description
Source Order
The property order as it appeared in the source file. In our example, the property order in the source CSV file was:
  1. Date
  2. Name
  3. Includes Sales
Is Nullable
The property value can be null and remain valid.
Field Type
Determines the valid values for the property.
Field Width (characters)
Default option.


Define Advanced Options - All Files (optional)

DateTime Configuration


Time Configuration



About this Article
  • Created: 08/31/2017 6:25 am EDT
  • Last updated: 11/06/2017 3:07 am EST
In This Article