File Load into Caching Database module

The File Load into Caching Database module is an automation tools module for Data integrity testing. Use this 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

The file encoding format. The default value is UTF8.

You can specify these 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 example, specify iso-8859-1 for Western European (ISO), or 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

The character that indicates a new row. Usually it's \r\n for Windows or \n Unix .

The default value is \r\n.

X

Cell Escape Character

The character that indicates 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 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.

The default value is ".

X

Column To Process

Specify a semicolon-separated list of columns that should be loaded.

By default, Tosca Cloud 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

The maximum number of errors before Tosca Cloud aborts execution.

The default value is 100.

X

Load Error Behavior->File Name

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: 

Value

Description

Trim

Removes all leading and trailing whitespace characters.

Default scope: HeaderAndData

Trim[<character>]

Removes 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

Removes all leading whitespace characters.

Default scope: HeaderAndData

TrimStart[<character>]

Removes 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

Removes all trailing whitespace characters.

Default scope: HeaderAndData

TrimEnd[<character>]

Removes 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>]

Replaces all occurrences of the first string with the second string.

Default scope: Data.

Substring[<start index>]

Extracts a part of a longer string. The extraction 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>]

Extracts a part of a longer string. The extraction 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>]

Extracts a part of a longer string. The extraction 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

Converts 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>]

Converts 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

Converts 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>]

Converts 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, add the parameter Scope in the :

  • 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].

You want to replace all instances of the string CustomerDataAustria with the string CustomerDataUSA. You want to apply this change to the header and all data rows.

To do so, define the this action: Replace[<CustomerDataAustria>][<CustomerDataUSA>][Scope:HeaderAndData]

You want to load parts of your Orders2026 table into the caching database so you can later check the values of specific cells with the DI DB Expert Module, or compare those parts with the data from another database using the Complete Row by Row Comparison module.

You use the File Load into Caching Database module in the following way:

  • Enter TestTable1 in the test step value Table Name to define the table in the caching database.

  • Select False in the test step value Keep Existing Table Entries to delete any existing content in TestTable1.

  • Enter C:\Users\User1\Orders2026.csv the test step value File Name to define the location of your file.

  • Select CSV in the test step value File Mode as your file uses a specific character to separate columns.

  • Select ASCII in the test step value File Encoding to indicate the encoding format of your file.

  • Select ; in the test step value Column Delimiter/length Info as the column-separator character.

  • Select Windows (\\r\\n) in the test step value Row Separator as you work with Windows.

  • Enter " in the test step value Cell Escape Character.

  • Enter #3;#4;#6 the test step value Colemns to process to specify the columns you want to test later on.

  • Leave the test step value Header Row empty as your file doesn't have one.

  • Enter ! in the test step value Skip Lines Starting with as this character indicates abandoned or failed orders in your table.

  • Expand Load Error Behavior and enter C:\Users\User1\DIreport in the test step value File Name to save the error report in the file DIreport if your table fails to load in the caching database.

Load a CSV file into the caching database