File Load into Caching Database module

Use the File Load into Caching Database module to load the content of a text file into an SQLite database.

This module contains the following values:

Value	Description	Optional
Table Name	Enter the name of the SQLite table into which Tosca Cloud loads the data.
Keep Existing Table Entries	Set to True to add the data of the text file to the existing table. Otherwise, Tosca Cloud drops the table before loading new data.	X
File Name	Enter the full file path and file name of the text file you want to load. You can load multiple files from the same directory into the same table. You can use wildcards, if needed. For instance, you could enter C:\temp\myExcelFile*.csv.
File Mode	Define whether the columns have a fixed length or are separated by commas.
File Encoding	File encoding format. The default value is UTF8. You can specify these file encoding formats: ASCII (most common), encoding standard for text files. UTF8 encoding format that supports Unicode characters. Default corresponds to UTF8. Unicode corresponds to UTF16. Unicode (BigEndian) corresponds to UTF16BE. UTF32 UTF32 (BigEndian) Specify Encoding Name lets you specify a custom encoding by name for files with encoding formats other than those listed above. If you select this option, you have to specify the Encoding Name. For instance, specify iso-8859-1 for Western European (ISO). Or, as another example, windows-1252 for Western European (Windows). For a complete list of supported encodings, check out Microsoft documentation (opens in new tab).	X
Column Delimiter/length Info	Indicate the starting point for each column in a comma-separated list or the separator symbol in character-separated files.
Row Separator	Specify the character that indicates a new row. Use either Windows \r\n or Unix \n. The default value is \r\n or \n.	X
Cell Escape Character	Specify the character that invokes new character logic. The default value is ". If a cell value includes characters that also function as column separators, or the cell escape character itself, then enclose the value with the cell escape character. This will preserve the cell value's original meaning or function. Only one character can be set as the cell escape character. Example: The comma (,) is a column separator and the double quote character (") is the cell escape character. Id,Street,City 1,"31,rue de L'Etoile",Paris The value for Street includes a comma that is part of an address and should not act as a column separator. Use the cell escape character to enclose this value and prevent the comma from acting as a column separator.	X
Column To Process	Specify a semicolon-separated list of columns that should be loaded. By default, Data Integrity loads all columns.	X
Column Renaming	Rename columns via file. Specify the full file path to a text or CSV file with the column mappings. The file has to start with the header row Current Column Name;Mapped Column Name followed by one line for each column that you want to rename. For example: Name1;First Name Name2;Last Name	X
Column Renaming-><Current Name>	Rename columns manually. In the Name column, specify the name of the column you want to rename. In the Value column, specify the new name.	X
Header Row	Set to True to indicate whether the file has a header row. By default, there is no header row. In case the header is on a different row, specify the row number using #<n> instead of True. Only single-row headers are supported.	X
Skip Lines Starting With	Specify a semicolon-separated list of values that should be skipped.	X
Only Lines Starting With	Define a semicolon-separated list of characters that indicate a valid row. For instance, process lines that start with _,- or < are specified as _;-;<.	X
Cell Settings->All Columns - Option	Apply one of the actions for cell settings to all columns.	X
Cell Settings->Single Columns-><Name>	Apply one of the actions for cell settings to a specified column. To specify a column, replace <Name> with the column name.	X
Load Error Behavior->Ignore Load Errors	Set to True if you want Tosca Cloud to ignore load errors. The default value is False.	X
Load Error Behavior->Max Errors	Define the maximum number of errors before Tosca Cloud aborts execution. The default value is 100.	X
Load Error Behavior->File Name	Specify the absolute file path where Tosca Cloud logs errors. Tosca Cloud overwrites any existing file with the same name. By default, Tosca Cloud doesn't log load errors.	X

Actions for Cell Settings

You can use the following actions for Cell Settings - All Columns and Cell Settings - Single Columns:

Action	Description
Trim	Remove all leading and trailing whitespace characters. Default scope: HeaderAndData
Trim[<character>]	Remove all leading and trailing occurrences of the specified character. Default scope: HeaderAndData Replace <character> with the character you want to remove. For example: To remove a ", enter the " four times: Trim[""""].
TrimStart	Remove all leading whitespace characters. Default scope: HeaderAndData
TrimStart[<character>]	Remove all leading occurrences of the specified character. Default scope: HeaderAndData Replace <character> with the character you want to remove. For example: To remove a ", enter the " four times: TrimStart[""""].
TrimEnd	Remove all trailing whitespace characters. Default scope: HeaderAndData
TrimEnd[<character>]	Remove all trailing occurrences of the specified character. Default scope: HeaderAndData Replace <character> with the character you want to remove. For example: To remove a ", enter the " four times: TrimEnd[""""].
Replace[<search string>][<replace string>]	Replace all occurrences of the first string with the second string. Default scope: Data.
Substring[<start index>]	Extract a part of a longer string. The extract starts at the defined start index position and runs until the end of the string. Default scope: Data For example: Substring[9] with input Project Manager returns Manager.
Substring[<start index>][<length>]	Extract a part of a longer string. The extract starts at the defined start index position and contains the number of characters specified in length. Default scope: Data. For example: Substring[9][3] with input Project Manager returns Man.
Right[<length>]	Extract a part of a longer string. The extract runs from the end of the string towards the beginning and contains the number of characters specified in length. Default scope: Data. For example: Right[7] with input Project Manager returns Manager.
Lowercase	Convert the string to lowercase using the currently active locale. Default scope: Data. For example: Lowercase with input Project Manager returns project manager.
Lowercase[Culture:<culture name>]	Convert the string to all lowercase. Lowercase[Culture:<culture name>] uses the given culture name to create a new locale. Default scope: Data. For example: Lowercase[Culture:zh-Hans] uses the culture information of "Chinese(simplified)" to convert uppercase to lowercase characters.
Uppercase	Convert the string to uppercase using the currently active locale. Default scope: Data. For example: Uppercase with input Project Manager returns PROJECT MANAGER.
Uppercase[Culture:<culture name>]	Convert the string to all uppercase. Uppercase[Culture:<culture name>] uses the given culture name to create a new locale. Default scope: Data. For example: Lowercase[Culture:en-us] uses the culture information of "English - United States" to convert lowercase to uppercase characters.

To change the default scope of an action, you can add a Scope parameter:

To apply the action to the header row only, add the parameter [Scope:Header].
To apply the action to all data rows but not the header, add the parameter [Scope:Data].
To apply the action to the header and all data rows, add the parameter [Scope:HeaderAndData].

For example, to replace all instances of the string CustomerDataAustria with the string CustomerDataUSA in the header and all data rows, define your action as: Replace[<CustomerDataAustria>][<CustomerDataUSA>][Scope:HeaderAndData]