Interacting with Rows and Columns in a Data File

Data sets contained in spreadsheets can be exported as comma-separated values (.csv) files. These files can then be interacted with directly on the local file system using SenseTalk scripting in Eggplant Functional.

Obtaining a Cell Value by Header and Row Names

The following example demonstrates how you can set up SenseTalk scripts to interact and process a data file located in a suites Resources pane, which is located on the local file system.

For this example, the DemoLanguage.txt file, located in the Resources pane, contains the following data:

RefWord,English,German,French,Japanese,ChinesePRC,ArabicHello,Hello,Hallo,Bonjour,こんにちは,你好,مرحبا,Testing,Testing,Testen,essai,検査,测试,تجريب,Goodbye,Goodbye,Auf Wiedersehen,Au Revoir,さようなら,再见,وداعا,

Notice that the DemoLanguage.txt file contains several language versions of only three words: hello, goodbye, and testing. This example uses the "English" language and the "hello" word. For the OCR search to work, you must have the "hello" word located somewhere on your SUT screen.

note

After getting this example working, you can experiment with the other languages and words contained in the DemoLanguage.txt file.

The Primary script passes a header name, "English", and a row name, "hello", into the TextLoc function. This function processes the DemoLanguage.txt data file, locating the column for the header ("English") and the row for the passed word ("hello"). The script then uses returns the cell value for that header and row name.

Here is the Primary script:

set global MyLanguage to "English"
doubleclick textLoc ("hello")

Here is the TextLoc script.

params MyWord
put ResourcePath("DemoLanguage.txt") into FilePath
repeat with each item MyItem of firstline
  if MyItem = global MyLanguage
    put repeatindex() into MyColumn
  end if
end repeat
repeat with each line of file FilePath
  if item 1 of it = MyWord
    put repeatindex() into MyRow
  end if
end repeat
log "Trying to find" && item MyColumn of line MyRow of file FilePath
return ImageLocation (Text:item MyColumn of line MyRow of file FilePath, Language:global MyLanguage)

To see this example in action, do the following:

1. Using the code shown above, add the Primary and TestLoc scripts to your suite.

2. Using the file content shown above, add the DemoLanguage.txt file to the Resources pane in your suite.

3. Add the word "hello" somewhere on your SUT screen for the TextLoc script's OCR search to work successfully.

4. Run the Primary script and look for an output similar to the following:

   8/8/17, 10:15:23 AM START Running PRIMARY.script8/8/17, 10:15:23 AM Log Trying to find Hello8/8/17, 10:15:26 AM imagelocation (TEXT:"Hello") at (328, 156)8/8/17, 10:15:26 AM doubleclick at (328, 156)8/8/17, 10:15:26 AM EndTestCase (Duration:"3.033", Errors:"0", Exceptions:"0", StartTime:"2017-08-08 10:15:23 -0600", Successes:"1", TestCase:"PRIMARY.script", Warnings:"0")8/8/17, 10:15:26 AM SUCCESS Execution Time 0:00:03 PRIMARY.script

Creating a .csv File

The following example demonstrates another way you can interact with a file on the local file system. In this case the file used is a .csv file containing a header row with column identifiers, followed by rows of data separated by the comma character.

This section of code deals with the creation of the .csv file. If you already have a .csv file you'd like to use, you can skip to section 2.

-- Define a name for the file
set myFile to env("temp") & "\myDataFile.csv"
-- If using a Mac, a possible equivalent would be to use env("TMPDIR")
-- Add some data to the file using put. The 'after' command ensures the text is appended to the file rather than overwriting the existing content:
put "Name,Email,Password" after file myFile
put "John Doe,john@doe.com,jdoe123" & return after file myFile
put "Jane Doe,jane@doe.com,jdoe123" & return after file myFile
put "Joe Bloggs,joe@bloggs.com,jblo123" & return after file myFile
put "Jane Bloggs,jane@bloggs.com,jblo123" & return after file myFile

Reading From a .csv File

SenseTalk makes interacting with files easy and even has built-in support for parsing comma-separated values into indexed containers.

As working with actual values is often more user-friendly than working directly with indexes, the below example demonstrates how you can specify the column and row names to locate the cell containing the desired data.

// Define a location for the CSV file. This step is required if not using the above code to generate the CSV file, so if that applies to you, simply uncomment and provide a suitable path to your CSV:
-- set myFile to "C:\ePF data\myDataFile.csv"
// Pass in information about what column and row you want to work with. This can be set in the script as it is done below, or passed in from a calling script, or drawn from a data file.
put "Email" into Column
put "Joe Bloggs" into Row
// When you iterate through the data in the CSV file, you'll need to know the position of the column we're after
put line 1 of file myFile into HeaderRow // Put the first line into a container
put the item number of Column in HeaderRow into ItemNumber
// Calculate the index of the column and store it in ItemNumber (starts with 1)
log ItemNumber // This will output the ItemNumber in the log
// With the above index set, you can now iterate through the rows in the data file
repeat with MyLine = each line of line 2 to the last of file myFile // Repeats with each row of the .csv file, skipping the header row.
  if the first item of MyLine is Row
  // as soon as the row you are after is found...
  then
    Log "Found row containing '" & Row & "'"// At this point you know that the correct row is selected
    Put item ItemNumber in MyLine into CellContents // The column index allows us to locate the correct value in the row to store in CellContents
    Log "Cell '" Column "," Row "' contains: " CellContents
// Optionally log when data was not found, useful for debugging
  (*
  else
    log "Row did not contain " & Row
  *)
  end if
end repeat