This site requires JavaScript to be enabled
IE BUMPER

Product
Version
IncidentLookup using list
Language
How can we help?

Solution Suggestions

Please enter your question to get suggestions.
  Help
IE BUMPER
Categories
Boolean Operators
OR or vertical bar symbol (|)
Finds a match if either the terms exist in a document (a union using sets).
AND
Finds a match if both terms exist in a document (an intersection of sets).
NOT, minus (-), or exclamation point (!)
Excludes documents that contain the term after NOT (a difference of sets).
Wildcards
Asterisk (*)
The asterisk symbol performs a multi character wildcard search.
Percent sign (%)
The percent sign performs a single character wildcard search.
Phrases
Quotation marks (")
Use quotation marks to search for an exact phrase with multiple terms.
For examples and more information look at our Knowledge Base: Advanced Search On The Support Portal

How to read, verify and modify CSV files with an SET

Note:    This example code is intended to demonstrate customization capabilities of the TOSCA Testsuite. It 
'             is not part of the TOSCA Testsuite scope of delivery and TRICENTIS will not offer any support. 
'             TRICENTIS disclaims any and all liability for any direct or indirect damage due to improper use
'             or customization.


There are some existing solutions proposed to read CSV files, most of which are based on using the ODBC driver to access the file. In this KB article I propose a different solution that is based on a SET. It's main advantage is that it is independent of additional setups and should work out of the box on most systems. As the code is embedded in the module the test step might be slower than expected - try to compile it into a DLL if that becomes a significant issue. If you need even faster runtimes please contact your sales representative to discuss what benefits the Tosca BI product could provide to you.


Situation

In a test case you need to verify or modify the contents of CSV files. Example usecases might be large output files produced by batch-processing mainframes.

Solution

Traditional Solutions rely on ODBC drivers or Excel. The proposed SET uses dotnet built-in DataTable functionality, which also allows to use the built in filter syntax: https://www.csharp-examples.net/dataview-rowfilter/.

You can verify as many fields as you like... 

DataTable Filter Syntax

You can also use the very powerful DataTable filter Syntax (SQL Like):

It is sometimes useful to include the column name in '[]', especially if there are special characters or whitespaces in the column name.

Some examples:

"[Date] < #1/1/2008#"    // date is less than 1/1/2008
"[Name] <> 'John'"       // string is not equal to 'John'
"[Price] IN (1.0, 9.9, 11.5)"          // float values
"[Name] LIKE '%jo%'"     // values that contain 'jo'
"MotherAge - Age < 20"   // people with young mother"
"Age % 10 = 0"           // people with decennial birthday
"[Date] < #1/1/2008# AND [Date] >= #1/1/2007" // Date range

Check https://www.csharp-examples.net/dataview-rowfilter/ for many more examples.

How to modify CSV files

If you want to modify CSV files you have to split and reassemble them - use the CSV module to split and modify the content, and Tbox create/save file to create the output CSV file.

Configuration

Implementation / Code

/*
' ****************************************************************** '
' TRICENTIS Technology & Consulting GmbH
' www.tricentis.com
' support@tricentis.com
' ****************************************************************** '
'
' script:               TBOXCSVReader.cs (SET)
' author:               kurgy
' date:                 09.07.2019
' customer:             Tricentis internal
'
' DISCLAIMER: This keyword is not part of the TOSCA Testsuite scope of delivery and TRICENTIS will not offer 
'             any support or other services in situations exceeding the analysis process for which it was intended.
'             TRICENTIS disclaims any and all liability for any direct or indirect damage due to improper use or customization.
*/

using System;
using System.Data;
using System.Linq;
using Microsoft.VisualBasic.FileIO;
using Tricentis.Automation.AutomationInstructions.TestActions;
using Tricentis.Automation.Creation;
using Tricentis.Automation.Engines;
using Tricentis.Automation.Engines.SpecialExecutionTasks;
using Tricentis.Automation.Engines.SpecialExecutionTasks.Attributes;
using Tricentis.Automation.Execution.Results;
using Tricentis.Automation.Resources;

namespace Tricentis.Automation.SpecialExecutionTasks.CSVReader
{
    [SpecialExecutionTaskName("TBOXCSVRead")]
    public class CSVResource : Resource
    {
        public DataTable Data;
    }


    public class CSVReader : SpecialExecutionTaskEnhanced
    {
        private const string FILE_PARAMETER_NAME = "Filename";
        private const string HEADER_PARAMETER_NAME = "Header";
        private const string SELECT_PARAMETER_NAME = "SELECT";
        private const string SELECT_CSVOut_PARAMETER_NAME = "CSVOutput";
        private const string OPTION_PARAMETER_NAME = "Options";

        private const string OPTION_DELIMITER_PARAMETER_NAME = "Delimiter";

        //private const string OPTION_LINEFEED_PARAMETER_NAME = "Output Line Separator";
        private const string OPTION_QUOTED_PARAMETER_NAME = "Fields enclosed in quotes";
        private const string OPTION_HEADER_LINES_PARAMETER_NAME = "Number of Header Lines";

        private const string DEFAULT_DELIMITER = ",";

        private const string DEFAULT_LINEFEED = "\r\n";
        private const bool DEFAULT_QUOTED = false;
        private const int DEFAULT_HEADER_LINES = 0;


        private string DELIMITER = DEFAULT_DELIMITER;
        private int HEADER_LINES = DEFAULT_HEADER_LINES;
        private bool QUOTED = DEFAULT_QUOTED;

        #region Constructors and Destructors

        public CSVReader(Validator validator)
            : base(validator)
        {
        }

        #endregion

        #region Public Properties

        public override bool EnableSynchronization => true;

        #endregion

        #region Public Methods and Operators

        public override void ExecuteTask(ISpecialExecutionTaskTestAction testAction)
        {
            // Read Parameters

            // Options
            var optionParameterParent = testAction.GetParameter(OPTION_PARAMETER_NAME, true);
            if (optionParameterParent != null)
            {
                var delimiterParameter = optionParameterParent.GetChildParameter(OPTION_DELIMITER_PARAMETER_NAME, true,
                    new[] {ActionMode.Input, ActionMode.Select});
                if (delimiterParameter != null)
                {
                    DELIMITER = delimiterParameter.GetAsInputValue().Value;
                    testAction.SetResultForParameter(delimiterParameter,
                        new PassedActionResult("Delimiter set to '" + DELIMITER + "'."));
                }

                var quotedParameter = optionParameterParent.GetChildParameter(OPTION_QUOTED_PARAMETER_NAME, true,
                    new[] {ActionMode.Input, ActionMode.Select});
                if (quotedParameter != null)
                {
                    QUOTED = bool.Parse(quotedParameter.GetAsInputValue().Value);
                    testAction.SetResultForParameter(quotedParameter,
                        new PassedActionResult("Inputs fields are quoted:  '" + QUOTED + "'."));
                }

                var headerLinesParameter = optionParameterParent.GetChildParameter(OPTION_HEADER_LINES_PARAMETER_NAME,
                    true,
                    new[] {ActionMode.Input, ActionMode.Select});
                if (headerLinesParameter != null)
                {
                    HEADER_LINES = int.Parse(headerLinesParameter.GetAsInputValue().Value);
                    testAction.SetResultForParameter(headerLinesParameter,
                        new PassedActionResult("Skipping  " + HEADER_LINES + " header lines."));
                }
            }

            var fileParameter =
                testAction.GetParameter(FILE_PARAMETER_NAME, permittedActionModes: new[] {ActionMode.Input});

            var headerParameters = testAction.GetParameters(HEADER_PARAMETER_NAME);

            var header = new string[0];


            var table = new DataTable();

            var path = fileParameter.GetAsInputValue().Value;
            using (var csvParser = new TextFieldParser(path))
            {
                csvParser.SetDelimiters(DELIMITER);
                csvParser.HasFieldsEnclosedInQuotes = QUOTED;


                var headerString = "";

                try
                {
                    // skip header lines
                    for (var r = 0; r < HEADER_LINES; r++) csvParser.ReadLine();


                    // Handle the Header (allow multiple attributes - buffer and verify, for instance
                    header = csvParser.ReadFields();
                    headerString = string.Join(DELIMITER, header.ToArray());
                    foreach (var headerParameter in headerParameters)
                        if (headerParameter != null)
                            HandleActualValue(testAction, headerParameter, headerString);

                    foreach (var col in header) table.Columns.Add(col, typeof(string));
                }
                catch (Exception e)
                {
                    var error = "Header could not be parsed. " + csvParser.ReadLine() + " " + e.Message;
                    testAction.SetResult(
                        new NotFoundFailedActionResult(error));
                }


                while (!csvParser.EndOfData)
                {
                    // Read current line fields, pointer moves to the next line.
                    var fields = csvParser.ReadFields();

                    try
                    {
                        table.Rows.Add(fields);
                    }
                    catch (Exception e)
                    {
                        var error = "Line " + csvParser.LineNumber + " could not be parsed." + "\r\n" + "Header: " +
                                    headerString + "\r\n" + "Fields" + string.Join(DELIMITER, fields) + "\r\n" +
                                    e.Message;
                        testAction.SetResult(
                            new NotFoundFailedActionResult(error));
                    }
                }

                testAction.SetResultForParameter(fileParameter, SpecialExecutionTaskResultState.Ok,
                    "File <" + path + "< has been parsed successfully",
                    table.Rows.Count + " data lines have been parsed", fileParameter.Value.ToString());
            }


            foreach (var selectParametersParent in testAction.GetParameters(SELECT_PARAMETER_NAME,
                new[] {ActionMode.Select}))
            {
                var selectedTable = table.Copy();

                DataRow[] resultRows;


                var selectionValue =
                    selectParametersParent.GetAsInputValue(true, new[] {ActionMode.Input, ActionMode.Select});

                if (selectionValue != null)
                    try
                    {
                        resultRows = selectedTable.Select(selectionValue.Value);
                        if (resultRows.Any())
                        {
                            selectedTable = resultRows.CopyToDataTable();
                        }
                        else
                        {
                            selectedTable = new DataTable();
                        }
                        

                        testAction.SetResultForParameter(selectParametersParent, SpecialExecutionTaskResultState.Ok,
                            "Filter " + selectionValue.Value + " applied successfully. " + resultRows.Length +
                            " rows found.", selectedTable.Rows.Count + " rows are selected",
                            selectionValue.Value);
                    }
                    catch (Exception e)
                    {
                        testAction.SetResultForParameter(selectParametersParent,
                            new NotFoundFailedActionResult(
                                "Filter " + selectionValue.ValueToLog + " could not be applied: " + e.Message));
                        return;
                    }


                // Handle the column parameters
                var colParameters = selectParametersParent.Parameters.Where(p => p.HasExplicitName);
                foreach (var currentColParameter in colParameters)
                {
                    try
                    {
                        var currentActionMode = currentColParameter.ActionMode;
                        var currentValue = currentColParameter.GetAsInputValue().Value;
                        var currentName = currentColParameter.ExplicitNameValue.Value;

                        int currentColIndex;

                        // Find column with explicit name
                        if(selectedTable.Columns.Contains(currentName))
                        {
                            currentColIndex = selectedTable.Columns.IndexOf(currentName);
                        }
                        else
                        {
                            testAction.SetResultForParameter(currentColParameter,
                                new NotFoundFailedActionResult(
                                    "Column with name: [" + currentColParameter.Name +
                                    "] not found in table. Available columns: " +
                                    string.Join(DELIMITER, header.Select(h => "[" + h + "]")) + "."));
                            continue;
                        }


                        // Action Mode = Input
                        // Replace values in all selected rows
                        if (currentActionMode == ActionMode.Input)
                        {
                            foreach (DataRow row in selectedTable.Rows)
                                row[currentColIndex] = currentValue;

                            testAction.SetResultForParameter(currentColParameter, SpecialExecutionTaskResultState.Ok,
                                "Column <" + currentName + "> replaced with " + currentValue);
                        }

                        // Action Mode = Constraint
                        // Remove all rows that do NOT match the constraint
                        else if (currentActionMode == ActionMode.Constraint)
                        {
                            var selectedRows = (from DataRow dr in selectedTable.Rows
                                where (string) dr[currentName] == currentValue
                                select dr).ToArray();
                            selectedTable = selectedRows.Any() ? selectedRows.CopyToDataTable() : new DataTable();

                            testAction.SetResultForParameter(currentColParameter, SpecialExecutionTaskResultState.Ok,
                                "Column <" + currentName + "> constrained to " + currentValue,
                                selectedTable.Rows.Count + " rows are selected", DataTable2CSV(selectedTable));
                        }

                        else
                        {
                            var currentColString = selectedTable.AsEnumerable().Aggregate("",
                                (current, r) => current + r.ItemArray[currentColIndex]);
                            HandleActualValue(testAction, currentColParameter, currentColString);
                        }
                    }
                    catch (Exception e)
                    {
                        testAction.SetResultForParameter(currentColParameter,
                            new NotFoundFailedActionResult(
                                "Handling Column failed: " + e.Message));
                    }
                }


                // Output data as CSV
                var csvOutParameters = selectParametersParent.GetChildParameters(SELECT_CSVOut_PARAMETER_NAME);
                foreach (var currentCsvOutParameter in csvOutParameters)
                    HandleActualValue(testAction, currentCsvOutParameter, DataTable2CSV(selectedTable));
            }


            #endregion
        }

        private string DataTable2CSV(DataTable t)
            // print data table content to CSV string
        {
            return t.Select().Aggregate("",
                (current, r) =>
                    current + string.Join(DELIMITER, r.ItemArray) + DEFAULT_LINEFEED);
        }
    }
}

 

Attachments
0 0

Pavan Kadam

Created: 2020-08-04 13:11:24

Hello,

 

Thank you for the SET. I have imported it in my project and is working smoothly. Just one query- When I am using UserName as column name and test@gmail.com as value to be verified in column. 

At the time of execution, I am getting verification error. It is tking all the data from UserName column.

Note: I have already used delimiter(,) and field enclosed in quotes.