Excel Data Source Currency Cell Issue

Hi Kerry,

I tested this test case extensively on my end and I will share more details about the data binding with Excel in Test Studio.

You can apply additional formatting to the cell, like currency sign, decimal numbers and a visual separator. This additional formatting is not actually part of the cell content, which Test Studio uses in the data binding. You can see which values will be used, when you initially bind the test to the data source (see dataBindingPreview.png).

At this point, with the provided information for the test scenario, I have 2 suggestions to automate your test. Please find more details, for each of them, below.

1. Change the cell formatting in Excel to Text and manually add the comma "," where it is necessary. This way Test Studio will use the cell value with the added comma, because it is now part of the cell value and not added by Excel for visual representation.
2. I tried exporting the Excel file, with currency formatting, to CSV file format and I noticed that during the export the formatting was set as cell value. For example, when I exported the 1389.39 value from Excell (it looked like so "£1,389.39") it was saved in CSV as "£1,389.39". I was able to add the CSV file and bind the test to it to get the expected results.

I hope that the above mentioned details will help you automate the test scenario. In case they do not cover it, or you have any follow up questions, please do not hesitate to get back to me. I will also appreciate, if you can share more details about the use case you have and any specifics that might be relevant. I will try to provide another suggestion for you.

Regards,
Plamen Mitrev
Progress Telerik

Hi Plamen,

Thank you for the reply they are both great suggestions for getting around my issue, unfortunately option 1 won't work for us as we have mathematical calculations that don't function once you convert the fields to text. Also, the csv file route is not ideal as we would have to keep an excel file to do the calculations in and then convert it to a csv every time we want to add a new row of data (and accompanying Test Studio test). This is very impractical from a maintenance point of view.

This is our use case (test) that I am trying to carry out:

A retail user journey from product selection through the cart and checkout journey.

The spreadsheet rows contain every relevant piece of pricing data, as you progress through the cart and checkout. It starts with a plain base price value and then performs calculations in each cell for the progressing values. For example, calculating a VAT total, then adding this total to a cart total price etc. This is why option 1 you offered won't work for these use cases as we need to be able to perform complex maths functions on the data (rounding and percentages for example).

Once the data has been setup in the spreadsheet I then use each column of data for individual verification steps though the cart and checkout user journey. So I am verifying line item prices, line item totals, sub totals, delivery charges, VAT totals, overall totals and then the same again, but when a discount is applied, as examples.

We didn't hit a problem with any of the journey tests we have done so far until this last one, as it was testing larger values and we hit this issue with the comma needing to be present in the expected value in Test Studio as it is naturally displayed on the web pages we are testing with a comma for 1,000 plus values.

I hope this gives you enough information on the use cases we are trying to test and how we are setting up the data. I have attached our working data spreadsheet so you can see the progression of the data calculations and values.

Any further help you could give us that means we don't have to maintain both an excel file and a csv file would be appreciated.

Regards,

Kerry

Hi Kerry,

Thank you for sharing more details and an example for your test scenario. I understand how the previous suggestions will not be applicable to your tests. I recorded a sample test against a public application to make it easier to explain my next suggestions. Please find more details below.

The idea is to compare a string in the application under test, to a essentially a string from the data source. Since both of those strings have different formatting (the first one has an additional comma and $ sign and more), I believe that the best way is to use a coded step. Here we can trim the unnecessary parts of the string and convert it to an integer or a double. This makes it easier and more reliable to compare the values and get consistent results.

The attached example is a test recorded against https://www.calculator.net/big-number-calculator.html, where I multiply 2 numbers to get a large number > 1,000. I added the result element to the elements repository in Test Studio from the Add to Elements option in the context menu. I am now able to use this element in my custom logic.

            var containerText = Pages.BigNumberCalculator0.x18444PTag.BaseElement.TextContent; //get the text content of the element

            //here you might need to remove some unnecessary parts of the string containerText before you proceed to convert it to double

            double extracted = Convert.ToDouble(containerText); //convert it to double
            double dataSource = (double)System.Convert.ChangeType(Data["Account_Code"], typeof(double)); //convert the string from the data source to double
                        
            Assert.IsTrue(dataSource.CompareTo(extracted) == 0); //verify if the two values are equal

You need to ensure that the result element's find logic does not contain the TextContent or InnerText, otherwise the test will fail to find the element, when the result changes.

The data source in my test project contains only 2 entries to illustrate the pass and fail of the test. Please analyze the attached project and feel free to follow up with any questions. You can also attach the custom code that you are using in your scenario and I will be happy to advise you further.

Regards,
Plamen Mitrev
Progress Telerik

Hi Plamen,

Thank you very much for the comprehensive response. I will be looking into this with one of our Developers to see how feasible it will be as we have already put a lot of work into our end to end tests and this would require a lot of work to go back and implement for each of them.

Kind Regards,

Kerry

Hi Plamen,

As it stands right now, we've decided to go with the export to csv file as it is the lowest effort from a maintenance point of view. However we have now hit another issue, we exported to a csv file and all the values are correct (with commas where needed), but when we data bind to this csv file Test Studio isn't picking up all the values with commas in, it is stripping out some of the commas, but not all of them. It's very strange why it recognises some of them but strips out others.

I have attached the csv file being used and a screenshot of the Data Bind window from Test Studio that shows some correct and some stripped out commas. Any help on this would be appreciated otherwise the csv file solution won't work.

Kind Regards,

Kerry

Hi Kerry,

I did some extensive testing in an effort to optimize the Excel document to show up as you need it. It was not easy to trick Excel and get the required result, because of the specifics of the Microsoft Office tool and how it visualizes the values. There is a noticeable difference between what Excel shows and how the Microsoft Excel drivers get the value, but I found a way to get the result with minimum effort. Please check the sample project and the steps I did to get there.

1. Open the original .xls file and save it as the latest extension for Excel - .xlsx.
2. Open the newly saved .xlsx file, select all cell and go to the Format Cells window from the context menu.
3. I noticed that the cells' formatting is locked. To unlock it, go to the Protection tab and uncheck the Locked option.
4. While in the Format Cells window, go to Number tab and select the Number category and enable the "Use 1000 Separator (,)" option.
5. Now we need to insert a new line with some random text in the beginning of the document. So, right click on row 2, click on Insert and enter some text in all cells in this row. I personally entered text in the first column and copied it in the whole row.
6. To help the Excel driver read the content properly, I found that I need to change the Format Cell category, of the newly created row 2, to Text.

Those are the preconditions that I made to fix the visualization issue and ensure that the file is correctly read. You can now proceed to add it to the project and bind it to the necessary test and filter out the first unnecessary line (see filterDataSource.png).

I hope that this solution will help you automate the test scenario with minimum effort and customization. Please do not hesitate to contact me again, if you need further assistance.

Regards,
Plamen Mitrev
Progress Telerik

Hi Plamen,

Thank you very much for the response and your efforts, it is very much appreciated. However I have now hit another issue (that we saw previously and got around by using a .xls file instead of .xlsx). Our version of Test Studio (2018.2.606.0) doesn't recognise the ProdData worksheet when trying to bind a test to a data source using an .xlsx file, I have attached screenshots to illustrate this issue. The only way we could see the ProdData sheet was to convert the file to .xls but then we are back to the issue of not seeing the commas in the values pulled into Test Studio!

I hope you can advise on how we get the binding working with a .xlsx file.

Kind Regards,

Kerry

Hi Kerry,

There might be an easy solution to the issue that you are having with missing table. Please install the 32-bit version of Microsoft Access Database Engine 2010, as it works great with the Excel driver. I have attached the correct zip in this reply, but you can also download it from the official Microsoft page.

Please let me know if that helped and you can select the table for an .xlsx file.

Regards,
Plamen Mitrev
Progress Telerik

Hi Plamen,

I would like to offer a massive thank you for your efforts in resolving all our issues. I can report that we are now up and running successfully using all your help detailed in your above posts.

Once again, thank you.

Kind Regards,

Kerry

Hi Kerry,

I am happy to know that you can automate your test scenario successfully and continue with the test automation.

Please do not hesitate to contact us again, if you need any help.

Regards,
Plamen Mitrev
Progress Telerik