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 Path | Path 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.
Example: https://{externalstoragesystem}/Completed_Applications |
| File Name | Name of the file you want to use to define the query. |
| File Type |
|
CSV Files
CSV Files Configuration Parameters
| Parameter | Description |
|---|---|
| Encoding |
|
| 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 |
|
| 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:
|
| Is Nullable | The property value can be null and remain valid. |
| Field Type | Determines the valid values for the property. |
| Field Width (characters) | Determines the number of characters allowed in the column. |
Define Advanced Options - All Files (optional)
DateTime Configuration
Time Configuration
