Wrong order of items when using > Put the records of MyExcelFile into MyData

Eggplant Functional
, ,
1

I have a spreadsheet which I read in and select a row at random.
My aim is to use the column headers as the text to search for on the screen, and the row data which it has selected as the input for the field.

All this mostly works fine, however I need to iterate from left to right through the data (As is - on spreadsheet) because certain fields have to be completed first in order to populate data for other drop down boxes.

But when I use the > Put the records of MyExcelFile into MyData
The data is sorted into alphaNumerical order and not kept in the same order as the spreadsheet.
This now means all the data is out of order and I cannot use it.

This is the code i have:

Put () into FinalList
	Put () into GLOBAL ExtractedRowData
	Set MyExcelFile to PathOfDataFile
	Put the records of MyExcelFile into MyData
	Repeat with each item DataRow of MyData
		if DataRow.("Enable Row (Y/N)") = "Y" then
			insert DataRow into FinalList
		end if
	End repeat
	Put the number of items in FinalList into x
	if ArbitraryRowNumber = "Random" then 
		Set ExtractedRowDataTEMP to  (item random(x) of FinalList)	
		repeat with each item x of keys(ExtractedRowDataTEMP)
			if property (x) of ExtractedRowDataTEMP is not empty then insert x & ":" & property (x) of ExtractedRowDataTEMP into GLOBAL ExtractedRowData
		end repeat
	else
		Try
			Set ExtractedRowDataTEMP to  (item ArbitraryRowNumber of FinalList)
			repeat with each item x of keys(ExtractedRowDataTEMP)
				if property (x) of ExtractedRowDataTEMP is not empty then insert x & ":" & property (x) of ExtractedRowDataTEMP into GLOBAL ExtractedRowData
			end repeat
		Catch theException
			_20_Global_Errors.DAIWrongValue "DAI Passed Value > "&ArbitraryRowNumber&" through to CBO_DataRetrieval. - Number of items available: "&number of items in FinalList
		End try
	end if

Can anyone please help?

2

Maybe a simple solution would be to prepend the column headers with a number. Not 100% sure this would work, but since Property Lists basically alphabetize themselves it might. I’m not sure how Property Lists work with numbers. It could order them as 1, 10, 11, … 19, 2, 20, etc. If that doesn’t work you might have to put 01 or 001 to get it to order them correctly. IF this works it would be the easiest fix.

A more complex way would be to create an object or a property list that has the known order that the fields are in. Then iterate over the property list in the order of the fields in the fieldOrder list. This could have a bit higher maintenance, but could give flexibility when skipping a field for a specific scenario.

I.E.
put {1: “firstName”, 2: “lastName”, 3: “Address”, 4: “city”, 5: “state”, 6: “zip”} into fieldOrder

3

Unfortunately not…

Alphanumeric sorts numbers with prefixes too…
1,10,11,12,13,2,21,22,23,3,4,5,6,7,8,9

Not only that but I will be continually adding columns in my datasheet so would have to keep renumbering which is a lengthy task.

And if that wasn’t enough, I want to use the Column headers as text to search for using OCR and I don’t want the additional step of having to strip x chars from each column header value.

It’s really important that I read from LEFT to RIGHT, Strip all columns which have no information in them (As they can vary from task to task) and then use the column headers as search strings for OCR and then enter text which is in the data row.

Thank you for your suggestion though… I do appreciate it.

4

Property Lists will always sort alphanumerically so maybe use something else. If it’s just a Key/Value pair that you need you could always create two Lists and then iterate over the first list and then access the second list through the index.

Say, once your column headers and your row are extracted from the excel file you have two equal length lists.

columnHeaders [“FirstName”, “LastName”, “Address”, “City”, “State”, “Zip”]
rowValues [“John”, “Doe”, “123 Wall St.”, “Anytown”, “Anystate”, “00000”]

Repeat with each item field of columnHeaders
//find field
//type value (or whatever you are executing here, TypeText is just an example)
TypeText (item repeatIndex() of rowValues)

end repeat

Sorry about the absence of formatting above. I’m new on the forum and still haven’t figured out how to post a code block.

5

This is certainly something I have looked at and at the moment seems to be the only solution.
However I am in talks with someone from Eggplant as I don’t see why alphanumeric should be forced when using property list. This is just extra functionality we don’t need especially when working with Excel data sheets.

I’ll wait for response before implementing this - But thank you for your reply.

P.s. Paste and highlight the code, and press CTRL+SHIFT+C to format it as a code block. :grin:

6

Thanks for the tip on getting my code into a code block.

One other thought would be to keep the spreadsheet open and read from it as the test progresses instead of extracting all the data at once. This could possibly slow the test down.

From my experience the Excel access could use some more robust capabilities.

7

One other thought would be to build an object (basically a Property List of Property Lists). When you extract the data from the Excel spreadsheet put it in a structure like the below orderedFieldList Property list. You will have to make the field[nnn] a padded number. There is only one advantage that I can think of off the top of my head that this has over the two single level lists. That is you could have a third property for each field if you need it. As you can see, field023 in the example below is intentionally put in the middle of the property list, but when this code is run it always executes last. Let us know if you get a different solution from Eggplant.

set orderedFieldList to { 
	field001: {fieldName:"FirstName", rowValue: "John"}, 
	field002: {fieldName:"LastName", rowValue: "Doe"},
	field003: {fieldName:"Address", rowValue: "123 Wall St."},
	field004: {fieldName:"City", rowValue: "Anytown"},
	field005: {fieldName:"State", rowValue: "Anystate"},
	field006: {fieldName:"Zip", rowValue: "00000"},
	field007: {fieldName:"seven", rowValue: "7"},
	field008: {fieldName:"eight", rowValue: "8"},
	field009: {fieldName:"nine", rowValue: "9"},
	field023: {fieldName:"twentythree", rowValue: "23"},
	field010: {fieldName:"ten", rowValue: "10"},
	field011: {fieldName:"eleven", rowValue: "11"},
	field012: {fieldName:"twelve", rowValue: "12"},
	field013: {fieldName:"thirteen", rowValue: "13"},
	field014: {fieldName:"fourteen", rowValue: "14"},
	field015: {fieldName:"fifteen", rowValue: "15"},
	field016: {fieldName:"sixteen", rowValue: "16"},
	field017: {fieldName:"seventeen", rowValue: "17"},
	field018: {fieldName:"eighteen", rowValue: "18"},
	field019: {fieldName:"nineteen", rowValue: "19"},
	field020: {fieldName:"twenty", rowValue: "20"},
	field021: {fieldName:"twentyone", rowValue: "21"},
	field022: {fieldName:"twentytwo", rowValue: "22"}
}

Repeat with each field of the keys of orderedFieldList
	//find field
	log "fieldName ..." && orderedFieldList.(field).fieldName
	//type value
	log "rowValue ..." && orderedFieldList.(field).rowValue
	
end repeat
8

@jcnordyke - This would be a valid solution as I cannot see it slowing down too much but not sure how to do such a thing with Eggplant.

With regards to your last comment, This is not possible for me I will have many (1000+) columns which I will be adding as and when I need them. This means the columns may not stay in the same order and renumbering them is too much maintenance.

Also creating a class for the fields is also a massive task as there are as many fields as columns and the code would be massive. Essentially, I cannot have arbitrary set columns because of the way I am working with the data and the system I am working on.

9

So if you want to create a Property List of Property lists dynamically you can do that as you extract the data from Excel. This way you don’t have to number or renumber field names in the Excel file header row. Maybe you already know how to do this, but for those who may be reading this and don’t here’s a little bit of code I whipped up as an example.

If your Excel file looks like this:
columnHeaders [“FirstName”, “MiddleInitial”, “LastName”, “Address1”, “Address2”, “City”, “State”, “Zip”, “Birthday”, “Anniversary”, “Favorites”, “Preferences”, “CC”, “ExpDate”]
rowValues [“John”, “X”, “Doe”, “123 Wall St.”, “Suite 234”, “Anytown”, “Anystate”, “00000”, “1/1/1970”, “1/2/1990”, “Sweets”, “no email”, “1234123412341234”, 01/2090]

Here’s a function that will take a worksheet and row number and create the Property List of Property Lists (structure is similar to the above orderedFieldList that I put in one of my previous answers). Pass in your worksheet in the first line and it should work for you. You can modify this to process multiple rows if necessary as well.

set global scenarioFieldValues to rowValues(worksheet, 2)
log global scenarioFieldValues

function rowValues (worksheet, row)

	// Iterate through the columns of a worksheet row to create a property list of
	// property lists of Key/Value Pairs. Key being FieldName/Value being row content
	// for that column. The padNumber function creates the field Property list with a 
	// 5 digit number so that you can have up to 99999 fields and they will stay in the
	// correct column order from what they are when extracted from Excel at runtime.
	
	set headerRow = 1 // static header row
	set column to 1 // iterator for columns
	set rowContents to {:} // empty property list to capture which rows to run

	// repeat until the row cell in column 1 is empty
	repeat until cell(worksheet, [headerRow, column]) = ""
		// add the field column number so that the property list stays in the same order as in Excel at runtime
		set fieldN to "field" & padNumber(repeatIndex(), "5")
		put {(cell(worksheet, [headerRow, column])): (cell(worksheet, [row, column]))} into property (fieldN) of rowContents

		add one to column
		
	end repeat
	return rowContents
end rowValues

function padNumber (num, numlen)

	// pad a number (num) with a number of zeros to the left to create a number/string that is numlen characters wide
	if (numlen > the length of num) then
		set paddinglen to numlen - the length of num
		set padding to "0" repeated paddinglen times
		
		return padding & num
	else
		return num
		
	end if
	
end padNumber

Output when I run this is the following Property List.

{field00001:{FirstName:“Jon”}, field00002:{MiddleInitial:“X”}, field00003:{LastName:“Doe”}, field00004:{Address1:“123 easy St”}, field00005:{Address2:“Suite 234”}, field00006:{City:“Anytown”}, field00007:{State:“Anystate”}, field00008:{Zip:“00000”}, field00009:{Birthday:“1970-01-01 05:00:00 -0700”}, field00010:{Anniversary:“1990-01-02 05:00:00 -0700”}, field00011:{Favorites:“Sweets”}, field00012:{Preferences:“no email”}, field00013:{CC:“1234123412341234”}, field00014:{ExpDate:“01/2090”}}

10

Thank you @jcnordyke - But unfortunately this still will not work.
It’s not a property list that I need - I need to use a database table (I think).
To be honest, I’m not sure of the naming for the type of table that I need, but if I put the records of an excel file into a variable, it has 1 item which can be referenced by the left side paired item.

MyExcelData
{Item 1}
Name|MyName
Comment|This_Is_A_Comment

With the structure above, I can use: Set MyValue to ExtractedRowData.(“Name”)
and it will return the value of ‘Name’ - “MyName”.

With your solution (In which I am very thankful you have worked on) every key pair has it’s own item entry. so I would have to know (or iterate through) the item(s) to find a value, then extract the pair and do further manipulation to split them.

I think it’s called a database table - but it doesn’t seem to be documented that well.

11

@jcnordyke - First of all… I’m really new to Eggplant. :frowning: and I’m trying to modify your code slightly to take in a global variable which contains the spreadsheet to use.

How do you reference the spreadsheet before referencing the worksheet in your code?

Despite what I said previously, I’ve just found out that database records reads the column headers as lower case only and thus causes another problem with using the column headers as the text to find on the screen and OCR as I use Case sensitive (Due to repeated words on a dialog).

So Your solution seems to be the better option but I don’t know how to use it right now as currently it’s not referencing a spreadsheet before the worksheet.

Thank you in advance.

In addition: It may be worth noting that my spreadsheet is in the resources folder.

12

@jcnordyke - Don’t worry… I read the documentation… :slight_smile:

For those reading this afterwards… you need the following

set MyExcelFile to Workbook(ResourcePath("YOUR_EXCEL_FILE.xlsx"))

then set the first line in @jcnordyke exmaple as:

set global scenarioFieldValues to rowValues((MyExcelFile.worksheet("YOUR_WORKSHEET_NAME")), 1)

Hope this helps someone.

And thank you @jcnordyke - This does indeed return the values with CASE on.

Ref: http://docs.eggplantsoftware.com/ePF/SenseTalk/stk-working-with-excel.htm

13

You might find this to be a bit easier when dealing with data in Excel: Reading rows from excel sheet and passing as parameters.